For no reason other than it was included in Office 2010, I chose Access to implement a home consumables inventory database. I think I have jumped off the deep end before learning to swim. This is my first attempt at using Access. Nevertheless, I have gotten well along in the DB’s implementation – having thus far produced 8 tables, 4 forms, 5 queries and 6 reports. I don’t know SQL, so I’m using the wizards, Expression Builder, VBA and other design aids built into Access. I am now stuck on a Restock report.
Background: The basic DB design is embodied in four tables, joined in one-to-many relationships. The top table is a table of categories (Categories table – example: batteries). Each category may have many subcategories (Subcategory table – example: AAA, AA, C, etc. batteries). Each subcategory may have many products (Product table – example: Kirkland AA Batteries, 40 count). Each product may have many instances (Items table – example: Kirkland AA Batteries bought 4/15/13, expires 10/5/18, Kirkland AA Batteries bought 1/30/14, expires 2/13/20.)
The Subcategory table includes ShelfLife , ConsumptionRate and a TrackByContainer flag. It is at the subcategory that stocking decisions must be made. TrackByContainer is set to True for items that are consumed a container at a time, like a package of bread mix. It is set to False for items that are consumed by unit, like batteries. So ConsumptionRate means packages per month for items that are consumed and tracked by container and it means units per month for items that are consumed and tracked by unit.
TargetStockQty: [ConsumptionRate]*[ShelfLife]
And inventory is a count of packages for items tracked by container and a count of units for items tracked by unit. Thus inventory is either a Count of items or a Sum of item sizes [ContainerSize] in the Items table, by subcategory, as a function of the TrackByContainer flag.
ActualStockQty: IIf([TrackByContainer]=True,[Count Of Item],[Sum Of ContainerSize])
I want to generate a report of subcategories which need to be restocked. I have produced a very nice report which shows current and target stock quantities for all subcategories and stock items. The problem I’ve run into is how to produce a report which only includes subcategories which need to be restocked.
It seems to be a chicken-and-egg type problem. I have a “group by” query which includes columns for the correct Count and Sum calculations. But Access doesn’t seem to allow these values to be used in the “Criteria” row of the query design page. This makes perfect sense, in hindsight. (At this point, I’m at risk of expressing my ignorance, which is quite easy.) Conceptually then, what I need is a two-stage query. The first will include all joined rows and perform the counts and sums. The second will then use the counts and sums in criteria expressions to exclude all subcategories which don’t need to be restocked. I have read about “stacked queries”, “subqueries”, and “inner queries”, but don’t know if these would work or how to implement them.
So my questions are:
1. What is the best conceptual design for solving this problem?
2. Can it be implemented in Access using just the included design aids such as builders and VBA, or must I resort to learning SQL?
I can supply a sample database if that will help.
Note: This is not going to be a huge database, so performance shouldn't be a particular concern.