Access Query - "Duplicate Records Selected"

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

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 -

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.
 
Thanks for responding, however, I may not have been clear. Currently access
is outputing 2 records .... 1 with each "ture" value. Query: select code
0and<1000
Click to expand...

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.
 
Thanks for responding, however, I may not have been clear. Currently access
is outputing 2 records .... 1 with each "ture" value. Query: select code

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.
Click to expand...

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

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

for a sample.
 
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
 
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.
Click to expand...

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