call 2 queries from within a 3rd ?

[PeterMac]

I have 2 very complex queries (many Union Selects in each one) and after so much coding Access tells me that the query is too complex. So I split the query into 2 separate ones. Now I want to be able to call both queries (I'm thinking from a third query) and have the results of both queries combined into one result set. Is that possible and if so how would I do that ?

 

[SgtJarrow]

If you have some sort of binding field, you merely add the two queries into the new query and display all the data as you like. But there must be some field in the two query that "join" or identify the rcords as a match.

Add the queries to the new query, ensure you set a reference between the identifing field and set up your third query as necessary. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[PeterMac]

thanks I am playing with that now... I have the 2 quieries in the design view of the quiery designer and they both are showning the same returned field (cardid), but I don't want them displayed as 2 columns. Do I just drag a join connection between the two columns ? Is it that simple?

Thanks for the quick reply.

 

[SgtJarrow]

I am not follwoing you...are you saying you want to display a field with data combined from two fields in the feeder queries?? Can you give an example of input and output...and we'll figure it out. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[PeterMac]

I have this so far...

SELECT [Any 2 Lines].cardid, [Any 1 Line + 4 Corners].cardid
FROM [Any 2 Lines], [Any 1 Line + 4 Corners];

but I want to do something like this...

SELECT cardid
FROM [Any 2 Lines], [Any 1 Line + 4 Corners];

will that combine the 2 queries into one a column result set?

 

[SgtJarrow]

In the design view of the query....yes, create a link line between the two feeder queries. Leave the realtionship alone as you want an inner join anyway (only show data where the number exists in both queries).

Then in the field display portion of the query window, add the cardid field from either of the two queries...it doesn't matter in an inner join. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[PeterMac]

Thanks a bunch... so this is what I have now...

SELECT [Any 2 Lines].cardid
FROM [Any 1 Line + 4 Corners] INNER JOIN [Any 2 Lines] ON [Any 1 Line + 4 Corners].cardid = [Any 2 Lines].cardid;


will that joint the two tables and give me all the cardid results from each one ?

table 1.cardid table 2.cardid
1 5
3 6
6 7

will return this:

cardid
1
3
5
6
6
7

right? Should a Distinct help take care of the dups here ?
and an order by to ensure sequence ?

 

[SgtJarrow]

Yes it should.....but since you are actually working with two different sets of data (different numbers in each) the current setup is probably not going to give you valid results....you example made it clear.

In set one....you have 1, 3, 6
In set two....you have 5, 6, 7
You want.... 1, 3, 5, 6, 7

Which is a UNION of the two sets, and DISTINCT which is showing now duplicates....

Try using the Query Wizard and select a Union query and go through the steps...shoudl give you the resuls you want very easily. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[PeterMac]

Thanks... got it figured... it was too simple


SELECT cardid from [Any 2 Lines]
UNION SELECT cardid FROM [Any 1 Line + 4 Corners];

cheers, and thanks for the pointers...

PeterMac