Query with most recent (Access 97 SR-1)

Topic

New Reply

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

Replies

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

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

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

Thanks Hans. It worked!

Reply | Quote