I'm trying to figure out the best way to set this up:

tblProducts
ProdID autonum PK
Product text
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:

tblMatches
MatchID number
MatchingProductID text
MatchReason lookup

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.

Hi M.

Something like this should work:

tblProducts
- ProdID [PK] A#
- Product text
- Other Product Info

tblMatch
- MatchID [PK] A#
- ProdID [FK] --> tblProducts
- MatchProdID [FK] --> tblProducts

HTH

Doug,
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?

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

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.

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:

tblMatch
- MatchID [PK] A#
- ProdID [FK] --> lookup qryPartlist
- MatchProdID --> lookup qryMatches (not a key field)
-MatchReason-->lookup values

I thought I would post this so you can see what works