Duplication

[Guest]

I'm trying to query a database, but when I get my results, it is well over a
million records. After looking at the records, many of them are duplicated.
How can I eliminate this. The query is based off of 4 tables and the option
to only include rows where the joined fields from both tables are equal is
selected. Each table is joined by 2 common fields. Please help.

 

[Alex White MCDBA MCSE]

Post up the query it sounds like the join(s) in the query are wrong....

 

[Guest]

how would you like for me to post this? I'm not sure I understand?

 

[Alex White MCDBA MCSE]

Go into design view of the query and click on the SQL button, select all the
text and paste into a post.

 

[Guest]

SELECT BSYDTAA_BSYMCPP.CPCPT, BSYDTAA_BSYMPTP.PTDE30,
BSYDTAA_BPBDPSP1.PSPRAM, BSYDTAA_BSYMCPP.CPDEPT, BSYDTAA_BSYMCPP.[CPITM#],
BSYDTAA_BSYMMCP.MCDES
FROM (BSYDTAA_BSYMMCP INNER JOIN BSYDTAA_BPBDPSP1 ON (BSYDTAA_BSYMMCP.MCDEPT
= BSYDTAA_BPBDPSP1.PSDEPT) AND (BSYDTAA_BSYMMCP.[MCITM#] =
BSYDTAA_BPBDPSP1.[PSITM#])) INNER JOIN (BSYDTAA_BSYMCPP INNER JOIN
BSYDTAA_BSYMPTP ON (BSYDTAA_BSYMCPP.[CPHSP#] = BSYDTAA_BSYMPTP.[PTHSP#]) AND
(BSYDTAA_BSYMCPP.CPCPT = BSYDTAA_BSYMPTP.[PTPRO#])) ON
(BSYDTAA_BSYMCPP.CPDEPT = BSYDTAA_BSYMMCP.MCDEPT) AND
(BSYDTAA_BSYMMCP.[MCITM#] = BSYDTAA_BSYMCPP.[CPITM#])
ORDER BY BSYDTAA_BSYMCPP.CPCPT, BSYDTAA_BSYMPTP.PTDE30;

 

[gls858]

SELECT BSYDTAA_BSYMCPP.CPCPT, BSYDTAA_BSYMPTP.PTDE30,
BSYDTAA_BPBDPSP1.PSPRAM, BSYDTAA_BSYMCPP.CPDEPT, BSYDTAA_BSYMCPP.[CPITM#],
BSYDTAA_BSYMMCP.MCDES
FROM (BSYDTAA_BSYMMCP INNER JOIN BSYDTAA_BPBDPSP1 ON (BSYDTAA_BSYMMCP.MCDEPT
= BSYDTAA_BPBDPSP1.PSDEPT) AND (BSYDTAA_BSYMMCP.[MCITM#] =
BSYDTAA_BPBDPSP1.[PSITM#])) INNER JOIN (BSYDTAA_BSYMCPP INNER JOIN
BSYDTAA_BSYMPTP ON (BSYDTAA_BSYMCPP.[CPHSP#] = BSYDTAA_BSYMPTP.[PTHSP#]) AND
(BSYDTAA_BSYMCPP.CPCPT = BSYDTAA_BSYMPTP.[PTPRO#])) ON
(BSYDTAA_BSYMCPP.CPDEPT = BSYDTAA_BSYMMCP.MCDEPT) AND
(BSYDTAA_BSYMMCP.[MCITM#] = BSYDTAA_BSYMCPP.[CPITM#])
ORDER BY BSYDTAA_BSYMCPP.CPCPT, BSYDTAA_BSYMPTP.PTDE30;

Wonderful naming convention

gls858

 

[Alex White MCDBA MCSE]

Your query is hard to read but just for testing copy this query to a new
query and replace the word INNER with the word OUTER.

See if that works.