VbzCart/docs/pieces/catalog/building/2006: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | pieces‎ | catalog‎ | building
Jump to navigation Jump to search
imported>Woozle
(→‎Step 6: removed extra SET line)
m (Woozle moved page VbzCart/VbzCart/pieces/catalog/building/2006 to VbzCart/docs/pieces/catalog/building/2006 without leaving a redirect: correct naming (was no way to import directly to this name))
 
(37 intermediate revisions by one other user not shown)
Line 3: Line 3:


==The Process==
==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.
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. On the other hand, it forces the process to be cleaner and simpler (believe it or not).


As it is now, parts of the building process are broken. Here's what happens:
The process is something like this:
# Build a temporary dataset (['''Calc Items Avail''']) consisting of items we know are currently available.
#* The rules for deriving this data are a little complicated; there are suppliers, who have catalogs listing what items they have available and with what options; to simplify data entry, we have "title groups" of common groups of options, and a record of which groups are applicable for each catalog item (called a "title"); and so on...
#* Each item is only identified by catalog number in the temporary table; we still need to match up each "available" item with a permanent item record in the Items table, or create a new Items record if the item is new.
#* We can only tell an item is new by noting that it doesn't match an existing catalog number; there's probably a better way to do this, but some design work is needed.
# Check to make sure there are no duplicate catalog numbers, which would mean that the catalog hasn't been entered quite right.
# Clear status fields in the (permanent) '''Items''' table; we're about to update it.
# Update existing items in the '''Items''' table from the temporary table of available items
# Update '''Items''' table from what's in stock (even if an item isn't available from a current catalog, we may have some in stock, which would make it still available but discontinued). Items in stock will always exist in the '''Items''' table, else something is seriously broken. (Should probably add a step to verify that all stock items exist in '''Items'''.)
# Look for (and mark) any "recyclable" '''Items''' records we need to use (done in code), then update them from the temporary table (['''Calc Items Avail''']).
# Having used up all the "recyclables", any temporary items still unassigned are new items and will need new records in '''Items'''. Create those records.
# Do a little error-checking, and we are done.
===Phase I===
===Phase I===
====Step 1====
====Step 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.
A query ('''qryMake_Items_Avail''') creates a table of items ([Calc Items Avail]) 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.


We really just want [Calc Items Avail].ID_Item to be all NULLs, but if you do that it gets created as a text field rather than an integer (text must be a default, when there's no restraint on the type), so we first assign it all zeros and then change the zeros to NULLs with '''qryVb_ItemsNeeded_PreClean'''. (There's probably a way to force the type on MySQL, but there doesn't seem to be one in Access.)
=====[Calc Items Avail] issues=====
* <s>We really just want [Calc Items Avail].ID_Item to be all NULLs, but if you do that it gets created as a text field rather than an integer (text must be a default, when there's no restraint on the type), so we first assign it all zeros and then change the zeros to NULLs with '''qryVb_ItemsNeeded_PreClean'''. (There's probably a way to force the type on MySQL, but there doesn't seem to be one in Access.)</s> Resolved by making the table permanent; see below.
* The data is generated from scratch each time, but it is a permanent table so that the CatKey field can be an index; there doesn't seem to be any non-klugey way to do this with a temporary table.
* Certain fields needed to be unique indexes, but this was impossible because their values were initially blank. Finally arrived at the solution of creating the initial dataset in a temp table ([Calc Items Avail scratch]), filling in all the blank fields, and ''then'' copying everything over to the "designed" table ([Calc Items Avail]).


=====queries=====
* '''qryVb_Clear_Items_Avail''':
*: '''DELETE''' * '''FROM''' [Calc Items Avail];
* '''qryVb_Make_Items_Avail''':
* '''qryVb_Make_Items_Avail''':
*: '''SELECT''' * '''INTO''' [Calc Items Avail] FROM qryVb_Fig_Items;
*: '''INSERT INTO''' [Calc Items Avail] '''SELECT''' * FROM qryVb_Fig_Items;
* '''qryVb_Fig_Items''':
* '''qryVb_Fig_Items''':
*: '''SELECT'''
*: '''SELECT'''
*:: t.ID AS ID_Title,
*:: t.ID AS ID_Title,
*:: NULL AS ID_Item,
*:: NULL AS ID_Item,
*:: OptionJoin(gt.GroupCode,o.CatKey,"/") AS ITypOptCode_Calc,
*:: OptionJoin(gt.GroupDescr,o.Descr," / ") AS IGrpOptDescr_Calc,
*:: t.CatNum AS CatNum_Title,
*:: t.CatNum AS CatNum_Title,
*:: BuildOption(g.Code, gt.GroupCode, o.CatKey) AS CatExt,
*:: NULL AS CatKey,
*:: iif(g.Descr IS NULL,iif(it.Descr IS NULL,it.NameSingular,it.Descr),g.Descr) AS ITypeDescr,
*:: NULL AS ITypOptDescr_Calc,
*:: g.Code AS GroupCode,
*:: g.Code AS GroupCode,
*:: gt.GroupCode AS GroupTitleCode,
*:: gt.GroupCode AS GroupTitleCode,
Line 24: Line 47:
*:: it.Code AS ItemTypeCode,
*:: it.Code AS ItemTypeCode,
*:: g.Sort & it.Sort & o.Sort AS ITypOptSort,
*:: g.Sort & it.Sort & o.Sort AS ITypOptSort,
*:: t.CatNum&BuildOption(g.Code, gt.GroupCode, o.CatKey) AS CatNum,
*:: NULL AS CatNum,
*:: g.Name AS GroupName,
*:: g.Name AS GroupName,
*:: gi.Descr AS GrpItemDescr,
*:: gi.Descr AS GrpItemDescr,
Line 34: Line 57:
*:: ID_Option,
*:: ID_Option,
*:: gt.Supplier_CatNum AS Supplier_CatNum_Group,
*:: 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,
*:: gt.GroupDescr AS GroupTitleDescr,
*:: o.Descr AS OptionDescr,
*:: o.Descr AS OptionDescr,
Line 46: Line 68:
*:: '''LEFT JOIN''' Options AS o '''ON''' gi.ID_Option=o.ID)
*:: '''LEFT JOIN''' Options AS o '''ON''' gi.ID_Option=o.ID)
*:: '''LEFT JOIN''' [Item Types] AS it '''ON''' gi.ID_ItemType=it.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))
*:: '''WHERE'''
*::: (c.ID IS NOT NULL) AND
*::: g.Active AND
*::: gt.Active AND
*::: gt.WhenDiscont IS NULL AND
*::: gi.Active AND
*::: (o.Active or (nz(ID_Option)=0))
*:: '''ORDER BY''' t.CatNum, g.Sort, o.Sort;
*:: '''ORDER BY''' t.CatNum, g.Sort, o.Sort;
* '''qryVb_ItemsNeeded_Ops''' calculates one more field which would be ugly SQL if we tried to do it in the previous step:
*: '''UPDATE''' [Calc Items Avail] '''SET'''
*:: ITypOptDescr_Calc = OptionJoin(ITypeDescr,IGrpOptDescr_Calc, " / "),
*:: CatNum = CatNum_Title & CatExt,
*:: CatKey = ID_Title & nz(CatExt);
* '''qryVb_ItemsNeeded_PreClean''':
* '''qryVb_ItemsNeeded_PreClean''':
*: '''UPDATE''' [Calc Items Avail] '''SET''' ID_Item = NULL;
*: '''UPDATE''' [Calc Items Avail] '''SET''' ID_Item = NULL;


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.
At this point, we have catalog numbers and other identifying data for everything but we don't know which specific records in '''Items''' these should be used to update, nor which ones are new items.


====Step 2====
====Step 2====
Line 65: Line 98:
Changes are committed in this phase.
Changes are committed in this phase.
====Step 3====
====Step 3====
'''qryVb_Items_ResetActive''' prepares the '''Items''' table for update:
'''qryVb_Items_CalcFields''' figures/updates significant calculated fields in the '''Items''' table:
* '''UPDATE''' Items '''SET''' Active=False, InPrint=False, Sources=NULL, WhenLastInPrint=NULL '''WHERE''' Active
* '''UPDATE''' Items AS i '''LEFT JOIN''' qryCatNum_Titles AS t '''ON''' i.ID_Title=t.ID '''SET''' i.CatKey = ID_Title&CatExt, i.CatNum = t.CatNum&i.CatExt;
'''qryVb_Items_ResetActive''' resets flags and timestamps in the '''Items''' table:
* '''UPDATE''' Items '''SET''' Active=False, InPrint=False, Sources=NULL '''WHERE''' Active


====Step 4====
====Step 4====
'''qryVb_Update_Items_Existing''' updates records in '''Items''' whose catalog numbers match items in '''[Calc Items Avail]''':
'''qryVb_Update_Items_Existing''' updates records in '''Items''' whose catalog numbers match items in '''[Calc Items Avail]''':
* '''qryVb_Items_toUpdate:
*: '''SELECT'''
*:: *
*: '''FROM'''
*:: [Calc Items Avail] AS ia '''LEFT JOIN'''
*:: Items AS i
*::: '''ON''' ia.CatKey=i.CatKey
*::: '''WHERE''' (i.CatNum IS NOT NULL) AND (ia.ID_Item IS NULL)
*: '''ORDER BY''' ia.CatNum;
The "ID_Item IS NULL" is largely a debugging feature, so you can see which [Calc Items Avail] rows haven't yet been taken care of. It might also be useful in the future if other updating of Items from [Calc Items Avail] takes place before this step.
* '''qryVb_Update_Items_Existing''':
* '''qryVb_Update_Items_Existing''':
*: '''UPDATE''' [Calc Items Avail] AS ia
*: '''UPDATE''' qryVb_Items_toUpdate
*:: '''LEFT JOIN''' Items AS i ON ia.CatNum=i.CatNum
*:: '''SET'''
*:: '''SET'''
*::: ia.ID_Item = i.ID,
*::: ia.ID_Item = i.ID,
*::: i.CatNum = ia.CatNum,
*::: i.CatExt = ia.CatExt,
*::: i.CatKey = ia.CatKey,
*::: i.InPrint = TRUE,
*::: i.InPrint = TRUE,
*::: i.Active = TRUE,
*::: i.Active = TRUE,
Line 82: Line 131:
*::: i.OptDescr = ia.GrpItemDescr,
*::: i.OptDescr = ia.GrpItemDescr,
*::: i.ID_ItemType = ia.ID_ItemType,
*::: i.ID_ItemType = ia.ID_ItemType,
*::: i.ITypDescr = ia.ITypeDescr,
*::: i.ITypDescr = ia.ITypeDescr & iif(GroupTitleDescr IS NULL,""," - "&GroupTitleDescr),
*::: i.ITypOptCode = OptionJoin(ia.GroupTitleCode,ia.OptionCode,"/"),
*::: i.ITypOptCode = ITypOptCode_Calc,
*::: i.ITypOptDescr = OptionJoin(ia.ITypeDescr,OptionJoin(ia.GroupTitleDescr,ia.OptionDescr," / "), " / "),
*::: i.ITypOptDescr = ITypOptDescr_Calc,
*::: i.ITypOptSort = ia.ITypOptSort,
*::: i.ITypOptSort = ia.ITypOptSort,
*::: i.ID_ShipCode = ia.ID_ShipCode,
*::: i.ID_ShipCode = ia.ID_ShipCode,
Line 94: Line 143:
*::: i.Supplier_CatNum_Group = ia.Supplier_CatNum_Group
*::: i.Supplier_CatNum_Group = ia.Supplier_CatNum_Group
*:: '''WHERE''' i.CatNum IS NOT NULL;
*:: '''WHERE''' i.CatNum IS NOT NULL;
====Step 5====
====Step 5====
'''qryVb_Update_Items_Stock''' also activates any items not already activated in Step 5, and also updates a few related fields.
'''qryVb_Update_Items_Stock''' also activates any items not already activated in Step 5, and also updates a few related fields.
Line 104: Line 154:
*:: '''WHERE''' (s.WhenRemoved IS NULL) AND (l.WhenVoided IS NULL) AND l.isSellable;
*:: '''WHERE''' (s.WhenRemoved IS NULL) AND (l.WhenVoided IS NULL) AND l.isSellable;
====Step 6====
====Step 6====
In this step, the list of "new" items in '''qryVb_ItemsCalc_Unassigned''' is checked against the list of recyclable items in '''qryItems_Recyclable'''. Recyclable items are allocated to new items by setting the ID_Item field in '''qryItemsCalc_Unassigned''' equal to the ID of each recyclable item allocated. The iteration for this is done in code, as I wasn't able to figure out any way to do it in SQL. After recyclables have been allocated to new items, '''qryUpdate_Items_Recycled''' does the full updating of each reallocated recyclable record.
In this step, the list of "new" items in '''qryVb_ItemsCalc_Unassigned''' is checked against the list of recyclable items in '''qryItems_Recyclable'''. Recyclable items are allocated to new items by setting the ID_Item field in '''qryItemsCalc_Unassigned''' equal to the ID of each recyclable item allocated. The iteration for this is done in code (PrepRecycle()), as I wasn't able to figure out any way to do it in SQL.
 
After recyclables have been allocated to new items, '''qryUpdate_Items_Recycled''' does the full updating of each reallocated recyclable record.


Note: bothering to recycle records in the first place is probably symptomatic of bad design in some way, but it bothers me to just delete records and leave huge holes in the numbering sequence. Huge holes make me think that something has been eating data (which has happened in the past), so I prefer not to have them. Perhaps this obsessive recycling does serve as a kind of minor data-integrity check?
Note: bothering to recycle records in the first place is probably symptomatic of bad design in some way, but it bothers me to just delete records and leave huge holes in the numbering sequence. Huge holes make me think that something has been eating data (which has happened in the past), so I prefer not to have them. Perhaps this obsessive recycling does serve as a kind of minor data-integrity check?


This also seems to be a broken bit, because if you disable creating new items but allow recycling, there are always leftover recyclables ''and'' leftover new items to save.
This bit wasn't working at first because (it turned out) the <u>ITypOptCode</u> and <u>ITypOptDescr</u> fields are allowed to be null but not empty strings, and the corresponding functions were sometimes returning empty strings. I changed the functions to return NULL whenever the result was an empty string, and now the query is happy.
* '''qryVb_ItemsCalc_Unassigned''':
* PrepRecycle():
*: '''SELECT''' * '''FROM''' [Calc Items Avail] AS i '''WHERE''' i.ID_Item=0;
** '''qryVb_ItemsCalc_Unassigned''':
* '''qryVb_ItemsCalc_Unassigned''':
**: '''SELECT''' * '''FROM''' [Calc Items Avail] AS i '''WHERE''' i.ID_Item=0;
*: '''SELECT''' * '''FROM''' Items AS i '''WHERE''' i.Pulled AND i.isFree;
** '''qryVb_Items_Recyclable''':
**: '''SELECT''' * '''FROM''' Items AS i '''WHERE''' i.Pulled AND i.isFree AND NOT i.Active;;
* '''qryVb_Update_Items_Recycled''':
* '''qryVb_Update_Items_Recycled''':
*: '''UPDATE''' [Calc Items Avail] AS ia '''LEFT JOIN'''
*: '''UPDATE''' [Calc Items Avail] AS ia '''LEFT JOIN'''
Line 119: Line 172:
*:: '''SET'''
*:: '''SET'''
*::: i.WhenRetitled = Now(),
*::: i.WhenRetitled = Now(),
*::: i.CatNum = ia.CatNum,
*::: i.CatNum = ia.CatNum,
*::: i.CatExt = ia.CatExt,
*::: i.CatKey = ia.CatKey,
*::: i.Pulled = FALSE,
*::: i.Pulled = FALSE,
*::: i.isFree = FALSE,
*::: i.isFree = FALSE,
Line 146: Line 201:
====Step 7====
====Step 7====
'''qryVb_Append_Items_New''' creates new '''Items''' records for any new items not yet taken care of.
'''qryVb_Append_Items_New''' creates new '''Items''' records for any new items not yet taken care of.
* '''qryVb_Append_Items_New''':
* '''qryVb_Items_toAppend''':
*: '''INSERT INTO''' Items ( CatNum, ID_Title, WhenFirstInPrint, WhenCreated, Pulled, isFree, InPrint, Active, WhenLastSourced, ID_Option, OptCode, OptDescr, ID_ItemType, ITypDescr, ITypOptCode, ITypOptDescr, ITypOptSort, ID_ShipCode, Cost, PriceOurs, PriceList, Sources, WhenLastInPrint, Supplier_CatNum_Group )
*:: '''SELECT'''
*:: '''SELECT'''
*::: ia.CatNum,
*::: ia.CatNum,
*::: ia.CatExt,
*::: ia.CatKey,
*::: ia.ID_Title,
*::: ia.ID_Title,
*::: ia.CatDateAvail AS WhenFirstInPrint,
*::: ia.CatDateAvail AS WhenFirstInPrint,
Line 175: Line 231:
*:: '''FROM''' [Calc Items Avail] AS ia
*:: '''FROM''' [Calc Items Avail] AS ia
*:: '''WHERE''' ia.ID_Item IS NULL;
*:: '''WHERE''' ia.ID_Item IS NULL;
* '''qryVb_Append_Items_New''':
*: '''INSERT INTO''' Items * FROM qryVb_Items_toAppend;


===Phase III===
===Phase III===
Line 183: Line 241:
* '''qryVb_Items_Avail_NeedNew'''
* '''qryVb_Items_Avail_NeedNew'''
*: '''SELECT''' <u>ia.CatNum</u>
*: '''SELECT''' <u>ia.CatNum</u>
*:: '''FROM''' [Calc Items Avail] AS ia
*:: '''FROM''' [Calc Items Avail] '''AS''' ia
*:: '''LEFT JOIN''' Items AS i ON <u>ia.CatNum</u>=<u>i.CatNum</u>
*:: '''LEFT JOIN''' Items '''AS''' i '''ON''' <u>ia.CatKey</u>=<u>i.CatKey</u>
*:: '''WHERE''' <u>i.CatNum</u> IS NULL;
*:: '''WHERE''' <u>i.ID</u> '''IS NULL'''
*:: '''ORDER BY''' <u>ia.CatNum</u>;


The choice of <u>i.CatNum</u> as a filter is somewhat arbitrary; the point is to find records in '''[Calc Items Avail]''' with no matching records in '''Items'''.
The choice of <u>i.ID</u> as a filter is somewhat arbitrary; the point is to find records in '''[Calc Items Avail]''' with no matching records in '''Items'''.


The resulting recordset does not include items which exist but need updating; it is only items which do not appear to have been created in '''Items''' yet, and thus need new or recycled '''Items''' records.
The resulting recordset does not include items which exist but need updating; it is only items which do not appear to have been created in '''Items''' yet, and thus need new or recycled '''Items''' records.

Latest revision as of 01:53, 25 February 2024

Template:Hide

Template:SidebarVbzCart: catalog building

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. On the other hand, it forces the process to be cleaner and simpler (believe it or not).

The process is something like this:

  1. Build a temporary dataset ([Calc Items Avail]) consisting of items we know are currently available.
    • The rules for deriving this data are a little complicated; there are suppliers, who have catalogs listing what items they have available and with what options; to simplify data entry, we have "title groups" of common groups of options, and a record of which groups are applicable for each catalog item (called a "title"); and so on...
    • Each item is only identified by catalog number in the temporary table; we still need to match up each "available" item with a permanent item record in the Items table, or create a new Items record if the item is new.
    • We can only tell an item is new by noting that it doesn't match an existing catalog number; there's probably a better way to do this, but some design work is needed.
  2. Check to make sure there are no duplicate catalog numbers, which would mean that the catalog hasn't been entered quite right.
  3. Clear status fields in the (permanent) Items table; we're about to update it.
  4. Update existing items in the Items table from the temporary table of available items
  5. Update Items table from what's in stock (even if an item isn't available from a current catalog, we may have some in stock, which would make it still available but discontinued). Items in stock will always exist in the Items table, else something is seriously broken. (Should probably add a step to verify that all stock items exist in Items.)
  6. Look for (and mark) any "recyclable" Items records we need to use (done in code), then update them from the temporary table ([Calc Items Avail]).
  7. Having used up all the "recyclables", any temporary items still unassigned are new items and will need new records in Items. Create those records.
  8. Do a little error-checking, and we are done.

Phase I

Step 1

A query (qryMake_Items_Avail) creates a table of items ([Calc Items Avail]) 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.

[Calc Items Avail] issues
  • We really just want [Calc Items Avail].ID_Item to be all NULLs, but if you do that it gets created as a text field rather than an integer (text must be a default, when there's no restraint on the type), so we first assign it all zeros and then change the zeros to NULLs with qryVb_ItemsNeeded_PreClean. (There's probably a way to force the type on MySQL, but there doesn't seem to be one in Access.) Resolved by making the table permanent; see below.
  • The data is generated from scratch each time, but it is a permanent table so that the CatKey field can be an index; there doesn't seem to be any non-klugey way to do this with a temporary table.
  • Certain fields needed to be unique indexes, but this was impossible because their values were initially blank. Finally arrived at the solution of creating the initial dataset in a temp table ([Calc Items Avail scratch]), filling in all the blank fields, and then copying everything over to the "designed" table ([Calc Items Avail]).
queries
  • qryVb_Clear_Items_Avail:
    DELETE * FROM [Calc Items Avail];
  • qryVb_Make_Items_Avail:
    INSERT INTO [Calc Items Avail] SELECT * FROM qryVb_Fig_Items;
  • qryVb_Fig_Items:
    SELECT
    t.ID AS ID_Title,
    NULL AS ID_Item,
    OptionJoin(gt.GroupCode,o.CatKey,"/") AS ITypOptCode_Calc,
    OptionJoin(gt.GroupDescr,o.Descr," / ") AS IGrpOptDescr_Calc,
    t.CatNum AS CatNum_Title,
    BuildOption(g.Code, gt.GroupCode, o.CatKey) AS CatExt,
    NULL AS CatKey,
    iif(g.Descr IS NULL,iif(it.Descr IS NULL,it.NameSingular,it.Descr),g.Descr) AS ITypeDescr,
    NULL AS ITypOptDescr_Calc,
    g.Code AS GroupCode,
    gt.GroupCode AS GroupTitleCode,
    o.CatKey AS OptionCode,
    it.Code AS ItemTypeCode,
    g.Sort & it.Sort & o.Sort AS ITypOptSort,
    NULL 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,
    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
    gt.WhenDiscont IS NULL AND
    gi.Active AND
    (o.Active or (nz(ID_Option)=0))
    ORDER BY t.CatNum, g.Sort, o.Sort;
  • qryVb_ItemsNeeded_Ops calculates one more field which would be ugly SQL if we tried to do it in the previous step:
    UPDATE [Calc Items Avail] SET
    ITypOptDescr_Calc = OptionJoin(ITypeDescr,IGrpOptDescr_Calc, " / "),
    CatNum = CatNum_Title & CatExt,
    CatKey = ID_Title & nz(CatExt);
  • qryVb_ItemsNeeded_PreClean:
    UPDATE [Calc Items Avail] SET ID_Item = NULL;

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

Step 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.

  • qryVb_CkDups_Items_Avail
    SELECT CatNum, Count(ID_Title) AS Count
    FROM [Calc Items Avail]
    GROUP BY CatNum
    HAVING Count(ID_Title)>1;

Phase II

Changes are committed in this phase.

Step 3

qryVb_Items_CalcFields figures/updates significant calculated fields in the Items table:

  • UPDATE Items AS i LEFT JOIN qryCatNum_Titles AS t ON i.ID_Title=t.ID SET i.CatKey = ID_Title&CatExt, i.CatNum = t.CatNum&i.CatExt;

qryVb_Items_ResetActive resets flags and timestamps in the Items table:

  • UPDATE Items SET Active=False, InPrint=False, Sources=NULL WHERE Active

Step 4

qryVb_Update_Items_Existing updates records in Items whose catalog numbers match items in [Calc Items Avail]:

  • qryVb_Items_toUpdate:
    SELECT
    *
    FROM
    [Calc Items Avail] AS ia LEFT JOIN
    Items AS i
    ON ia.CatKey=i.CatKey
    WHERE (i.CatNum IS NOT NULL) AND (ia.ID_Item IS NULL)
    ORDER BY ia.CatNum;

The "ID_Item IS NULL" is largely a debugging feature, so you can see which [Calc Items Avail] rows haven't yet been taken care of. It might also be useful in the future if other updating of Items from [Calc Items Avail] takes place before this step.

  • qryVb_Update_Items_Existing:
    UPDATE qryVb_Items_toUpdate
    SET
    ia.ID_Item = i.ID,
    i.CatNum = ia.CatNum,
    i.CatExt = ia.CatExt,
    i.CatKey = ia.CatKey,
    i.InPrint = TRUE,
    i.Active = TRUE,
    i.WhenLastSourced = Now(),
    i.ID_Option = ia.ID_Option,
    i.OptCode = ia.OptionCode,
    i.OptDescr = ia.GrpItemDescr,
    i.ID_ItemType = ia.ID_ItemType,
    i.ITypDescr = ia.ITypeDescr & iif(GroupTitleDescr IS NULL,""," - "&GroupTitleDescr),
    i.ITypOptCode = ITypOptCode_Calc,
    i.ITypOptDescr = ITypOptDescr_Calc,
    i.ITypOptSort = ia.ITypOptSort,
    i.ID_ShipCode = ia.ID_ShipCode,
    i.Cost = ia.PriceCost,
    i.PriceOurs = ia.PriceCust,
    i.PriceList = ia.PriceList,
    i.Sources = i.Sources&"."&ia.CatAbbr,
    i.WhenLastInPrint = ia.CatDateAvail,
    i.Supplier_CatNum_Group = ia.Supplier_CatNum_Group
    WHERE i.CatNum IS NOT NULL;

Step 5

qryVb_Update_Items_Stock also activates any items not already activated in Step 5, and also updates a few related fields.

  • qryVb_Update_Items_Stock:
    UPDATE (
    Items AS i
    LEFT JOIN Stock AS s ON s.ID_Item=i.ID)
    LEFT JOIN Locations AS l ON s.ID_Location=l.ID
    SET i.Active = TRUE, i.WhenLastStocked = Now(), i.Sources = i.Sources&".stock"
    WHERE (s.WhenRemoved IS NULL) AND (l.WhenVoided IS NULL) AND l.isSellable;

Step 6

In this step, the list of "new" items in qryVb_ItemsCalc_Unassigned is checked against the list of recyclable items in qryItems_Recyclable. Recyclable items are allocated to new items by setting the ID_Item field in qryItemsCalc_Unassigned equal to the ID of each recyclable item allocated. The iteration for this is done in code (PrepRecycle()), as I wasn't able to figure out any way to do it in SQL.

After recyclables have been allocated to new items, qryUpdate_Items_Recycled does the full updating of each reallocated recyclable record.

Note: bothering to recycle records in the first place is probably symptomatic of bad design in some way, but it bothers me to just delete records and leave huge holes in the numbering sequence. Huge holes make me think that something has been eating data (which has happened in the past), so I prefer not to have them. Perhaps this obsessive recycling does serve as a kind of minor data-integrity check?

This bit wasn't working at first because (it turned out) the ITypOptCode and ITypOptDescr fields are allowed to be null but not empty strings, and the corresponding functions were sometimes returning empty strings. I changed the functions to return NULL whenever the result was an empty string, and now the query is happy.

  • PrepRecycle():
    • qryVb_ItemsCalc_Unassigned:
      SELECT * FROM [Calc Items Avail] AS i WHERE i.ID_Item=0;
    • qryVb_Items_Recyclable:
      SELECT * FROM Items AS i WHERE i.Pulled AND i.isFree AND NOT i.Active;;
  • qryVb_Update_Items_Recycled:
    UPDATE [Calc Items Avail] AS ia LEFT JOIN
    Items AS i
    ON ia.ID_Item=i.ID
    SET
    i.WhenRetitled = Now(),
    i.CatNum = ia.CatNum,
    i.CatExt = ia.CatExt,
    i.CatKey = ia.CatKey,
    i.Pulled = FALSE,
    i.isFree = FALSE,
    i.ID_Title = ia.ID_Title,
    i.InPrint = TRUE,
    i.Active = TRUE,
    i.WhenFirstInPrint = iif(i.WhenFirstInPrint IS NULL,Now(),i.WhenFirstInPrint),
    i.WhenLastSourced = Now(),
    i.ID_Option = ia.ID_Option,
    i.OptCode = ia.OptionCode,
    i.OptDescr = ia.GrpItemDescr,
    i.ID_ItemType = ia.ID_ItemType,
    i.ITypDescr = ia.ITypeDescr,
    i.ITypOptCode = OptionJoin(ia.GroupTitleCode,ia.OptionCode,"/"),
    i.ITypOptDescr = OptionJoin(ia.ITypeDescr,OptionJoin(ia.GroupTitleDescr,ia.OptionDescr," / "), " / "),
    i.ITypOptSort = ia.ITypOptSort,
    i.ID_ShipCode = ia.ID_ShipCode,
    i.Cost = ia.PriceCost,
    i.PriceOurs = ia.PriceCust,
    i.PriceList = ia.PriceList,
    i.Sources = i.Sources&"."&ia.CatAbbr,
    i.WhenLastInPrint = ia.CatDateAvail,
    i.Supplier_CatNum_Group = ia.Supplier_CatNum_Group
    WHERE i.Pulled AND i.isFree AND (NOT i.Active);

Step 7

qryVb_Append_Items_New creates new Items records for any new items not yet taken care of.

  • qryVb_Items_toAppend:
    SELECT
    ia.CatNum,
    ia.CatExt,
    ia.CatKey,
    ia.ID_Title,
    ia.CatDateAvail AS WhenFirstInPrint,
    Now() AS WhenCreated,
    False AS Pulled,
    False AS isFree,
    True AS InPrint,
    True AS Active,
    Now() AS WhenLastSourced,
    ia.ID_Option,
    ia.OptionCode AS OptCode,
    ia.GrpItemDescr AS OptDescr,
    ia.ID_ItemType,
    ia.ITypeDescr AS ITypDescr,
    OptionJoin(ia.GroupTitleCode,ia.OptionCode,"/") AS ITypOptCode,
    OptionJoin(ia.ITypeDescr,OptionJoin(ia.GroupTitleDescr,ia.OptionDescr," / ")," / ") AS ITypOptDescr,
    ia.ITypOptSort,
    ia.ID_ShipCode,
    ia.PriceCost AS Cost,
    ia.PriceCust AS PriceOurs,
    ia.PriceList,
    "." & ia.CatAbbr AS Sources,
    ia.CatDateAvail AS WhenLastInPrint,
    ia.Supplier_CatNum_Group
    FROM [Calc Items Avail] AS ia
    WHERE ia.ID_Item IS NULL;
  • qryVb_Append_Items_New:
    INSERT INTO Items * FROM qryVb_Items_toAppend;

Phase III

This is just error-checking, to make sure everything got taken care of.

Step 8

qryVb_Items_Avail_NeedNew contains any catalog numbers which do not already exist in the Items table, as an error check. We know something about the process is broken because it is coming up non-empty.

  • qryVb_Items_Avail_NeedNew
    SELECT ia.CatNum
    FROM [Calc Items Avail] AS ia
    LEFT JOIN Items AS i ON ia.CatKey=i.CatKey
    WHERE i.ID IS NULL
    ORDER BY ia.CatNum;

The choice of i.ID as a filter is somewhat arbitrary; the point is to find records in [Calc Items Avail] with no matching records in Items.

The resulting recordset does not include items which exist but need updating; it is only items which do not appear to have been created in Items yet, and thus need new or recycled Items records.