Problem with record sorting 1

[bosk00]

I am having a problem with record sorting. The data being pulled is for primary and secondary company locations. I have 2 queries that pull the data seperatly, but need a way of combining the output. The first query will give me all of the Primary locations.

SELECT DISTINCT QAMAIN.T01PLSL, QAMAIN.COMPANY, QAMAIN.T01CLINO, QAMAIN.T01CLILO, States.[AOFA ST], [Date on Report].[Date on Report], QAMAIN.T01CONDTM, QAMAIN.REGION, QAMAIN.T01CLINOP, QAMAIN.T01CLILOP
FROM [Date on Report], QAMAIN INNER JOIN States ON QAMAIN.STATE = States.ST
WHERE (((QAMAIN.T01PLSL)="P") AND ((QAMAIN.T01CONDTM)=[Enter Month for Covers]))
ORDER BY QAMAIN.COMPANY
WITH OWNERACCESS OPTION;

The second query does the same for the secondary locations.

SELECT DISTINCT QAMAIN.T01PLSL, QAMAIN.COMPANY, QAMAIN.T01CLINO, QAMAIN.T01CLILO, States.[AOFA ST], [Date on Report].[Date on Report], QAMAIN.T01CONDTM, QAMAIN.REGION, QAMAIN.T01CLINOP, QAMAIN.T01CLILOP
FROM [Date on Report], QAMAIN INNER JOIN States ON QAMAIN.STATE = States.ST
WHERE (((QAMAIN.T01PLSL)="S") AND ((QAMAIN.T01CONDTM)=[Enter Month for Covers]))
ORDER BY QAMAIN.COMPANY
WITH OWNERACCESS OPTION;

The final result that I am trying to acheve is for the primaries to print out in company order with their secondaries. Something like this.

Company 1 Primary
secondary 1 of Company 1
secondary 2 of Company 1
Company 2 Primary
Secondary 1 of Company 2
Company 3 Primary
Company 4 Primary

Not all Primaries have secondaries, as with Company 3 above.
The fields that tie the secondary companies to the primaries are the T01CLINOP and T01CLILOP of the secondaries equals the T01CLINO and T01CLILO of the Primary.
I think this can be done by using the exsisting queries as the source for a new query. All help is appreciated.
Thanks in advance.


Alan
Senility at its finest

 

[cmmrfrds]

Use a UNION to join the 2 queries. Add a literal to both the queries. Example.

SELECT DISTINCT "1" as seq, QAMAIN.T01PLSL, etc...
UNION
SELECT DISTINCT "2" as seq, QAMAIN.T01PLSL, etc...
Order by seq, etc

 

[PHV]

And what about this simple query ?
SELECT DISTINCT QAMAIN.T01PLSL, QAMAIN.COMPANY, QAMAIN.T01CLINO, QAMAIN.T01CLILO, States.[AOFA ST], [Date on Report].[Date on Report], QAMAIN.T01CONDTM, QAMAIN.REGION, QAMAIN.T01CLINOP, QAMAIN.T01CLILOP
FROM [Date on Report], QAMAIN INNER JOIN States ON QAMAIN.STATE = States.ST
WHERE (((QAMAIN.T01PLSL)IN ("P", "S")) AND ((QAMAIN.T01CONDTM)=[Enter Month for Covers]))
ORDER BY QAMAIN.T01PLSL, QAMAIN.COMPANY
WITH OWNERACCESS OPTION;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bosk00]

I want to thank you both for your suggestions. Using the union query with the added seq has given me an output that has nested the secondary companies under the primaries.

Alan
Senility at its finest