Keep one of many duplicate rows, delete the others

[dnnr]

I have a list of projects and am creating a query that tells me their duration based on beginning and ending dates. These beginning and ending dates can be edited by the user, and frequently, there are multiple beginning dates. When I query the projects, instead of getting unique project numbers on the left hand side to create unique rows, I get many of the same projects with different start dates. I would like to use the FIRST start date only for every project and not have multiple rows with the same projects being counted multiple times.

Is there any way to delete the excess rows and keep the original row? I've read a lot about using DISTINCT and UNIQUE INDEX with SQL but it seems as though these are only available for tables and not queries. Bright ideas, anyone?

 

[CraigDolphin]

In the design view of your query, right click on your start date field and choose Totals from the menu. Change the Group By total option to 'Min' for that field.

 

[dnnr]

I couldn't tell you why, but for some reason that doesn't work. I found this website as well and done everything without any luck:

http://office.microsoft.com/en-us/access/HA010345581033.aspx#13

I think the problem could be that the date format is not standard. It is:
MM/DD/YYYY HH:MM:SS

So maybe access can't differentiate between their supposed values. Anyone know how to parse information in access?

 

[dnnr]

Okay, so I found a site about parsing dates. Problem is, I've never used code in access. How would one apply the code that is offered at this link?

http://www.codeguru.com/forum/showthread.php?t=373173

 

[dnnr]

Okay, that actually shouldn't be a problem because I just changed the format to match it. So it shouldn't have difficulty reading it.

I've tried min and first in the total field under the start date column with no success...

 

[CraigDolphin]

Can you post a stripped down version of your db?

 

[ByteMyzer]

Try the following SQL (substitute the highlighted text with the actual table/field names):

DELETE T1.*
FROM [b][i]MyTable[/i][/b] T1
WHERE T1.[b][i]StartDate[/i][/b] >
 (SELECT MIN(T2.[b][i]StartDate[/i][/b])
  FROM [b][i]MyTable[/i][/b] T2
  WHERE T2.[b][i]Project[/i][/b] = T1.[b][i]Project[/i][/b]
 )
;