Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    336
    Thanks
    2
    Thanked 4 Times in 4 Posts

    Update inventory (2003)

    I am new to Access and find it pretty daunting. I am making my way through some very large books and am not looking for anyone to design by database for me because l enjoy the tinkering. But I am hoping someone can point me in the right direction at the moment.

    I am designing a database to keep track of a rather large private wine collection (several thousand bottles). (Not my collection, regrettably.) I have four tables: tblWineID, tblWineInventory, tblWinePurchases, and tblWineUse. Each table, of course, has a primary key, and I have set up relationships. Here's my question: For each wine, I want the number of bottles in tblWineInventory to equal the number of bottles in tblWinePurchases minus the number of bottles in tblWineUsage. I was trying to figure out how to do that in tables, but now I am beginning to suspect that the best way to do that is in a Query. Is that right? If so, I'll direct my reading toward query design.

    Here is more information on the tables and their primary keys, if that would help. All tables except tblWineID have multiple fields as a primary key. The primary keys are as follows:
    tblWineID: WineID (autonumber)
    tblWineInventory: WineID, Vintage
    tblWinePurchases: WineID, Vintage, PurchaseDate
    tblWineUse: WineID, Vintage, DateUsed

    As I said, I am a complete Access newby, so just a nudge in the right direction would help. Meanwhile, I have lots more reading to do.

    Fafner

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update inventory (2003)

    What does a record in tblWineInventory represent? A single bottle, or all bottles of a specific wine/vintage combination (such as 2001 Lytton Springs Zinfandel)
    And do you want to keep a history of the collection, or just store the present state?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    336
    Thanks
    2
    Thanked 4 Times in 4 Posts

    Re: Update inventory (2003)

    Thanks for your quick reply.

    Good question. A record in tblWineInventory represents all bottles of a specific wine and vintage. And I am not interested in keeping a history, just the present state. I originally started with one table with all possible fields for each wine, and then split this table into two (inventory and purchases) and then into four. Here are the fields in each table, with the field or fields of the primary key in parentheses. (The field called Uzit represents whether or not this wine needs to be used soon. The field called WRD represents white, red, or dessert.)

    tblWineID: (WineID), WRD, Country, Region, Subregion, WineName, Producer, Grapes, Notes
    tblWineInventory: (WineID, Vintage), Quantity, Bin, Uzit, Notes, Value, ValueDate
    tblWinePurchases: (WineID, Vintage, PurchaseDate), Source, BottleSize, Quantity, Price, Notes
    tblWineUse: (WineID, Vintage, DateUsed), Quantity, Notes

    Fafner

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update inventory (2003)

    Strictly speaking, you don't need the Quantity field in tblWineInventory, since it can be derived from tblWinePurchases and tblWineUse. You can create a totals query based on tblWinePurchases that groups by WineID and Vintage and sums on Quantity, and a similar query based on tblWineUse. You can then create a query based on tblWineInventory and the two totals queries, joined on WineID and Vintage (Left Join from tblWineInventory), and calculate the difference between the SumOfQuantity fields from the totals queries.
    This query will not be updateable, since it involves totals queries, so you can't use it as record source for a form in which records are to be edited, but you can use it as record source for reports, and you can use DLookup to retrieve values from it in a form in which records can be edited.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    336
    Thanks
    2
    Thanked 4 Times in 4 Posts

    Re: Update inventory (2003)

    Thank you! This helps a lot. This is the direction I will work in. I appreciate it very much.

    Otherwise, do the table designs more or less make sense, as far as you can tell from the information I've given?

    Fafner

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update inventory (2003)

    There are several different designs imaginable, but this one should work. You may want some extra fields in tblWineInventory: DrinkFrom and DrinkUntil (dates or years), and perhaps BottleSize.

    You could create auxiliary tables for Country, Region etc., so that the user can select those from a combo box.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    336
    Thanks
    2
    Thanked 4 Times in 4 Posts

    Re: Update inventory (2003)

    I like the idea of the auxiliary tables. Thanks again for your help.

    Fafner

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update inventory (2003)

    Check out

    http://allenbrowne.com/AppInventory.html

    for description and code that may help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •