Question on duplicates

[Guest]

I've created a query from two tables, where the query has criteria from both
tables. What I would like for the query to do, is only give me one instance
of any duplicated MBRNO, and all the records/instances that are not
duplicated. I've looked at other posts and can't seem to find one that fits
my need. I would like to make the query be as simple as possible, because the
criteria will need to be changed often. One MBRNO can have multiple LOCATION
in CAV_MEMBERDETL, but since the query is joined at LOCATION, the query
produces duplicate MBRNO records. I've listed the SQL. Thanks in advance for
any assistance.
SELECT CAV_MEMBERDETL.MBRNO INTO [Line Section Member Address]
FROM CAV_LOCINFODETL INNER JOIN CAV_MEMBERDETL ON CAV_LOCINFODETL.LOCATION =
CAV_MEMBERDETL.LOCATION
WHERE (((CAV_LOCINFODETL.LINESECTION) Between "000000021115" And
"000000022000") AND ((CAV_MEMBERDETL.DISCDATE)="000000"));

 

[Guest]

SELECT distinct CAV_MEMBERDETL.MBRNO INTO [Line Section Member Address]
FROM CAV_LOCINFODETL INNER JOIN CAV_MEMBERDETL ON CAV_LOCINFODETL.LOCATION =
CAV_MEMBERDETL.LOCATION
WHERE (((CAV_LOCINFODETL.LINESECTION) Between "000000021115" And
"000000022000") AND ((CAV_MEMBERDETL.DISCDATE)="000000"));

I added one little word to your sql. However it won't work if any of the
fields are Memos and a couple of other rarer data types.

 

[Guest]

Jerry,
This is much apreciated. It works great. Funny how one small detail can make
a huge change. Again, thank you.

SELECT distinct CAV_MEMBERDETL.MBRNO INTO [Line Section Member Address]
FROM CAV_LOCINFODETL INNER JOIN CAV_MEMBERDETL ON CAV_LOCINFODETL.LOCATION =
CAV_MEMBERDETL.LOCATION
WHERE (((CAV_LOCINFODETL.LINESECTION) Between "000000021115" And
"000000022000") AND ((CAV_MEMBERDETL.DISCDATE)="000000"));

I added one little word to your sql. However it won't work if any of the
fields are Memos and a couple of other rarer data types.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I've created a query from two tables, where the query has criteria from both
tables. What I would like for the query to do, is only give me one instance
of any duplicated MBRNO, and all the records/instances that are not
duplicated. I've looked at other posts and can't seem to find one that fits
my need. I would like to make the query be as simple as possible, because the
criteria will need to be changed often. One MBRNO can have multiple LOCATION
in CAV_MEMBERDETL, but since the query is joined at LOCATION, the query
produces duplicate MBRNO records. I've listed the SQL. Thanks in advance for
any assistance.
SELECT CAV_MEMBERDETL.MBRNO INTO [Line Section Member Address]
FROM CAV_LOCINFODETL INNER JOIN CAV_MEMBERDETL ON CAV_LOCINFODETL.LOCATION =
CAV_MEMBERDETL.LOCATION
WHERE (((CAV_LOCINFODETL.LINESECTION) Between "000000021115" And
"000000022000") AND ((CAV_MEMBERDETL.DISCDATE)="000000"));