Totals Query Total: First - returning wrong record (image)

[Rx_]

The underlying table is determining the First record once the GroupBy has been completed.
Users may not enter records in order. A status field (and associated Order field) determins the status. In a custom make table, only the latest status needs to be displayed.

A Pre-Sort table was created. Sure enough, the order looks perfect.
Now, if only the first record for each Well_ID (much like a Customer_ID) could be output. Instead, the First reverts to the underlying table and the order that the user entered the records for each Well_ID.

Searching this forum and many other forums, this seems to be a common question. And, one lacking a good answer. I am fustrated and ready to just write a VBA solution. I woul be very greatful for an explanation or solution.

 

[pbaldy]

Does this help?

http://support.microsoft.com/kb/208190

 

[Rx_]

Thank you very much. This article absolutely confirms the cause of the problem.

But, using Min on the Order does not pull the record, it only pulls the min value from that group. Since the rest of the fields have numeric data, it can pull values from a different record based on the order the user entered data.

The TOP option only returns the first ID_Wells record. It might be possible to put a TOP SQL statement (surrounded by parens) in the criteria of the Req_Fin column. I have not figured that out just yet.

 

[boblarson]

Does this help?

http://support.microsoft.com/kb/208190

Just a note about that article. The statement about DFirst and DLast is slightly misleading. I wish they would have explaned that DFirst gets the very first record entered within the constraints of the table or query based on the criteria and the DLast gets the very last record entered (with the same constraints).

 

[pbaldy]

Is this what you're after?

http://www.baldyweb.com/LastValue.htm

 

[Rx_]

Thanks, that sparked an idea and helped me reach a preliminary solution.
Using a Group By for the Wells_ID (much like a CustomerID) and then the Min for the Status, that query only returned the two unique fields.

The result query was joined back on the first query. This time, the First only responded to the single record returned by the join instead of the first record in the underlying table.

The 9_1_Team_Permit_SHLBHL at that point could even be converted from a Totals query to a standard query.

Just an update: Finished the validation. This process was query sub-step 9 in a 12 step reporting tool for a complex report. Worked like a champ. The key is to filter down the requirements as shown in the example on the previous post and as the modified view on this attachment. Thanks everyone. Now I know what to avoid and how to approach the solution.