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;