Finding sub-groups of data automatically

[cjackson]

I need some help on list manipulation...

Below is an example of some data from our orders database: -


customer1
order_no_1 date1 product_a value
order_no_1 date1 product_b value
order_no_1 date1 product_c value
order_no_2 date2 product_a value
order_no_2 date2 product_d value
order_no_3 date3 prosuct_a value
order_no_3 date3 product_b value
order_no_3 date3 product_c value
customer2
order_no_1 date4 product_z value
etc etc

for each customer I want to be able to find all the products for the latest order by date.

I can do this on an individual basis but can't work out how to do it for all customers in one go.

Also it would be nice, but not essential, to find a customers average order value.

Thanks in advance for any help or advice

 

[RickSpr]

The solution depends very much on how this data is arranged into tables. It's possible (though poor relational design) that it's all in one table, or it may be broken up into as many as three tables (Customers, Orders, and Products Ordered).

For each of the data fields above, you need to specify the table name and the field name it's in. Also, specify whether you are looking to create a query that returns the data, a form or report that shows it, or VBA code that can search for it. Rick Sprague

 

[cjackson]

Thanks Rick,

Here is some more info: -

The data is held in two tables (Customers and orders linked by a Customer ID field).

I need a query that returns the details of the latest order only for all clients.

Hope this helps.

 

[Zelandakh]

To get the latest order no,

select max(orderno) from orders - this number is stored as zelandakh.

To select details of this order:

select customer, product, date, value
from
customers,
orders,
where
orders.orderno = zelandakh
order by date desc;

In the report you can do a summary to give the total or average order value by customer. Are you doing this in Access?

 

[cjackson]

Yer I was hoping to this in Access, although if required I can apply my earth shatering knowledge of SQL, as long as you understand when I say earth shatering I'm talking a very small amount of earth...

 

[Zelandakh]

You can do this in Access too...

New query. Add both customers and orders tables. Ensure there is a link between them.

Double click on the customer, orderno, date and value. Run this (use the ! button). This should give you the info you want.

Create a report to summarise it. Put a report footer on to do the average order and format it prettily.

If you get the info you want, go back to the query and change the view to SQL. You will see your Access query in SQL.

 

[cjackson]

I think I must be missing something basic here,

I've tried: -
select max(orderno)as last_order from orders

but this returns all teh orders for the customers as if i'd ran a normal select query, I also tried using the date as the max field but again it returned every order for the customers.

Any idea's?

 

[Zelandakh]

Because you are returning the maximum orderno in that row, rather than in the field I think.

If you create it visually in Access just selecting the orderno as your output then do a group by and choose maximum does this change the SQL text?

 

[RickSpr]

You want the latest order by date, so don't use Max(orderno) in your query, use Max(date).

I would do this with nested queries. Create a query with just the Orders table. Drop CustomerID in the grid, and then type the following expression in a blank column of the Field line:
LastOrderDate: Max(date)
Save this query as LatestOrder.

Now create another query with the Customers table, the Orders table, and the LatestOrder query. Draw join lines between the fields in the Orders table and the LatestOrder query. (You should already have a join line between Customers and Orders.) Drop into the grid any data you want from the Customers and Orders tables. Save this query as LatestProducts. This query returns what you're looking for. Rick Sprague

 

[cjackson]

Rick,

sorry its taken a while to get back to you, I tried what you suggested but cant get past the first stage.

I get the following error when I try and run the query with the expresion Lastorder: max(date): -

You tried to execute a query that doesn’t include the specified expression ‘CustomerID’ as part of an aggregate function

I also get this error if I just save the query and try and run it linked to the customers table.

Any idea's? I think were close

Oh more info, I've got no grouping or wheres etc on any fields.