Eliminating Duplicates in a Query

[amiscus]

I have a table that lists prospective sales as they travel through time in the sales process. Every month a snapshot is taken of the sales progress and uploaded to the table. This table has an autonumber as the primary key because the unique sales ID for all of these often duplicates when the sales process takes a month or more. This is a problem for all the other queries I want to run on the data wich really only look at the date to get a status report. However, I need to create a table taht lists all the sales that have a decision date in a current month. This does not change so I am getting multiple records back when I perform the Query.

For instance, I want to see all the sales that have a decision date in August. I set up a query to select all of those but when it returns I have multiples for every oppurtunity that was being worked on in more than one month so it might return back 3 instances of the same sale at different points in the sales process on for July, June and one for May. I only want the most recent instance of the oppurtunity to appear. Any help is greatly appreciated and if anything needs clarifying just ask and I'll do my best to explain further.

 

[Michael J Ross]

Hi

Use Max on the date something like

SELECT Tablename.Opportunityfield, Max(Tablename.Date) AS MaxOfDate
FROM Tablename
WHERE (((Tablename.Date) Between #8/1/2009# And #8/31/2009#))
GROUP BY Tablename.Opportunityfield;