A better way? (1 Viewer)

[Demetious]

I have frmInventoryDetails to input inventory items that I need to link each item to a project. The problem is, each item is used in multiple projects. Right now I have a text box on the form and I have a caption that reads;
"Project Name: Please seperate project name with a comma" this is stored in fldProject in tbl.Inventory.
Then my query for rptProjectA uses the criteria;
tblInventory
fldProject
like "*ProjectA*"
This displays all of the components in my inventory for Project A only. I only need to copy/paste and change my form criteria to use it on ProjectB...C...etc.
Does anyone have a better way of doing this? I wanted to use a multiple value box box but I understand now why that is a bad idea. Thanks.

 

[Mr. B]

The first thing that gets my attention is the fact that you have many items that can be related to many projects. This immediately indicates a many-to-many relationship.

You must design your data structure to allow for managing the many-to-many relationship.

A many-to-many relationship requires the use of a "linking" or "join" table. This is a table where you can link each project with many items. Assuming that you have an AutoNumber type field in your projects table and one in your Items table, then the "join" table all you would have to have is a ProjectID field and a ItemsID field. Then you can define the links between multiple projects with the necessary items.

Hope this helps.

 

[Demetious]

Mr. B,
So tblInventory already has a relationship with my tblTransactions which allows me to add/delete inventory. I would then need to create an additional field in my tblInventory with an autonumber in it and link that directly to my tblProjects in a one (tblInventory) to many (tblProject) relationship correct? I will play around with that but I am still unsure how that allows me to get multiple values on my form with a single box.

EDIT:
Ok nm, that won't work, I forgot you can only have one Autonumber. Back to the drawing board.

 

[Mr. B]

If you have a transactions table than it sounds like you can simply have a Number field (long integer type) and store the ProjecID and then have a number field (again long integer type) and store the Id of each Item. This should give you the list of items for any project.