Lots of Records

M

Metalteck

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;
 
J

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

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]
 
M

Metalteck

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?
 
Top