Many to Many/lnktable/query

[Sharon]

I have a table of patents with the primary key of PatentMasterID. I have a
table with Inventors with the primary key as PatentInventorID. I have
created a link table using both of these fields as primary keys.

The PatentMaster table is joined to the link table by the field Patent
MasterID to include only rows where the joined field from both tables are
equal and the PatentInventor table is joined to the link table using the
PatentInventorID field with the join to include al records from the
PatentMaster table and only those from the link table where the joined fields
are equal.

In the query window, I have created another table that is PatentInventor1,
PatentInventor2, PatentInventor3, etc. I have identified the inventors as
Inventor1:InventorName from the PatentInventor table, Inventor2:InventorName
from the PatentInventor1 table and Inventor3:InventorName from the
PatentInventor2 table.

However, when I run the query, it shows two entries for PatentMasterID: one
with the first inventor (unfortunately in both fields: Inventor1 and
Inventor2) and the second with the second inventor in both fields. What am I
doing wrong?

Any help is appreciated.

 

[Tom van Stiphout]

On Tue, 9 Dec 2008 15:03:00 -0800, Sharon

Your db design seems sound: a M:M between Patents and Inventors.
However, you did not describe what you want the output of the query to
be, only what you tried and did not work.

-Tom.
Microsoft Access MVP

 

[John Spencer]

It sounds as if you need a ranking query to establish the InventorNames and
then a crosstab to transform them into Inventor1 to Inventor#n.

Save a query like the following as qInventorNumber
SELECT A.PatentMasterId, A.PatentInventorID
, Inventors.InventorName
, 1 + Count(B.PatentInventorID) as InventorNumber
FROM Inventors INNER JOIN (LinkTable as A LEFT JOIN LinkTable As B
ON A.PatentMasterID = B.PatentMasterID
AND A.PatentInventorID < B.PatentInventorID)
ON Inventors.InventorID = A.LinkTable.PatentInventorID
GROUP BY A.PatentMasterID, A.PatentInventorID

A Crosstab query would use the above query along with your PatentMaster table
to give you what I think are the desired results.

TRANSFORM First(q.InventorName) as fInventor
SELECT PM.PatentMasterID, PM.PatentDate
FROM PatentMaster as PM INNER JOIN qInventorNumber as Q
ON PM.PatentMasterID = Q.PatentMasterID
GROUP BY PM.PatentMasterID, PM.PatentDate
PIVOT q.InventorNumber

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County