Finding highest ‘many in ‘one-to-many’ query (Access 2003 winxp sp2)

Topic

New Reply

Hi all,

I have in my db an invoice form which shows the delivery docket related to the job being invoiced. In the event of the job having multiple delivery dockets, I see multiple invoices for the same job, one for each docket. How do I set the form’s query so that it returns only the highest numbered docket for each job?

Reply | Quote

Replies

Set the criteria for the docket number to

(SELECT Max(t.[DocketNo]) FROM [tblDockets] AS t WHERE t.[JobID] = [tblDockets].[JobID])

Replace DocketNo with the name of the docket number field, tblDockets with the name of the relevant table (or query), and JobID with the name of the field that uniquely identifies the job.

Reply | Quote

Once again Hans, right on the money! I don’t quite understand how it works but it does.

Many Thanks,

Allan

Reply | Quote

The solutions proposed by me and Patrick (which are basically the same) use a so-called subquery: the criteria for the docket field is a query-within-the-query that returns the highest docket number for the current job in the ‘main’ query. The subquery is written in SQL, the query language used by Access. The queries you design are also based on SQL, but Microsoft has written a user-friendly interface so that most of the time, you don’t see the SQL. But for a subquery it is unavoidable.

Reply | Quote

Create a query that returns all of the data you want from the invoice table. In the WHERE clause, include a query that returns the Max(Invoice ID) from the same table where a common field is equal to the set of invoices you are querying. For example:

SELECT invoice_fields (one of them being JobID
FROM tblInvoice AS i
WHERE i.InvoiceID = (SELECT Max(i1.InvoiceID) from tblInvoice AS i1 WHERE i1.JobID = i.JobID);

Without additional parameters, this query will return the highest numbered Invoice record for each job.

InvoiceID must be unique or the sub query could return arbitrary results.

Reply | Quote