Combine Multiple Queries into new query or table

[Jack W via AccessMonster.com]

I've got 5 queries, all doing the same thing but seperated per client, each
off the back of a seperate table for each client. I want to combine these
queries into one table or query for reporting purposes but I wouldn't have a
clue where to start, does anyone have any ideas?

They all have the same fields so I've got a seperate append query for each
into a new table, but there must be an easier way...

Cheers.

 

[Terrell Miller]

I've got 5 queries, all doing the same thing but seperated per client, each
off the back of a seperate table for each client. I want to combine these
queries into one table or query for reporting purposes but I wouldn't have a
clue where to start, does anyone have any ideas?

They all have the same fields so I've got a seperate append query for each
into a new table, but there must be an easier way...

Since they all have the same fields you can use UNION to run your
queries in sequence and generate one big recordset. You have to be in
SQL View and type/copy-paste this structure (no way to do it in the
query design grid), where you have the SQL clause for your first table,
then a UNION statement, then the second clause, another UNION, and so on:


Select (stuff)
From Table1

UNION

Select (stuff)
From Table2

UNION

....


UNION

Select (stuff)
From TableN

HTH,


--
Terrell Miller
(e-mail address removed)

"Suddenly, after nearly 30 years of scorn, Prog is cool again".
-Entertainment Weekly

 

[Jack W via AccessMonster.com]

I've done that and it works great thanks. Also, I want to use another table
as criteria for one of the fields in my Union query, any way that can be done
(easily)?

I've got 5 queries, all doing the same thing but seperated per client, each
off the back of a seperate table for each client. I want to combine these

[quoted text clipped - 3 lines]

They all have the same fields so I've got a seperate append query for each
into a new table, but there must be an easier way...

Since they all have the same fields you can use UNION to run your
queries in sequence and generate one big recordset. You have to be in
SQL View and type/copy-paste this structure (no way to do it in the
query design grid), where you have the SQL clause for your first table,
then a UNION statement, then the second clause, another UNION, and so on:

Select (stuff)
From Table1

UNION

Select (stuff)
From Table2

UNION

...

UNION

Select (stuff)
From TableN

HTH,

 

[Guest]

Jack,

Although what Terrell says is true- the UNION query will combine each of
these tables into one result- if these are indeed clients and you are running
a business, do you intend to limit your business to only these 5 clients
forever?

Putting each client in a separate table is NOT A GOOD THING. If you're
client list grows, you have to add a new table for each client, and remember
to update this query. Suppose one of the clients has something different that
needs to be captured in his table? Your tables are no longer all identical.
If your client list grows to 100 and you have two or more clients with the
same name, what do you do? How do you determine quickly that this client that
you need to change info for is John Doe 1 and not John Doe 2?

Much better idea- although I don't really know what you want to accomplish
here-
is one table and some way of uniquely identifying each client. Each client
would then simply be a new row in a data table. New client? Simply add the
info in the datasheet for that table. If you try to do anything other than
simply viewing this data in a datasheet, it will also greatly simplify the
task of querying, grouping, analyzing the data that you are capturing.

--
Chaim

I've done that and it works great thanks. Also, I want to use another table
as criteria for one of the fields in my Union query, any way that can be done
(easily)?

I've got 5 queries, all doing the same thing but seperated per client, each
off the back of a seperate table for each client. I want to combine these

[quoted text clipped - 3 lines]

They all have the same fields so I've got a seperate append query for each
into a new table, but there must be an easier way...

Since they all have the same fields you can use UNION to run your
queries in sequence and generate one big recordset. You have to be in
SQL View and type/copy-paste this structure (no way to do it in the
query design grid), where you have the SQL clause for your first table,
then a UNION statement, then the second clause, another UNION, and so on:

Select (stuff)
From Table1

UNION

Select (stuff)
From Table2

UNION

...

UNION

Select (stuff)
From TableN

HTH,

 

[Terrell Miller]

I've done that and it works great thanks. Also, I want to use another table
as criteria for one of the fields in my Union query, any way that can be done
(easily)?

have a form whose datasource is the other table.

Then in the query grid for the first query, for the criteria you will
open up the Expression Builder, in the left window choose Forms/Loaded
Forms, highlight the form, then doubleclick the appropriate field name
in the middle window. That control name should now appear in the
expression window up top. THen click OK, and use that SQL statement as
the basis for your other clauses in the UNION structure.

On your form you might also want to have a command button that when
clicked will run the UNION query. Create the button, then the easiest
way is to use the wizard to speficy that you will Open a Query, and
specify the name of your UNION query.

To use the form, navigate to the record with the criteria you want for
the query, then simply click the button. The query will run and appear
onscreen.

HTH,

--
Terrell Miller
(e-mail address removed)

"Suddenly, after nearly 30 years of scorn, Prog is cool again".
-Entertainment Weekly

 

[Jackson via AccessMonster.com]

Chaim I understand what you are saying but this can't be done, the 'clients'
are actually fixed entities, where we get financial information, all in
different formats which I import, can't get a standardised report from each
unfortunately. Therefore, this is probably the easiest way for me to do this,
the information is basically reset each day so I do not explicitly need past
info.

Terrell, I'm sort of understanding what you've said, but how do I write in a
criteria, for example, if I add in a date column into this query, how would I
filter for just =date()