Mar 11 2005, 05:20 PM
I'm trying to figure out the best way to set this up:
ProdID autonum PK
MatchID number (same as ProdID)
It has around 100,000 unique records. Each Product can have more than 1 related products that is also in tblProducts, so I want to make a table called tblMatches with a list of all the Products that go with each ProdID. Like this:
Each product has its own unique set of matching parts. Am I setting this up right?
It seems like I don't need to have MatchID in the products table because it is the same value as ProdID, but I'm back and forth on this.
Ideally, I would like to have a subdata sheet where when I hit the + sign on the tblProducts, I would get a list of matching products and their ProdID.
Mar 11 2005, 06:16 PM
Something like this should work:
- ProdID [PK] A#
- Product text
- Other Product Info
- MatchID [PK] A#
- ProdID [FK] --> tblProducts
- MatchProdID [FK] --> tblProducts
Mar 14 2005, 11:54 AM
I was going to set it up the way you suggest, but the problem I have is that Access creates a table named tblProducts_1 if I try to create two relationships from tblProducts to tblMatch. I guess it's sort of a lookup table. This doesn't seem to be efficient to me, but I guess it's way, eh?
Mar 14 2005, 01:58 PM
Why do you think it's inefficient? It doesn't not create a new table, just another reference to the same table.
This allows you to have 2 lookup tables for the price of one
Mar 14 2005, 03:11 PM
Oh...so it's just a reference to the same table. I thought it was a duplicate table. Since this is the largest table in my DB, I didn't want to double it. Ok then...I will do it that way.
Mar 15 2005, 05:33 PM
Althought it was a good idea, I kept getting "bad reference"errors trying to do it that way. So, what I did was make two queries qryPartList and qryMatches. They are identical queries with just ProdID and Product (Ascending). I used one query as a lookup for ProductID in the Match table and the other query as a lookup for the MatchPartID. For some reason, Access does not work well when two lookups refer to the same fields on the same table. So here is the setup for tblMatch:
- MatchID [PK] A#
- ProdID [FK] --> lookup qryPartlist
- MatchProdID --> lookup qryMatches (not a key field)
I thought I would post this so you can see what works
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here