Selecting only the first record for each site.

[londonkiwi]

I want to select only the first records (in my query) for each site eg:

SITE_NO SITE_NAME START_DATE AADT HEAVY
10000550 BAY PADOCK ROAD 01/11/2000 80 2.81
10000550 BAY PADOCK ROAD 05/11/2000 80 4.29
00300300 AVOCA STREET 18/10/2000 1430 5.26
00300300 AVOCA STREET 22/10/2000 1430 3.96

becomes
10000550 BAY PADOCK ROAD 01/11/2000 80 2.81
00300300 AVOCA STREET 18/10/2000 1430 5.26
etc

i've tried setting up a Function (it pick every second record, but not necessarily srating with the first ONE!)
How do you do it in a query!!!!!!!!!!!!!

*************************************
Public Function PlusOne(var As Variant)

Static i As Integer
i = i + 1
PlusOne = i
End Function
**************************************
The query sql is below:
SELECT TC_SUMMARY.SITE_NO, TC_INDEX.SITE_NAME, TC_SUMMARY.START_DATE, TC_SUMMARY.AADT, (100*(([H_7day]+[M_7day])/[total_7day])) AS HEAVY
FROM TC_INDEX INNER JOIN TC_SUMMARY ON TC_INDEX.SITE_NO = TC_SUMMARY.SITE_NO
WHERE (((TC_SUMMARY.AADT)>0) AND ((TC_SUMMARY.LANE)=1) AND ((PlusOne([Start_Date]) Mod 2)=0));

 

[BNPMike]

Use Select Distinct in the SQL or count something else grouped by the things you only want one of.

Or you can use DFirst or Topvalues. mike.stephens@bnpparibas.com