Help a newbie

[lsanchez]

I need help. Now I've written a post so that some one could help retrieve current information and they told me to use Dmax. Great but I don't know how to use Dmax. Basically I need step by step instructions.

This is an example of what my table looks like:


Date of Order --Product--- UNIT COST
20-May-05----- XYZM---- $8.87
20-Jun-05------ XYZM ----$9.39
21-Jul-05 ------XYZM -----$9.24
19-Mar-05----- ACE ------$7.85
20-May-05 ----ACE------- $7.89
20-Jun-05 ----ACE-------- $8.01
21-Jul-05 -----ACE------- $8.13
22-Jul-05------ACE ------$8.05

What I need is a report that will show me the last ordered price of the product.

Date of Order --Product-- UNIT COST
21-Jul-05------ XYZM ---$9.24
22-Jul-05 ------ACE ----$8.05

How do I get a query that will allow me to retrieve this information? I'm still in training and need step by step intructions. Please Help!!!!

 

[neileg]

I wouldn't use DMax. I would use a nested query, like this.

Create a query that has the date and the product. Turn this into a Totals query but set the operator to MAX and not SUM. Click on the Σ button to turn a select query into a totals query. This will give you the latest date for each product.

Now create a second query. Add in the first query and your product table. Join the two using both the date and the product ID. Add into the grid any of the information that you need from the product table. Job done.

 

[lsanchez]

Maybe I should elaborate a bit more. I import all my information periodically to the same table. sometimes the prices changes sometimes it doesn't. The date is the only field that changes everytime.

Date of Order --Product--- UNIT COST
20-May-05----- XYZM---- $8.87
20-Jun-05------ XYZM ----$9.39
21-Jul-05 ------XYZM -----$9.24
19-Mar-05----- ACE ------$7.85
20-May-05 ----ACE------- $7.89
20-Jun-05 ----ACE-------- $8.01
21-Jul-05 -----ACE------- $8.13
22-Jul-05------ACE ------$8.05

The other table that is created is the one with the products of which I am looking for the most current price for. e.g. XYZ, ACE....(I don't only have to product I have hundreds and what I do now is look up each price individually.)

So I link the two table and I get the above information when I only want:

Date of Order --Product-- UNIT COST
21-Jul-05------ XYZM ---$9.24
22-Jul-05 ------ACE ----$8.05

When I try the sum an error message comes up.

 

[lsanchez]

I re-read your suggestion and I finally got what I need....

Thanks a million!

 

[lsanchez]

Neil,

I figured out what I needed to do but now I have duplicates because it's picking out a date from each year, and not the latest date.

 

[neileg]

How are you holding your dates? If you have a date/time field you should only get one result.