MAX Function works in one Query but not another

[TOPSie]

I suspect this is a case of not being able to see the wood for the trees. I have two Queries - the first produces a report and the second acts as the Record Source for a ListBox on a form.
In the ListBox case the MAX function for the item [Grade No] successfully records only the highest [Grade No] for the associated Client/Date etc.
However on the Report two lines for [Grade No] appear for the appropriate Client/Date etc.
I am obviously missing something, but as you can see the Queries are quite longwinded:-

Report Query

SELECT DISTINCT tblTempPurchaseTransactions.PaymentNumber,
tblTempPurchaseTransactions.PurchaseTransactionNum ber,
tblTempPurchaseTransactions.SelectForPayment,
tblPurchaseAccounts.SupplierName,
tblTempPurchaseTransactions.Date,
tblTempPurchaseTransactions.SupplierRef,
tblTempPurchaseTransactions.OurReference,
tblTempPurchaseTransactions.EggReceiptNumber,
tblTempPurchaseTransactions.PaymentRef, tblPaymentTypes.Name,
tblEggReceipts.EggReceiptID,
tblEggReceiptDetails.EggStockType2,
tblEggReceiptDetails.EggStockType, tblEggReceiptDetails.AmountGross,
tblEggStockType.EggStockTypeName, tblEggStockGroup.EggStockGroupName,
tblEggStockTypeName.Name, tblEggReceipts.AmountSupplement,
tblEggReceipts.ChequeNumber,
tblTempPurchaseTransactions.ChequeNumber,
tblTempPurchaseTransactions.PaymentType,
tblTempPurchaseTransactions.AmountGross,
tblTempPurchaseTransactions.Type,
IIf([tbltempPurchaseTransactions.type]="pay" Or [tbltempPurchaseTransactions.type]="CRD",tbltempPurchaseTransactions.amountgross, 0) AS Payment,
IIf([tbltempPurchaseTransactions.type]="INV",tbltempPurchaseTransactions.amountgross, 0) AS Invoices,
Max(tblGradingReport.GradingNumber) AS [Grade No],
tblGradingReport.GradingPrice,
tblGradingReport.GradingCost,
IIf([gradingprice]>0,[gradingprice],tblEggReceiptDetails.amountgross) AS Expr1,
tblGradingReport.TotalEggsReceived,
tblEggReceiptDetails.TotalEggsReceived,
IIf(tblgradingreport.totaleggsreceived>0,tblgradin greport.totaleggsreceived,tbleggreceiptdetails.tot aleggsreceived) AS Eggs
FROM (((tblPurchaseAccounts
INNER JOIN tblTempPurchaseTransactions
ON tblPurchaseAccounts.SupplierID = tblTempPurchaseTransactions.SupplierID)
LEFT JOIN tblPaymentTypes ON tblTempPurchaseTransactions.PaymentType = tblPaymentTypes.ID)
LEFT JOIN ((((tblEggReceipts
LEFT JOIN tblEggReceiptDetails
ON tblEggReceipts.EggReceiptID = tblEggReceiptDetails.EggReceiptID)
LEFT JOIN tblEggStockType ON tblEggReceiptDetails.EggStockType2 = tblEggStockType.EggStockTypeID)
LEFT JOIN tblEggStockTypeName ON tblEggStockType.EggStockTypeName = tblEggStockTypeName.ID)
LEFT JOIN tblEggStockGroup
ON tblEggStockType.EggStockGroup = tblEggStockGroup.EggStockGroupID)
ON tblTempPurchaseTransactions.EggReceiptNumber = tblEggReceipts.EggReceiptNumber)
LEFT JOIN tblGradingReport
ON tblEggReceiptDetails.EggReceiptDetailID = tblGradingReport.EggReceiptDetailID
WHERE (((tblTempPurchaseTransactions.Type)<>"pay") AND ((tblGradingReport.GradingNumber)="Max"))
GROUP BY tblTempPurchaseTransactions.PaymentNumber,
tblTempPurchaseTransactions.PurchaseTransactionNum ber,
tblTempPurchaseTransactions.SelectForPayment,
tblPurchaseAccounts.SupplierName,
tblTempPurchaseTransactions.Date,
tblTempPurchaseTransactions.SupplierRef,
tblTempPurchaseTransactions.OurReference,
tblTempPurchaseTransactions.EggReceiptNumber,
tblTempPurchaseTransactions.PaymentRef,
tblPaymentTypes.Name,
tblEggReceipts.EggReceiptID,
tblEggReceiptDetails.EggStockType2,
tblEggReceiptDetails.EggStockType,
tblEggReceiptDetails.AmountGross,
tblEggStockType.EggStockTypeName,
tblEggStockGroup.EggStockGroupName,
tblEggStockTypeName.Name,
tblEggReceipts.AmountSupplement, tblEggReceipts.ChequeNumber, tblTempPurchaseTransactions.ChequeNumber, tblTempPurchaseTransactions.PaymentType, tblTempPurchaseTransactions.AmountGross, tblTempPurchaseTransactions.Type, tblGradingReport.GradingPrice, tblGradingReport.GradingCost, tblGradingReport.TotalEggsReceived, tblEggReceiptDetails.TotalEggsReceived;






List box Query


SELECT tblEggReceipts.EggReceiptID,
tblEggReceipts.Date AS [Receipt Date],
tblPurchaseAccounts.SupplierName,
Sum(tblGradingReport.Total) AS Graded,
tblEggReceipts.Paid,
Max(tblGradingReport.GradingNumber) AS [Grade No],
Max(tblGradingReport.Date) AS [Grade Date],
First(qryEggReceiptCases.SumOfTotal) AS cases,
tblEggReceipts.PaymentRef, tblEggReceipts.SupplierID,
tblEggReceipts.EggReceiptNumber,
tblEggReceipts.PurchaseTransactionNumber,
First(tblPurchaseTransactions.PaymentNumber) AS FirstOfPaymentNumber,
First(tblPurchaseAccounts.Email) AS FirstOfEmail,
First(tblPurchaseAccounts.EmailGradingReport) AS FirstOfEmailGradingReport


FROM (tblPurchaseTransactions
RIGHT JOIN ((tblEggReceiptDetails
INNER JOIN (tblEggReceipts
INNER JOIN tblPurchaseAccounts
ON tblEggReceipts.SupplierID = tblPurchaseAccounts.SupplierID)
ON tblEggReceiptDetails.EggReceiptID = tblEggReceipts.EggReceiptID)
LEFT JOIN tblGradingReport ON tblEggReceiptDetails.EggReceiptDetailID = tblGradingReport.EggReceiptDetailID)
ON tblPurchaseTransactions.PurchaseTransactionNumber = tblEggReceipts.PurchaseTransactionNumber)
INNER JOIN qryEggReceiptCases ON tblEggReceipts.EggReceiptID = qryEggReceiptCases.EggReceiptID
WHERE (((tblEggReceipts.Posted)=True))
GROUP BY tblEggReceipts.EggReceiptID,
tblEggReceipts.Date,
tblPurchaseAccounts.SupplierName,
tblEggReceipts.Paid,
tblEggReceipts.PaymentRef,
tblEggReceipts.SupplierID,
tblEggReceipts.EggReceiptNumber,
tblEggReceipts.PurchaseTransactionNumber
HAVING (((Sum(tblGradingReport.Total))>0))
ORDER BY tblEggReceipts.EggReceiptID DESC ,
tblPurchaseAccounts.SupplierName;

[Welshgasman]

I am not even going to attempt to try and read those huge blocks of data.
I will just say that if one work and one does not, then the data supplied is different?

[Micron]

You don't say if the max value is the same for both records, but I suspect it is. When you use outer joins, you're asking for all records from one table plus those from other tables that match. This can cause multiple records because multiple records satisfy the query fields involved. The max value will be included for each record.

Two approaches:
Copy the problem query and trim it back by removing linked tables one at a time and test. When the max function works you won't necessarily know if it was because of the last removed table or because of 2 or more that you removed. You could then try putting the table back in (verify the problem returns) and removed fields one at a time as you did tables until the issue goes away again. This could indicate which field is the issue.

Create a single query that will not return multiple records and use that in your final query using equal joins. Your first query might need DISTINCT or DISTINCT ROW predicates to eliminate dupes.

Next time, please post code within code tags (# on posting toolbar).