Best way to produce unique records

[Shep]

Hello, hoping for some help on the following scenario:

I need to produce a query which results in one record for each part number.

In a sample table of purchasing history (tblPODetails):
Fields:
PartNumber, Description, Cost, DatePurchased

We have purchased any given part number numerous times over the years. From time to time, descriptions have changed, as have our cost and of course the date received.

I am attempting to create a query which gives me a list of part numbers with no duplicates, showing the part number, description and cost by max date received.

The resulting description field would of course be the one in use for the latest received date.

I am not having much luck. When I add more fields to the totals query (other than part number and max date received), I get duplicates. I know this should be simple for me...

 

[Sean O'Halloran]

Final goal?

When you get the dataset you want from the query, what do you wnat to do with it? Print it, edit data?

Sean

 

[Shep]

I wish to store the dataset as a new table.

 

[Sean O'Halloran]

..no expert - except at trial and error

This is advice from a novice; my hope is that my reply will generate some thinking on your part and a more useful post from another forum member.

My first thought is that you need to focus on the 'latest date' field by creating a query that pulls that set of records. That should solve the duplication, since there can be only one 'latest date' (assuming you have no part whose description was updated twice in one day).

How to do it? I'm not sure; look at the 'Totals' functions in the query design window. I'd try using the 'last' function on the date field.

Again, if it was me, I'd probably search the forum on 'Totals' 'query' 'dates'. Also, since I have nothing but self-training, no local expert, and no time to attend classes, I often resort to the following process:

1. Search forum for info. Find "A", "B", "C"
2. Try "A"
3. "A" fails
4. Drink coffee
5. Try "B"
6. "B" fails
7. Repeat step 4.
8. Try "C"
9. "C" fails
10. Go back to step 1.

As you can see from my process, I'm not a programmer. A programmer would have written the above in 'If - Then" statements and plugged in loops.

HTH - and good luck - Sean

 

[Shep]

Thanks, same conditions apply here
I couldn't find anything good by searching, that's my first attack always...

Actually I ran max date with only the primary key added in the query, saved it and compared it to the original table, nailed it to within 2-300 dupes which I hand edited out.

Thanks much