Combining 6 queries into 1 master query/xlsx export

Toad Data Point
1

Sounds crazy I know. But currently I do have a program called Forest and Trees that uses Views much like TDA uses editors, however, each “view” can see each other based on the view name. I can query 2 views with a 3rd view as if the first two views were independent tables.

So, Forest and Trees is no longer supported (either by the company CA or our company) so I need to transition this monster report to TDA. I am trying to figure out how the heck I am going to accomplish this. I am not strong with Macro’s so that would be a learning curve for me.

I am not sure if I can do this but if I were to run the first 6 queries can I combine them into a final query that refernces the first 6? Iam truly not sure how this would look, but I gave it a novice attempt below.

So select * from customers where blah blah; A,select * from orders where blah blah; B,select * from order_lines where blah blah; C,select * from a, b, c where a.id = b.id and b.id = c.id

Excuse me for my lack of knowledge on this subject. Not sure how you would reference multiple queries.

P.S. I can’t do this with a JOIN, the data will not mash together properly with the current DB setup I am pulling from, they need to be individual queries, been there done that.

Thanks, Jim

2

We are in the middle of coding a feature that will do exactly what you ask. (Virtual Views) But unfortuntely, it will not be out until next version (3.2). This will store a query and treat it like a table.

In the meantime I suggest you use the Toad Sample Database or Local Storage (depending on the version you are using) to import the query data into a table, then join the tables.

Another idea would be to create linked Queries in Access. Once they are defined in Access you can see them in the Query tab of TDA and can join them likes tables in a single query.

The first method could be put into an automation script so you could refresh the data just prior to generating the report.

Using the second method, each query is executed when the composite query is executed.

Does one of these methods work for you?

Debbie

3

Sorry Debbie,
I have been a little tied up and unable to try this yet. I will give it a try soon and let you know. I have version 2.7 btw.