SQL

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

Can anyone tell me what is wrong with this SQL code?

SELECT vwPatentMastersReport.PatentMasterId,
vwPatentMastersReport.PatentInventorID, Inventors.InventorName, 1 +
Count(qryinventors2.PatentInventorID) as InventorNumber
FROM Inventors
INNER JOIN (lnktblMASTERIDINVENTORID as vwPatentMastersReport
LEFT JOIN lnktblMASTERIDINVENTORID As qryinventors2
ON vwPatentMastersReport.PatentMasterID =qryinventors2.PatentMasterID
AND vwPatentMastersReport.PatentInventorID < qryinventors2.PatentInventorID)
ON qryInventorsList.InventorID =
vwPatentMastersReport.lnktblMASTERIDINVENTORID.PatentInventorID
GROUP BY vwPatentMastersReport.PatentMasterID,
vwPatentMastersReport.PatentInventorID;
 
Every field that's in the SELECT list must either have an Aggregate function
(such as Count) applied to it, or else must be in the GROUP BY list. You've
got Inventors.InventorName in the SELECT list, but not in the GROUP BY list.

SELECT vwPatentMastersReport.PatentMasterId,
vwPatentMastersReport.PatentInventorID, Inventors.InventorName, 1 +
Count(qryinventors2.PatentInventorID) as InventorNumber
FROM Inventors
INNER JOIN (lnktblMASTERIDINVENTORID as vwPatentMastersReport
LEFT JOIN lnktblMASTERIDINVENTORID As qryinventors2
ON vwPatentMastersReport.PatentMasterID =qryinventors2.PatentMasterID
AND vwPatentMastersReport.PatentInventorID < qryinventors2.PatentInventorID)
ON qryInventorsList.InventorID =
vwPatentMastersReport.lnktblMASTERIDINVENTORID.PatentInventorID
GROUP BY vwPatentMastersReport.PatentMasterID,
vwPatentMastersReport.PatentInventorID, Inventors.InventorName

I believe Danny was implying that that's what the error message should have
said.
 
It didn't give me the results that I wanted but at least the query worked!
Let me explain what I am trying to do.

I have a table with patents listed (vwPatentMasterReport) and a table with
Inventors (vwPatentInventorsReport) which is a many-to-many relationship. I
have created the link table (as you can see by the code above) with
PatentMasterID and PatentInventorID as the primary keys. What I am trying to
do (I have been trying this for months with no success) is list the correct
inventors (in the right order) for each Patent.

For instance:

Patent1 - Inventors - Smith, Jones, Harrington, Devlin

Patent2 - Inventors - Devlin, Smith

Patent 3 - Inventors - Jones, Harrington, Smith

I seem to go round and round in circles. If anyone can help it would sure
be appreciated.

S
 
Back
Top