Help with bundled item inventory lists

[fiesta]

Hello,
I am mostly done with a simple inventory database I put together to make life easier at work. The structure and scope of the database is pretty simple; we sell gift collections, the gift collections have between 5-10 items in them. The database is designed to let us select a gift, it pulls a list of items in the gift and outputs that list in a stylized way that can be printed and sent with the gift. Simple and works fine.

What I'm trying to now do is create a "Pick list" generator where we can go to a form and input the number of each gift we are trying to fulfill (3 of gift A, 7 of gift B, etc) and out will pop an inventory list of what we need to fulfill the gifts (so 3 of each item from gift A, 7 of each item for gift B, etc).

I'm able to make it work for one gift at a time using a simple query with a calculated field and criteria expression but am having a lot of trouble conceptualizing where to start to get this to work with multiple gifts at the same time. Any push in the right direction would be greatly appreciated.
Thanks!

[davegri]

Can you post your DB? Hard to be very specific without seeing the tables and some data.

[fiesta]

Thank's for the reply. Because it is already in use and populated with information I will have to clean up a copy before doing so. I'm more looking for a general idea of where to start with such a project, independent of the data or specific use-case. Out of the office now, I'll be sure to do so tomorrow. Thanks again!

[orange]

Here's a link to a 26 min youtube video on Stock Management that should help put some of the concepts and design ideas together.

Inventory and Stock management tend to be complex.
Good luck.

[fiesta]

Thanks for the video!
I ended up solving it with a bit of half compromise work around. Ideally the form would update with something like a continuous form that lists the currently available gifts and have a text box next to the gift to input quantity. Instead I just went with several combo boxes that then run grouped union queries for the output. I'm sure there are better ways to handle this with skipping null inputs so it does not display items with zero quantity, but for now it works
SQL Below if anyone is curious:

SELECT Products.Product, Sum(SQ.[Total Quantity]) AS [SumOfTotal Quantity]


FROM


(SELECT Products.Product, [quantity]*[Forms]![testpiklist]![qty] AS [Total Quantity], Products.Unit, [Forms]![testpiklist]![qty] AS [Number of gifts]
FROM Gifts INNER JOIN (Products INNER JOIN GiftDetails ON Products.ID = GiftDetails.FKProductID) ON Gifts.GiftID = GiftDetails.FKGiftID
WHERE (((GiftDetails.FKGiftID)=[Forms]![testpiklist]![Combo0]))


UNION ALL SELECT Products.Product, [quantity]*[Forms]![testpiklist]![qty1] AS [Total Quantity], Products.Unit, [Forms]![testpiklist]![qty1] AS [Number of gifts]
FROM Gifts INNER JOIN (Products INNER JOIN GiftDetails ON Products.ID = GiftDetails.FKProductID) ON Gifts.GiftID = GiftDetails.FKGiftID
WHERE (((GiftDetails.FKGiftID)=[Forms]![testpiklist]![Combo1]))


UNION ALL SELECT Products.Product, [quantity]*[Forms]![testpiklist]![qty2] AS [Total Quantity], Products.Unit, [Forms]![testpiklist]![qty2] AS [Number of gifts]
FROM Gifts INNER JOIN (Products INNER JOIN GiftDetails ON Products.ID = GiftDetails.FKProductID) ON Gifts.GiftID = GiftDetails.FKGiftID
WHERE (((GiftDetails.FKGiftID)=[Forms]![testpiklist]![Combo2]))


) AS SQ


GROUP BY Products.Product;