Table structure

[mrenshaw]

I have a product return table and a code table. The product table was originally set up with 6 lookup fields for the codes as to there could be more than one thing wrong with the product. I know this is the wrong structure because of the redundant code information. The question is, How do I attach multiple codes to one record ID without multiple fields. The end result would be to query the defects based on the codes. Sort by code and give a count of each one.

I read a few threads on many to many relationships and this sounds like where I want to go but I’m a little confused on how to get there. I’m assuming I would have 3 tables:

Table 1 Table 2 Table 3
tbl_product_return tbl_codes tbl_codelist
record_ID code_ID code_ID
other info code_description record_ID


First thing is how to get the code ID and record ID to table 3 every time they select a defect or code on the form?

I’m going in the right direction as far as structure?

Any help would be greatly appreciated.

Michael

 

[The_Doc_Man]

To do what you describe can be done like this:

tblReturns
-- loReturnNumber (autonumber, prime key)
-- other descriptive data

tblReasons
-- loReasonCode (autonumber, prime key)
-- explanation of return code

tblReturnReason
-- loReturnNumber
-- loReasonCode

And make the key for the return-reason table a compound key based on the two numbers.

Now you need two preparatory queries because of a little confusion that Access runs into when you have multiple joins in a single query.

Make a query in which you join tblReturns to tblReturnReasons on loReturnNumber where you have all records from tblReturns and all matching records from tblReturnReasons. Make sure this is grouped by loReturnNumber or something else related to the return items.

Next make a query that joins the first query to the Reasons table on loReasonCode. Again, you want all the records from the first query and all matching records from the reasons table.

Now you can write a report based on that query that shows every returned item as a group header and every reason as a detail area item.

Turn this around a bit. Make a query that joins the ReturnReasons table to the Reasons table. If you group this by the reason codes, you can get counts of why things are returned.



Now, every time you accept an item, you can add a record for every applicable reason. To find out why an item is returned, you query tblReturns joined to tblReturnReason on loReturnNumber(where you have all returns and all return reasons that are equal) joined to tblReasons on loReasonCode where where you sort by the return number.

 

[Pat Hartman]

Many-to-Many sample

Take a look at the Many-to-Many sample db that I posted.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany

 

[mrenshaw]

Thanks

Thanks! I think I see the light now. I really appreciate your help.
Michael