Query to Select Only First Instance of Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
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.
 
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
 
Back
Top