Multiple query results in one record 1

[math20]

Hi,

I am trying to get multiple query results in one record.

I have five different queries the results of which does not have any relation to each other. I would like to have one record produced for all the queries with its original headings.

Is it possible? Can anyone plaese assist me?

Thank you

 

[randy700]

I can't imagine what you'd use this for, but...

SELECT Query1.*, Query2.*, Query3.*, Query4.*, query5.*
FROM Query1, Query2, Query3, Query4, Query5

Randy

 

[math20]

Hi Randy,

Thank you. I know it is bit unusual. However, when I use this code I get an error saying the query is too complex.

Currently I am running each query seperately and then copy and paste it into an excel spreadsheet.

 

[randy700]

Add an autonumber field to each query. Then...

SELECT Q1.*, Q2.*, Q3.*, Q4.*, Q5.*
FROM (((Q1 INNER JOIN Q2 ON Q1.ID = Q2.ID) 
           INNER JOIN Q3 ON Q1.ID = Q3.ID) 
           INNER JOIN Q4 ON Q1.ID = Q4.ID) 
           INNER JOIN Q5 ON Q1.ID = Q5.ID

Randy

 

[math20]

Hi Randy,

It is still giving me the same error:
here is my code:

SELECT [Total For Option_1s].*, [Total For Option_2s].*, [Total For Option_3s].*, [Total For Option_4s].*, [Total For Option_5s].*, [Total Options].*
FROM (((([Total For Option_1s] INNER JOIN [Total For Option_2s] ON [Total For Option_1s].[No] = [Total For Option_2s].[No])
INNER JOIN [Total For Option_3s] ON [Total For Option_1s].[No] = [Total For Option_3s].[No])
INNER JOIN [Total For Option_4s] ON [Total For Option_1s].[No] = [Total For Option_4s].[No])
INNER JOIN [Total For Option_5s] ON [Total For Option_1s].[No] = [Total For Option_5s].[No])
INNER JOIN [Total Options] ON [Total For Option_1s].[No] = [Total Options].[No];

Thank you.

 

[dhookom]

Maybe the query is too complex. You haven't provided any information about your source queries so it is a bit difficult to determine your issue.

Have you tried with one source, then adding the second, then the third, then the fourth?

Duane
Hook'D on Access
MS Access MVP

 

[math20]

Hi Randy,

I have tried adding one query at a time and it stops working when I add the third query. The result for third query is bit long.

I am thinking of adding all the queries except the third one and run that seperately to import into excel.

Thank you for all your assistance.

 

[dhookom]

Again you haven't provided much information about your queries. There are a number of issues that cause "too complex" error messages. Some of these are caused by values that mix text vs numeric and don't explicity convert from one to the other.

Duane
Hook'D on Access
MS Access MVP

 

[math20]

Hi dhookom,

I have checked the data type and they are ok. The only thing that is causing the error is the size of the query as you have correctly guessed the source query is actually few queries that derive data at various level and options.

Third query is build on top of atleast 15 other queries.

I am thinking and please let me know if this is possible, since my ultimate requirement is to import it into an excel spreadsheet, is it possible to write a code that will lookup these individual queries (I mean the final 5 queries) and export them to excel spreadsheet in one row including each query headings?

Thank you for your assistance.

 

[dhookom]

I wonder if the queries could be simplified but I don't have the time to troubleshoot.

An alternative might be to push some of the queries into temporary tables and then query the tables.

Duane
Hook'D on Access
MS Access MVP

 

[math20]

Hi dhookom,

I will give that a try.

Thank you.