Prev: Replacing individual characters in a field to something else.
Next: Numeric Field Overflow Error
From: Brent on 30 Apr 2010 09:12
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
From: Daryl S on 30 Apr 2010 10:05
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
From: Brent on 30 Apr 2010 16:01
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
From: John W. Vinson on 30 Apr 2010 17:34
On Fri, 30 Apr 2010 13:01:03 -0700, Brent <Brent(a)discussions.microsoft.com>
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]
From: Brent on 30 Apr 2010 19:54
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 <Brent(a)discussions.microsoft.com>
> 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]
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Replacing individual characters in a field to something else.
Next: Numeric Field Overflow Error