• Query with most recent (Access 97 SR-1)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Query with most recent (Access 97 SR-1)

    Author
    Topic
    WSkanders1
    AskWoody Lounger
    May 16, 2003 at 9:11 pm #387650

    I have a database with 3 linked tables. One has the people (table1), one has the orders (table2), and one has the specifics of each order (table3). In the table3, each entry has a date. For example, when we get an order for the same service, we don’t create a new order. We just add a new line to the same order number (table2). How would I create a query with the person’s name (table1) and their most recent activity (table3). When I create the query, I’ve been using the criteria of Max for the date field in table 3. However, as soon as I add any other fields to the table, the person appears several times with mutliple requests. I hope this is clear. Please let me know if you need more info. Any help is greatly appreciated.

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      May 16, 2003 at 9:36 pm #677290

      First, create a query based on the three tables. Add only the primary key from the People table and the date field from the Order Details table. Make the query into a Totals query; leave the Totals option for the primary key as Group By, and set it to Max for the date field. Save this query, I’ll name it qryMaxDate for illustration purposes.

      Next, create another new query based on the three tables plus qryMaxDate. Join qryMaxDate to the People table on the primary key, and to the Order Details table on the date field. Add the fields you want to see to the query grid. This query will display each person with their most recent activity, plus other activities on the same day – so there may still be more than one record per person if there is more than one activity on the most recent date. If you want to return only one record per person, make this new query into a Totals query too, leave the Totals option for the fields from the People table and the date field as group by, and set it to First for the other fields. If you want to return a specific record for a person and date, set the sort order so that the desired record comes first.

      Reply | Quote
      • WSkanders1
        AskWoody Lounger
        May 19, 2003 at 6:36 pm #677930

        Thanks Hans. This appears to have worked well.

        Now I can’t figure out how to get a query with all the People who have never had an order before. If I just use the People table for the query everyone will show up but if I use the Order and Order Details tables in the query then the people with no orders won’t show up. I’m sure there’s an easy solution to this one, I’m just not sure what it is.

        Thanks again.

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          May 19, 2003 at 6:47 pm #677954

          The default behavior of joins between tables in a query is to return only those records whose values on either side of the join are equal – implying that there *is* a value. To modify this behavior, open the query in design view, and double click a join from the People table to one of the other items. Select the option that says “Return all records from People and …”, then click OK. You will see that the join line now exhibits an arrow from People to the other object. Repeat for the other joins (you may have to experiment a bit to get it right.)

          Reply | Quote
    Viewing 0 reply threads
    Reply To: Query with most recent (Access 97 SR-1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.