Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

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

    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?
    "Heading for the deep end"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding highest 'many in 'one-to-many' query (Access 2003 winxp sp2)

    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.

  3. #3
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding highest 'many in 'one-to-many' query (Access 2003 winxp sp2)

    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.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Finding highest 'many in 'one-to-many' query (

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

    Many Thanks,

    Allan
    "Heading for the deep end"

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding highest 'many in 'one-to-many' query (

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •