VbzCart/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:

Stage 1

A query (qryMake_Items_Avail) builds a table (in a new, temporary table: [Calc Items Avail]) of items which are known to be available based on supplier catalog data.

• 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;