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
>0and<1000

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 <(E-Mail Removed)>
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
>>0and<1000
>
>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 <(E-Mail Removed)>
> 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
> >>0and<1000
> >
> >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 <(E-Mail Removed)>
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]