To bring my question down to the simplest case - I have a table with
two records and two fields. The field names are FirstName and City.
If record 1 is Joe and Los Angeles and record 2 is Joe and San
Francisco, in my query, (no VBA), I would like to pull the first
record for each unique FirstName. I don't really care if I pick the
"Joe and Los Angeles" record or the "Joe and San Francisco" record but
I only want one unique occurrence of Joe with a city. Of course there
is a real business use for what I really need to do...

Thanks, Sheldon

Sheldon wrote:
Use a groupsby/totals query.

Salad <oil (AT) vinegar (DOT) com> wrote in
news:75mdnTR8RoZadezXnZ2dnUVZ_vZi4p2d (AT) earthlink (DOT) com:



--
Bob Quintal

PA is y I've altered my email address.

On Jul 30, 2:39*pm, Salad <o... (AT) vinegar (DOT) com> wrote:
I'm probably missing the obvious but GroupBy/Totals still shows me two
records. Can you be a little more specific on the query? Thanks.

Juzer Sheldon Potolsky <SHPsalm139 (AT) aol (DOT) com> napisa³

Select
FirstName
, Min(City) as FirstCity
From ...
Group By
FirstName

--
KN

Sheldon Potolsky wrote:
Uncheck city.

In my scenario, I'd want to see one entire record, so City would be
checked. But my actual scenario would be more like a table with two
records and four fields. The field names are FirstName, City, State
or Province, Country
So, if record 1 is Joe/Los Angeles/California/USA and
record 2 is Joe/Toronto/Ontario/Canada
I would like to pull one complete record for each unique FirstName.
i.e. In this case, I'd want to see
Joe/Los Angeles/California/USA or
Joe/Toronto/Ontario/Canada
I guess to get that, I'd need a MIN each for City, State or Province,
Country but my actual work problem uses at least one Expression so I
could not use MIN for that field. If my two records were in Table1,
maybe the best way to solve this would be to have an append query to
write just the FirstName field to Table2. So initially, Table2 would
have 1 record with "Joe" and the other three fields blank. Then I
would have an update query to populate the other three fields and
Access would choose either the Joe/Los Angeles/California/USA
combination or the one with Joe/Toronto/Ontario/Canada. I did test it
and it worked but wondering if this is the best/only solution. Thanks
for your ideas/input.

Sheldon