when querying, how do you remove duplicate records within a group

[Guest]

i'm a bit confused. hopefully i can detail the problem correctly. i'm tring
to remove duplicate group records within a broader query.

Field 1 Field 2 Field 3
0500010 001-10-01-01 12485
0500010 002-05-01-01 24564
0500045 001-10-01-01 12485
0500045 004-11-05-02 12485
0500008 003-10-01-05 32554
0500008 003-10-01-05 24564

I want to remove the fourth record because Field 3 is shown more than once
within Field 1 (both are 0500045 - Field 1 is my group). I am fine with
Field 3 being shown more than once as long as Field 1 is a different value.
If Field 2 did not exist, it would be simple to group both fields and you
would have no duplicate records, but I need the information from Field 2 and
when you group all three fields, I am left with my current problem. How do
you do fix this in access?

 

[Marshall Barton]

i'm a bit confused. hopefully i can detail the problem correctly. i'm tring
to remove duplicate group records within a broader query.

Field 1 Field 2 Field 3
0500010 001-10-01-01 12485
0500010 002-05-01-01 24564
0500045 001-10-01-01 12485
0500045 004-11-05-02 12485
0500008 003-10-01-05 32554
0500008 003-10-01-05 24564

I want to remove the fourth record because Field 3 is shown more than once
within Field 1 (both are 0500045 - Field 1 is my group). I am fine with
Field 3 being shown more than once as long as Field 1 is a different value.
If Field 2 did not exist, it would be simple to group both fields and you
would have no duplicate records, but I need the information from Field 2 and
when you group all three fields, I am left with my current problem. How do
you do fix this in access?

If you don't care which value you get for field 2, then you
can use the First function for field 2:

SELECT fld1, First(fld2) As Sample, fld3
FROM . . .
GROUP By fls1, fld3

If that's not what you want, please explain it in more
detail.

 

[Guest]

Thanks for the reply Marsh. That's basically what I'm looking for, but it
didn't work. I ended up with more records that I started with. I don't
care which value I get for Field 2, so I can't figure out why it isn't
working.

SELECT fld1, First(fld2) As Sample, fld3. what does the "As Sample" mean?

If you have any ideas what I'm doing wrong, please let me know. Thanks.

 

[John Vinson]

i'm a bit confused. hopefully i can detail the problem correctly. i'm tring
to remove duplicate group records within a broader query.

Field 1 Field 2 Field 3
0500010 001-10-01-01 12485
0500010 002-05-01-01 24564
0500045 001-10-01-01 12485
0500045 004-11-05-02 12485
0500008 003-10-01-05 32554
0500008 003-10-01-05 24564

I want to remove the fourth record because Field 3 is shown more than once
within Field 1 (both are 0500045 - Field 1 is my group). I am fine with
Field 3 being shown more than once as long as Field 1 is a different value.
If Field 2 did not exist, it would be simple to group both fields and you
would have no duplicate records, but I need the information from Field 2 and
when you group all three fields, I am left with my current problem. How do
you do fix this in access?

You say "you need the information from Field2" but you also say you
want to see only one value of Field2. This sounds like a
contradiction! If you want to see only one of the two records, which
value of Field2 do you want to see? 001-10-01-01 or 004-11-05-02? Or
does it matter?

If you don't care which you see, use a Totals query grouping by Field1
and Field3, and use First as the Totals operator for Field2. It will
show whichever value is first in disk storage order (basically, an
arbitrary and uncontrollable choice).


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Marshall Barton]

You should post an exact Copy/Paste of the query you're
using so I can make sure we're talking about the same
things.

I can't tell from " more records that I started with", but
maybe the FROM clause is messed up??

As Sample simply assigns the name of the calculated field.

 

[Guest]

Marsh,

It's working now. I'm not sure what I was doing wrong, but things are good
now. I appreciate your help.

You should post an exact Copy/Paste of the query you're
using so I can make sure we're talking about the same
things.

I can't tell from " more records that I started with", but
maybe the FROM clause is messed up??

As Sample simply assigns the name of the calculated field.
--
Marsh
MVP [MS Access]

Thanks for the reply Marsh. That's basically what I'm looking for, but it
didn't work. I ended up with more records that I started with. I don't
care which value I get for Field 2, so I can't figure out why it isn't
working.

SELECT fld1, First(fld2) As Sample, fld3. what does the "As Sample" mean?

If you have any ideas what I'm doing wrong, please let me know. Thanks.

 

[Guest]

John,

Thanks for the input. The First operator works perfectly. I guess the
solution was simpler than I thought.