Duplication

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Go into design view of the query and click on the SQL button, select all the
text and paste into a post.
 
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;
 
Metalteck said:
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;
Click to expand...
Wonderful naming convention :-)

gls858
 
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.
 
Back
Top