Simple max query

CanWest

Registered User.
Local time
Today, 12:28
Joined
Sep 15, 2006
Messages
272
I am having trouble trying show ONLY the record with the highest value in a specific field. The query shows the record set that I wish to grab the record from but I can not figure out how to show only the record with the highest value in a specific field

I know this is probably easy. I have looked though the forums but could not find anything that was close.
 
Code:
SELECT tbl_ProgramInvoiceLineItem.ServiceID, tbl_ProgramInvoiceLineItem.ProgInvLineItemCount, tbl_ProgramInvoiceLineItem.ProgServiceAssocID, tbl_ProgramInvoiceLineItem.ProgInvID, tbl_ProgramInvoiceLineItem.ProgInvLineItemCount, tbl_ProgramInvoiceLineItem.ProgInvLineItemUnitCost, tbl_ProgramInvoiceLineItem.ProgInvLineItemTotalAmt, tbl_ProgramInvoiceLineItem.ProgInvLineItemPrgTotalCount, tbl_ProgramInvoiceLineItem.ProgramID, tbl_ProgramInvoiceLineItem.ProgInvLineItemPrgTotalAmt
FROM tbl_ProgramInvoiceLineItem
WHERE (((tbl_ProgramInvoiceLineItem.ServiceID)=0) AND ((tbl_ProgramInvoiceLineItem.ProgServiceAssocID)=0) AND ((tbl_ProgramInvoiceLineItem.ProgramID)=[Forms]![frm_Programs]![ProgramID]));

The field that I wish to get the highest valuse from and show that record is ProgInvLineItemPrgTotalAmt
 
If you're talking about getting the highest value of a particular field from a table and you are going to use this query in a report or form then there is really no need repeating this information several times as that will just slow down your query. Use the DMax() function in the record source of your control to get the max.

http://www.techonthenet.com/access/functions/domain/dmax.php
 
That or just change it to a TOP 1 query sorted DESC on that field.
 
If you're talking about getting the highest value of a particular field from a table and you are going to use this query in a report or form then there is really no need repeating this information several times as that will just slow down your query. Use the DMax() function in the record source of your control to get the max.

http://www.techonthenet.com/access/functions/domain/dmax.php

Not a report or form. I need to use this record to append to another table using the values in this record as a basis for some calculations for the appended record
 
Basically the value will remain the same for every record right? How many records approximately?
 
Basically the value will remain the same for every record right? How many records approximately?

What I need is only one record to show that being the one with the highest value in a specific field.
 
I think we've covered all the possible solutions above. Max option in the query, or DMax() function used in the query, or TOP 1 with records in DESC. Look at the links provided and Google some of the terms.
 
I think we've covered all the possible solutions above. Max option in the query, or DMax() function used in the query, or TOP 1 with records in DESC. Look at the links provided and Google some of the terms.

I could not get Max option in the query or DMax() function used in the query to work but TOP 1 with records in DESC worked perfectly. Many Thanks
 
For Info

To do this with Max requires 2 queries as Max is a field operation.
The first query just has max on the field to find that value the second joins that back to the table on that field to extract the rest of the record.

Brian
 
I could not get Max option in the query or DMax() function used in the query to work but TOP 1 with records in DESC worked perfectly. Many Thanks
Glad you got one of them working.
 

Users who are viewing this thread

Back
Top Bottom