query works but error message in report

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Hi, I am building queries for duplicate payments reports. I have the query
working but when I go to run it in the report I get an error message (error
3079) in help. The query SQL are as follows

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.
 
You will have to specify the error message description, as the number only
returns "Application-defined or object-defined error"

--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/

Tony said:
Hi, I am building queries for duplicate payments reports. I have the query
working but when I go to run it in the report I get an error message (error
3079) in help. The query SQL are as follows

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
Click to expand...
(((Min(DuplicateSubQuery2dr.SupplierNumber)) said:
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.
Click to expand...
 
Hi, sorry, I got busy typing and forgot to write the message.
The error message when I try to open the report is "The specified field
'DuplicateQuery2dr.InvoiceNumber' could refer to more than one table listed
in the FROM clause of your SQL statement"
I find this strange as the query runs with no problem and shows the correct
result. Thanks again, Tony.

[MVP] S.Clark said:
You will have to specify the error message description, as the number only
returns "Application-defined or object-defined error"

--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/

Tony said:
Hi, I am building queries for duplicate payments reports. I have the query
working but when I go to run it in the report I get an error message (error
3079) in help. The query SQL are as follows

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
Click to expand...
(((Min(DuplicateSubQuery2dr.SupplierNumber)) said:
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.
Click to expand...
Click to expand...
 
Back
Top