Lots of Records

[Guest]

When I query a database I get a total of 61510 records, when in fact, it
should be more like 3000. Please help me eliminate the duplicate records. I
tried changing the joins from inner to outer, but received a syntax error.

BSYMCPP AND BSYMPTP ARE THE TABLES I QUERY FROM.
Fields that are being used are cpcpt and ptde30.
Cpcpt is a number field, for ex 95819, and ptde30 is a description field,
such as ankle fracture.

This is the Select Statement:

SELECT BSYMCPP.CPCPT, BSYMPTP.PTDE30
FROM BSYMCPP INNER JOIN BSYMPTP ON (BSYMCPP.[CPHSP#] = BSYMPTP.[PTHSP#]) AND
(BSYMCPP.CPCPT = BSYMPTP.[PTPRO#])
ORDER BY BSYMCPP.CPCPT, BSYMPTP.PTDE30;

 

[John Vinson]

When I query a database I get a total of 61510 records, when in fact, it
should be more like 3000. Please help me eliminate the duplicate records. I
tried changing the joins from inner to outer, but received a syntax error.

BSYMCPP AND BSYMPTP ARE THE TABLES I QUERY FROM.
Fields that are being used are cpcpt and ptde30.
Cpcpt is a number field, for ex 95819, and ptde30 is a description field,
such as ankle fracture.

This is the Select Statement:

SELECT BSYMCPP.CPCPT, BSYMPTP.PTDE30
FROM BSYMCPP INNER JOIN BSYMPTP ON (BSYMCPP.[CPHSP#] = BSYMPTP.[PTHSP#]) AND
(BSYMCPP.CPCPT = BSYMPTP.[PTPRO#])
ORDER BY BSYMCPP.CPCPT, BSYMPTP.PTDE30;

It sounds like the joins may not be on appropriate fields. What are
the Primary Keys of the two tables? Do you have a two-field
relationship defined between the tables using the joins in the JOIN
clause? Is PTPRO# in fact a valid foreign key linked to CPCPT, and
PTHSP# linked to CPHSP#? It sounds like there may be many records in
BSYMPTP for each pair of values of these fields.

John W. Vinson[MVP]

 

[Guest]

The primary keys for the Bsymptp are the pthsp# and ptpro# and pttype# and no
primary key in the Bsymcpp.

Pttype does not have a related field in the Bsymcpp table, therefore not
used. Ptpro# is a foreign key linked to cpcpt.

I'm not sure what you mean by a two field relationship, but both
relationships are set up to only include rows where the joined fields in both
tables are equal.

Is there a way that I can limit this query?

 

[John Vinson]

The primary keys for the Bsymptp are the pthsp# and ptpro# and pttype# and no
primary key in the Bsymcpp.

Pttype does not have a related field in the Bsymcpp table, therefore not
used. Ptpro# is a foreign key linked to cpcpt.

I'm not sure what you mean by a two field relationship, but both
relationships are set up to only include rows where the joined fields in both
tables are equal.

Is there a way that I can limit this query?

I'm sure that there is, but without knowing anything more than this
about the structure of the data I can't say how. If you have multiple
records with the same pthsp# and ptpro# (with different pttype#
values), you will get multiple records - say there are 40 Bsymtp
records with a given pthsp# - ptpro# pair, and 50 Bsymcpp records with
that same combination; you'll get all 50*40 = 2000 possible pairings.

It sounds like your relationships need to be reinvestigated!

John W. Vinson[MVP]