repost-queries work but report gives error message

T

Tony

Hi, I am building queries for duplicate payments reports. The query
works ok and comes up with the correct detail but when I go to run it in the
report I get an error message "The specified field
'DuplicateQuery2dr.InvoiceNumber' could refer to more than one table listed
in the FROM clause of your SQL statement" (error3079) in help. The query SQL
are as follows and I am trying to show the 4th query in report form

1.SELECT PaymentTable.InvoiceNumber, PaymentTable.InvoiceDate,
PaymentTable.InvoiceAmount, PaymentTable.SupplierNumber,
PaymentTable.SupplierName
FROM PaymentTable
WHERE (((PaymentTable.InvoiceNumber) In (SELECT [InvoiceNumber] FROM
[PaymentTable] As Tmp GROUP BY [InvoiceNumber],[InvoiceDate],[InvoiceAmount]
HAVING Count(*)>1 And [InvoiceDate] = [PaymentTable].[InvoiceDate] And
[InvoiceAmount] = [PaymentTable].[InvoiceAmount])) AND
((PaymentTable.InvoiceAmount)>0))
ORDER BY PaymentTable.InvoiceNumber, PaymentTable.InvoiceDate,
PaymentTable.InvoiceAmount;

2.SELECT Min(DuplicateSubQuery2dr.SupplierNumber) AS MinOfSupplierNumber,
Max(DuplicateSubQuery2dr.SupplierNumber) AS MaxOfSupplierNumber,
Min(DuplicateSubQuery2dr.SupplierName) AS MinOfSupplierName,
Max(DuplicateSubQuery2dr.SupplierName) AS MaxOfSupplierName,
DuplicateSubQuery2dr.InvoiceNumber, DuplicateSubQuery2dr.InvoiceDate,
DuplicateSubQuery2dr.InvoiceAmount
FROM DuplicateSubQuery2dr
GROUP BY DuplicateSubQuery2dr.InvoiceNumber,
DuplicateSubQuery2dr.InvoiceDate, DuplicateSubQuery2dr.InvoiceAmount
HAVING (((Min(DuplicateSubQuery2dr.SupplierNumber))<>Max([SupplierNumber]))
AND ((Max(DuplicateSubQuery2dr.SupplierNumber))<>Min([SupplierNumber])) AND
((Min(DuplicateSubQuery2dr.SupplierName))<>Max([SupplierName])) AND
((Max(DuplicateSubQuery2dr.SupplierName))<>Min([SupplierName])));

3.SELECT PaymentTable.SupplierNumber, PaymentTable.SupplierName,
PaymentTable.InvoiceNumber, PaymentTable.InvoiceDate,
PaymentTable.InvoiceAmount
FROM PaymentTable INNER JOIN DuplicateQuery2dr ON
PaymentTable.InvoiceNumber=DuplicateQuery2dr.InvoiceNumber
GROUP BY PaymentTable.SupplierNumber, PaymentTable.SupplierName,
PaymentTable.InvoiceNumber, PaymentTable.InvoiceDate,
PaymentTable.InvoiceAmount
HAVING (((PaymentTable.InvoiceAmount)<0));

4.SELECT DuplicateQuery2dr.MinOfSupplierNumber,
DuplicateQuery2dr.MaxOfSupplierNumber, DuplicateQuery2dr.MinOfSupplierName,
DuplicateQuery2dr.MaxOfSupplierName, DuplicateQuery2dr.InvoiceNumber,
DuplicateQuery2dr.InvoiceDate, DuplicateQuery2dr.InvoiceAmount,
DuplicateQuery2cr.SupplierNumber, DuplicateQuery2cr.SupplierName,
DuplicateQuery2cr.InvoiceNumber, DuplicateQuery2cr.InvoiceDate,
DuplicateQuery2cr.InvoiceAmount
FROM DuplicateQuery2dr INNER JOIN DuplicateQuery2cr ON
DuplicateQuery2dr.InvoiceNumber=DuplicateQuery2cr.InvoiceNumber
GROUP BY DuplicateQuery2dr.MinOfSupplierNumber,
DuplicateQuery2dr.MaxOfSupplierNumber, DuplicateQuery2dr.MinOfSupplierName,
DuplicateQuery2dr.MaxOfSupplierName, DuplicateQuery2dr.InvoiceNumber,
DuplicateQuery2dr.InvoiceDate, DuplicateQuery2dr.InvoiceAmount,
DuplicateQuery2cr.SupplierNumber, DuplicateQuery2cr.SupplierName,
DuplicateQuery2cr.InvoiceNumber, DuplicateQuery2cr.InvoiceDate,
DuplicateQuery2cr.InvoiceAmount;

Thank you if anyone can help, Tony.
 
Top