Results 1 to 3 of 3
  1. #1
    AlanH is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2014

    Two-stage query?

    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.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011

    I don't really like your design but let's leave that be for a minute.

    If you have a query that is performing the calculations you want, but you are simply not able to apply criteria to calculated fields that's one problem.

    If you are not able to construct a query that gives you the results you want (except for applying criteria) at all that's a different problem entirely.

    If it's the former you can create a query based on your query and apply a critera to your calculated field(s)

    Here's the basic problem with your database. You are not really tracking expendature's from your stock.

    What you are doing (let's take your AA batteries as an example)

    You are recording a purchase of 40 AA batteries on 1/1/2014
    Every time you take items out of that pack you are updating a field to record the total used items.

    So if you take two batteries out on 1/2/2014 and 1/3/2014
    You are updating the same field once, to 2, the second time, presumably to 4, so your calculation for items left would be: containerUOM - Expended

    I can't tell from your post if you're trying to properly normalize this database but it's not right now.

    Let's say for the sake of argument you buy 2 cases of ice mountain bottled water on 1/1/2014
    Let's say that bottles are taken out of each case before one or the other is completely used.

    You would have to update each 'received' item every time a bottle was used from either instead of, say, recording just that you took 10 bottles out on 1/2/2014 and another 7 bottles out on 1/3/2014. In your construction you would have to apply these to a specific case purchased, in a normalized database you would just record the item and usage.

    At any rate, if you can build a query that shows your usage the way you want (and the remaining quantity) you can build a query on that query (treating the 'original' query as though it were a table) and start applying criteria to it just as you would any other table

  3. #3
    AlanH is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2014
    Quote Originally Posted by rpeare View Post

    If it's the former you can create a query based on your query and apply a critera to your calculated field(s)
    My delay in replying was not because I didn't appreciate your response. It's just that I got pulled away right after my post, and am just now getting back to the DB. Yes, it was the former. I did as you suggested and added a second query based on the first, and applied the selection criteria in the second. All is good. Thank you very much for taking the time to answer what I'm sure was a trivial question for you. As I said, I'm a novice with Access.

    Thank you also for your insight and suggestion on how to track inventory usage. I'm going to incorporate your suggestion into my design. I really appreciate your help - no doubt this will not be my last question. Have a good evening.


Please reply to this thread with any new information or opinions.

Similar Threads

  1. multi stage timer
    By jaykappy in forum Programming
    Replies: 5
    Last Post: 08-01-2013, 03:11 PM
  2. Replies: 2
    Last Post: 10-31-2012, 11:52 AM
  3. End User Stage
    By KevinMCB in forum Database Design
    Replies: 2
    Last Post: 03-02-2011, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Tech Forums: Microsoft Office Forums