Access Query - "Duplicate Records Selected"

Any help would be appreciated. When viewing records resulting from a
multiple table query, I discovered "Duplicate" records being selected.
However, all fields are not exactly the same, for the value selected from the
second table makes the record truly unique. How do I modify the query
statement to "output" only one occurance of the record? I tried unique value
for the "member number" field but the query still selected member 001 two
times.

--
Brent

Brent -

You need to tell Access how to decide which of the two fields is the one you
want output. This is usually done in a query by adding 'Totals' - use the
total button in query design view to add this row. Most of the fields will
have "Group by" in their totals row. For the field from the second table
that has more than one value, you need to choose something else. Some of the
options in this drop-list only work for numeric fields, like Sum or Average.
Others, like First, Max, etc. will work with other field types as well.
Select the appropriate one and run your query.

--
Daryl S


"Brent" wrote:

Any help would be appreciated. When viewing records resulting from a
multiple table query, I discovered "Duplicate" records being selected.
However, all fields are not exactly the same, for the value selected from the
second table makes the record truly unique. How do I modify the query
statement to "output" only one occurance of the record? I tried unique value
for the "member number" field but the query still selected member 001 two
times.

--
Brent

Thanks for responding, however, I may not have been clear. Currently access
is outputing 2 records .... 1 with each "ture" value. Query: select code
0and1000

Code is stored in a separate table from rec num name & street.

id rec num code name Street
001 5498 123 B.O. SAM 123 Main
002 5498 549 B.O. SAM 123 Main

Problem.... I want only one record in my output.
would actually like one name and address record with all qualifing codes....
123,549.

lastly, the output would be printed on labels with the code or codes printed
above.
--
Brent


"Daryl S" wrote:

Brent -

You need to tell Access how to decide which of the two fields is the one you
want output. This is usually done in a query by adding 'Totals' - use the
total button in query design view to add this row. Most of the fields will
have "Group by" in their totals row. For the field from the second table
that has more than one value, you need to choose something else. Some of the
options in this drop-list only work for numeric fields, like Sum or Average.
Others, like First, Max, etc. will work with other field types as well.
Select the appropriate one and run your query.

--
Daryl S


"Brent" wrote:

Any help would be appreciated. When viewing records resulting from a
multiple table query, I discovered "Duplicate" records being selected.
However, all fields are not exactly the same, for the value selected from the
second table makes the record truly unique. How do I modify the query
statement to "output" only one occurance of the record? I tried unique value
for the "member number" field but the query still selected member 001 two
times.

--
Brent

On Fri, 30 Apr 2010 13:01:03 -0700, Brent
wrote:

Thanks for responding, however, I may not have been clear. Currently access
is outputing 2 records .... 1 with each "ture" value. Query: select code
0and1000

Code is stored in a separate table from rec num name & street.

id rec num code name Street
001 5498 123 B.O. SAM 123 Main
002 5498 549 B.O. SAM 123 Main

Problem.... I want only one record in my output.
would actually like one name and address record with all qualifing codes....
123,549.

lastly, the output would be printed on labels with the code or codes printed
above.

You'll need some VBA code to make this practical. See

http://www.mvps.org/access/modules/mdl0004.htm

for a sample.
--

John W. Vinson [MVP]

John,

Thanks for the reference. Considering that I'm a beginner, this looks a
little overwhelming for me.

Is there a way for me to eliminate the "duplicate rec" from the query. If
possible, would like to eliminate it based on the "rec num" field. I'll deal
with multiply occuring codes later.

Thanks

--
Brent


"John W. Vinson" wrote:

On Fri, 30 Apr 2010 13:01:03 -0700, Brent
wrote:

Thanks for responding, however, I may not have been clear. Currently access
is outputing 2 records .... 1 with each "ture" value. Query: select code
0and1000

Code is stored in a separate table from rec num name & street.

id rec num code name Street
001 5498 123 B.O. SAM 123 Main
002 5498 549 B.O. SAM 123 Main

Problem.... I want only one record in my output.
would actually like one name and address record with all qualifing codes....
123,549.

lastly, the output would be printed on labels with the code or codes printed
above.

You'll need some VBA code to make this practical. See

http://www.mvps.org/access/modules/mdl0004.htm

for a sample.
--

John W. Vinson [MVP]
.

On Fri, 30 Apr 2010 16:54:06 -0700, Brent
wrote:

John,

Thanks for the reference. Considering that I'm a beginner, this looks a
little overwhelming for me.

Is there a way for me to eliminate the "duplicate rec" from the query. If
possible, would like to eliminate it based on the "rec num" field. I'll deal
with multiply occuring codes later.

Sure. Make it a Totals query; group by the four other fields, and choose First
(or Min or Max) for code to show at least one of the codes.

The VBA in the website is not at all hard to use. Simply copy and paste the
code from the "code start" through the "code end" lines into a new Module;
save the module under the name basConcatenate (any name other than
fConcatChild); choose Debug... Compile from the menu; and follow the
instructions. In this case you'ld put

Codes: fConcatChild("yourtablename", "RecNum", "Code", "Long", [RecNum])

in a vacant Field cell.

--

John W. Vinson [MVP]