IF...Then in a query. 1

[Marc52]

I really need help with this; I am stuck. I have Access 97.

I have a table, "Orders," that shows orders made in 1998, 1999, 2000, and 2001. Some clients have ordered in all years, some haven't ordered for a years or so. I also have "Customer" and "Order details" tables.

I can run a query that shows orders for all years, and for specific years, but I am stuck as to how to run the following query.

I would like to run a query that will show the last order made by each client. It would go something like this:

If an order was made in 2001, then show that; If no order was made in 2001, then show 2000; if no order was made in 2000, then 1999; if no order was made in 1999, then 1998.

I don't know the proper syntax for this criteria. Can it even be done? Maybe with subqueries, or layered queries?

Is there something I need to put in a criteria box? Maybe under the "year ordered" column in the grid?

Any help will be greatly appreciated.

Thank you very much,

Marc

 

[Telsa]

Actually, you would create another "field" and put it in there. So in a blank field of your query, type for instance: LastOrder: iif([Year ordered] = 2001, 2001, IIf([year ordered] = 2000, 2000, IIF([year ordered] = 1999, 1999, Iif([year ordered] = 1998, 1998))))

Lots of imbedded IIfs... someone else might have a better idea on this than me though.

Mary

 

[JerryDennison]

Use a totals query. For the date ordered field set it's total property to Max.

 

[Marc52]

Telsa and JerryDennison,

Thank you for taking the time advise me. I have been trying you utilize your suggestions. So far, I have not yet gotten the desired results, but I think I am getting closer.

I will continue working with your ideas. I know the answer is in there somewhere.

Thank you both,

Marc

 

[petermeachem]

You could try this:-

My table is caleld text, Customer text, Year Number, Amount Currency.

We need to break the problem down.

1) Find the last order year for each customer. I'll put the sql in as it is easier than trying to explain the 'visual' way.

SELECT customer, Max(year) AS MaxYear
FROM test
GROUP BY customer;

Save this as query1.

2) Find the amounts for those customers and years. Start a new query, add table test, add query1 and link Customer with Customer and Maxyear with year. here is the sql

SELECT customer, year, amount
FROM test INNER JOIN Query1 ON (test.year = Query1.MaxYear) AND (test.customer = Query1.customer);

Does that do it?
Peter Meachem
peter@accuflight.com