Filtering in a Query

[Eddie Mason]

Hi All,

I have tables in a database that store Customers, products and date ordered. What I want to do if it is possible is create a query that gives me the customer, each product ordered and the last date ordered.

Example: if the tables held the following data:

Customer Product Date Ordered
Smith A 01/01/2000
Smith A 12/12/2000
Smith B 06/01/2000
Smith B 12/03/2002
Smith B 01/01/2003
Smith C 07/04/2001
Jones A 14/03/2002
Jones B 16/08/2002
Jones B 23/12/2002

The information I want to display is:

Smith A 12/12/2000
Smith B 01/01/2003
Smith C 07/04/2001
Jones A 14/03/2002
Jones B 13/12/2002

I hope you can help with this one?

Regards

Eddie

 

[Rich_Lovina]

If you have your three fields in the one table you have a 'flat' database which will not do what you have asked...
Have a look at the sample Northwinds and read the Help files on database relationships.

Customer needs a ref no in an Orders table.

 

[Jon K]

In a new query, drag the three fields to three columns. Click on the Totals button on the tool bar so that a Total: row appears in the grid.

In the Customer and Product columns, select Group By in the Total: row. In the Date Ordered column, select Max in the Total: row.

 

[Rich_Lovina]

Agreed, one way, but still safer to set up Customer details in Customer table and Orders details with Cust_ID being the linking field in a separate table as per Northwinds sample db.

As later on customers might want to be seen by town, country, etc. and Orders might want to be seen by date, time, town or country. The Group By must be listed in the right order and then ascending/descending, etc. selected.

 

[Eddie Mason]

Hi All,

Many, many thanks for the help you have given me, I really am grateful. The tables were not flat file but quite well normalised. However they were Lotus Approach tables which I had linked to an Access database to extract information and thanks to your help I can know achieve this.

Again many thanks for your support.

Regards

Eddie

 

[Rich_Lovina]

I dragged myself kickin & screamin away from Lotus into Access two yrs ago. Found it better to create an ascii text file of Lotus stuff, then re-build in Access, running tandem systems for a while. The linking methodology has some limitations. Using spare text version also gives time for a re-think on fundamentals for the new Access version.

Good luck, you'll be annoyed at some of the Approach short-cuts like finding duplicates & deleting oldest dupe. Auto click in Approach c.f. programming forever in Access. Then again Access makes you think more towards the bigger picture of yr dbase.

Good luck