Query to Select Only First Instance of Field

[Glenn1203]

I have a table with 6000 records and a couple dozen fields. I want to
consolidate the table to contain only distinct instances of one field
("CircuitID"), but all the other fields in each row are different so I can't
use DISTINCT. So, I've arranged the data so that the record containing the
first instance of each CircuitID is the one I want to keep. The logic would
be, once a specific instance of CircuitID has been found, do not select any
more rows with that same CircuitID.
It seems like it should be easy, but I can't figure out a way to do it. How
would I do this?

Thanks, Glenn

 

[Jerry Whittle]

What do you mean by "first instance of each CircuitID"? First and Last have
little meaning in a relational database unless you first sort the records by
something like a date. Also if by "arranged the data" you mean that you've
entered the data into a table in the way you want to see it, you may be in
for a major disappointment. The next time you open the table, the records
could be in any order.

The point is that you must have something to sort on in a query before you
can even consider using something like First or Last. Do any of the other
fields include something like a date or control number which would indicate
which is the latest record?

 

[Glenn1203]

Thanks Jerry. Yes, I have another field with the correct priority order
(1-4). There will never be more than one CircuitID with the same priority
order. So if I sort on Priority Ascending, then the record for the CircuitID
I want to keep will always be the one I want to pull out of the table.

 

[John Spencer]

Does it matter if the records are updatable? Does it matter which values
you get? If the answer to both questions is no, then you should b able to
use a totals (aggregate) query.

SELECT CircuitID
, First(AnotherField) as fAnotherField
, First(FieldThree) as fThree
, First(Field82) as f82
FROM YourTable
GROUP BY CircuitID

In the query grid, bring in all the fields you want to see
Select View: Totals from the Menu
Change Group By to FIRST under all the fields except Circuit ID - leave it
as Group By