Can't find last order with data fields in a query

R

Rolf Rosenquist

Simplified, I have one customer table with customer no. and name and one
order table with customer no, order no, valid date from and valid date to.
That means that the order (or contract) is valid from one date to another.

I cannot get a query where I want one row for each customer, showing only
his last order with order no and both dates.

I have tried setting the order no to Max in the query, but get one row for
each order, not every customers last order. The only chance to get one row
for every customer is to set also the two dates to Max, but then I get the
wrong data, not the specific last order per customer, but the last date from
all his orders and that is not the right data. The data should come from
every customer's last order.

Anyone care for helping me with this? I am already grey haired.... :)

/ Rolf
 
D

Douglas J. Steele

It's not clear to me how you can determine the last order: is it by the
highest order number, or by the highest Valid Date From or some other means?

What you can do is create a query that returns only the customer number and
the last order number, then join the results of that query to a query that
joins the two tables so that you can retrieve all of the relevant
information associated with the customer and the last order.
 
R

Rolf Rosenquist

I mean the highest order number. Now I have got the intended result when I
used 'Last' in the query instead. But I had to put the 'Last' also on both
dates as well.

But I think your solution with another query is the better one. How do you
mean to join the second query?
/ Rolf
 
P

PC Datasheet

Create a query based on both tables. Include the fields Customer name, Order
No, Valid date From and Valid Date to in the query. Click on the Sigma
(looks like capital E) button in the toolbar at the top. Under Valid Date
To, change Group By to Max. Run the query and you should get what you want.
 
R

Rolf Rosenquist

No, sorry, but that gave me only the Max values for each field. They were
not data of the same record.
/ Rolf
 
D

Douglas J. Steele

Let's assume you've got a query along the lines of:

SELECT CustomerNumber, Max(OrderNumber) As LatestOrder
FROM OrderTable
GROUP BY CustomerNumber

Save that query, say as qryLatestOrder

You can now join qryLatestOrder to your other two tables in a query that
will only return the details associated with the latest order number for
each customer.
 
Top