Inventory

[Nemacol]

Hello all, Thank you for taking the time to read my post.

I am currently working on an inventory system for the Food and Bev department. The overall goal will be to have a master table with all items, and be able to pull inventory count sheets from this master table for each venue. There are 14 venues here and obviously each item can be used at more than one venue (burgers are sold at more than one venue, same beer at more than one bar, etc).

The only solution i have come up with is to add 14 columns to the master sheet and mark true or false for each item and venue.
Then to get the inventory sheet that they will use will be:

Select * from Master
Where Venue3 = "true";


Then export each to a report or excel sheet for the manual count.

I wanted to know if it is possible to do this without having 14 extra columns in the master sheet.. or if it is even worth trying to do so.

I thought there should be a relation table where
0000000000011 = Venue 1 and Venue 2


1000000000011 = Venue 1, Venue 2, and Venue 14

Etc... but that seems needlessly messy for entering all of this in and I am not exactly sure how to make that work (to be honest).


Anyone have a good idea for me?

If i am not being detailed enough i apologize, please ask and i will be as clear and detailed as i can.

Thanks much for your time!
Nem

[June7]

I don't understand your inventory. You inventory burgers - not lettuce, tomatoes, pickles, beef, mustard, etc.?

Not using the 14-column method would mean going with:

tbleItemsVenue
ItemID (foreign key)
VenueID (foreign key)

Query for items related to a venue means using the venue ID as filter criteria. This setup allows more versatility for reporting output and adding venues.

[Nemacol]

The inventory will have every item in it that FnB uses. I was just using burgers as an example.

I am going with your example of the:
tbleItemsVenue
ItemID (foreign key)
VenueID (foreign key).

see how it goes for me. thank you for the Feedback.