VbzCart/docs/archive/catalog building 2006

The Process

This was recently revised to use mostly SQL queries to do the necessary data manipulation; it had been done in code before. SQL is much faster, but seems to require larger chunks of thinking in order to maintain successfully, in part because you can't step through it.

As it is now, parts of the building process are broken. Here's what happens:

Phase I

Stage 1

A query (qryMake_Items_Avail) builds a (temporary) table of items which are known to be available based on supplier catalog data. qryMake_Items_Avail gets data from qryFig_Items and uses it to create [Calc Items Avail] without further processing.

• qryMake_Items_Avail:

  SELECT * INTO [Calc Items Avail] FROM qryFig_Items;

• qryFig_Items:

  SELECT

  t.ID AS ID_Title,

  NULL AS ID_Item,

  t.CatNum AS CatNum_Title,

  g.Code AS GroupCode,

  gt.GroupCode AS GroupTitleCode,

  o.CatKey AS OptionCode,

  it.Code AS ItemTypeCode,

  g.Sort & it.Sort & o.Sort AS ITypOptSort,

  t.CatNum&BuildOption(g.Code, gt.GroupCode, o.CatKey) AS CatNum,

  g.Name AS GroupName,

  gi.Descr AS GrpItemDescr,

  Cost AS PriceCost,

  PriceOurs AS PriceCust,

  PriceList,

  ID_ShipCode,

  ID_ItemType,

  ID_Option,

  gt.Supplier_CatNum AS Supplier_CatNum_Group,

  iif(g.Descr IS NULL,iif(it.Descr IS NULL,it.NameSingular,it.Descr),g.Descr) AS ITypeDescr,

  gt.GroupDescr AS GroupTitleDescr,

  o.Descr AS OptionDescr,

  c.Abbr AS CatAbbr,

  c.DateAvail AS CatDateAvail

  FROM ((((

  (qryCatNum_Titles AS t LEFT JOIN [TGroup Titles] AS gt ON gt.ID_Title=t.ID)

  LEFT JOIN TGroups AS g ON g.ID=gt.ID_TGroup)

  LEFT JOIN qryCatalogs_Active AS c ON gt.ID_Catalog=c.ID)

  LEFT JOIN [TGroup Items] AS gi ON gi.ID_TGroup=g.ID)

  LEFT JOIN Options AS o ON gi.ID_Option=o.ID)

  LEFT JOIN [Item Types] AS it ON gi.ID_ItemType=it.ID

  WHERE (c.ID IS NOT NULL) AND g.Active AND gt.Active AND gi.Active AND (o.Active or (nz(ID_Option)=0))

  ORDER BY t.CatNum, g.Sort, o.Sort;

At this point, we have catalog numbers for everything but we don't know which records in Items these should be used to update, nor which ones are new items.

Stage 2

qryCkDups_Items_Avail contains any catalog numbers which have been duplicated. If there are any, processing stops at this point because further data massaging/entry is needed.

• qryCkDups_Items_Avail

  SELECT CatNum, Count(ID_Title) AS Count

  FROM [Calc Items Avail]

  GROUP BY CatNum

  HAVING Count(ID_Title)>1;

Stage 3

qryItems_Avail_NeedNew contains any catalog numbers which have not already been entered into the Items table. If it is empty, then there are no new items to deal with, so we can skip

• qryItems_Avail_NeedNew

  SELECT ia.CatNum

  FROM [Calc Items Avail] AS ia

  LEFT JOIN Items AS i ON ia.CatNum=i.CatNum

  WHERE i.CatNum IS NULL;

Phase II

Changes are committed in this phase.