Combining Multiple queries with like data as one 1

[houstonbill]

Looking for help on the best way to accomplish combining multiple queries with the same type of data that will be give me single lists of that data. Basd on how the data is dumped to the table (by the CCN #), I have had to break up the data in queries. Below is an example of the SQL of the 1st of those queries. There are 8 more identical to it except that the FEX, FDT and FCD increase in value (FEX4, FEX5, FEX6, etc, and the same for the FDT and FCD entries). I want to buid a single query for a report with a format as shown in the sxample below the SQL. The Fex entry, regardless of number, is the clerk who handled the CCN and I will short by clerk. I want anything a certain clerk done to be listed below their number. Can this be done?

SELECT CCSClosed.FEX3, CCSClosed.FDT3, CCSClosed.FCD3, CCSClosed.CCN, CCSClosed.SUBNOFUL, CCSClosed.GROUPNO, CCSClosed.ICN
FROM CCSClosed
WHERE (((CCSClosed.TYPECD)="RO" Or (CCSClosed.TYPECD)="TW" Or (CCSClosed.TYPECD)="nx") AND ((CCSClosed.REG)="042" Or (CCSClosed.REG)="045"))
GROUP BY CCSClosed.FEX3, CCSClosed.FDT3, CCSClosed.FCD3, CCSClosed.CCN, CCSClosed.SUBNOFUL, CCSClosed.GROUPNO, CCSClosed.ICN
HAVING (((CCSClosed.FDT3)>=#8/1/2007#) AND ((CCSClosed.FCD3)="ACL" Or (CCSClosed.FCD3)="RET"));

REPORT SAMPLE

FEX FDT FCD SUBNOFUL CCN GROUP ICN
5847 08/15/07 ACL 02071920 596100284 76415 7262511
08/17/07 RET 12344689 123456789 98753 1234456
08/20/07 ACL 22233344 012345612 31235 2223334

 

[PHV]

You may try an UNION query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[houstonbill]

I have only done a Union query once. It had much less info in the SQL with fewer criterias, and extended across a row rather then to be in a column. Try as I may, I cannot get this to work. Considering the SQL that I entered, can someone please deomonstrate how I would join this SQL to a 2nd SQL that would be identical other than it would have a 4 rather than 3 following the FEX, FDT, and FCD (I too will continue working on it). I will still need to add about 8 more SQL statements. Thanks.

 

[PHV]

SELECT FEX3 AS FEX, FDT3 AS FDT, FCD3 AS FCD, CCN, SUBNOFUL, GROUPNO, ICN
FROM CCSClosed
WHERE TYPECD In ('RO','TW','nx') AND REG In ('042','045')
AND FDT3>=#2007-08-01# AND FCD3 In ('ACL','RET')
UNION SELECT FEX4, FDT4, FCD4, CCN, SUBNOFUL, GROUPNO, ICN
FROM CCSClosed
WHERE TYPECD In ('RO','TW','nx') AND REG In ('042','045')
AND FDT4>=#2007-08-01# AND FCD4 In ('ACL','RET')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[houstonbill]

This helps perfectly. I was very close to what you have but my misplacement of a few things stopped it from working. What you have is perfect! I knew there had to be a way to make this work. Thanks so much for your help. Put this one in my learning notebook.