MAKE-TABLE FROM UNION QUERY

G

Guest

Hi Guys,

I have two problems with the task that is killing me.

1. How do I ensure that a union query displays its result in the order that
it was built? I made a union query from many tables and queries, carefully
placing the unions in a particular order, only to find that the results were
displayed alphabetically. I had intended similar items to be next to each
other.

2. Secondly, I want the results of my union query (which is essentially a
foundation for what auditors and accountants refer to as TRIAL BALANCE)
stored in a table where future records will also be added, such records that
can be retrieved at will. My poor understanding of a make-table query however
suggests that a new table will be created each time I run the query. Is there
a way to have only one table for all records, provided of course that each
set of records is properly identified for easy retrieval in future? The
numerous fields for the supertable have to come from several tables, and I
would not know in advance what users would have put there.
 
T

Tom Ellison

Dear Glint:

1. I recommend deliberately sorting the results according to the way you
want to see them. You can put an ORDER BY clause after the last SELECT,
that is, at the very end of the whole thing.

2. How about saving the UNION query, then building other queries on that.
Consider creating a table ahead of time and using an append query instead of
a make table. You could have a parameter in the query that goes in the
SELECT clause and thereby groups all the results by its value:

INSERT MyTable
SELECT [Enter Set Name:], *
FROM MyUnionQuery

Just make sure you use a different Set Name every time!

Tom Ellison
 
G

Guest

Good idea, Tom. Thanx.
--
Glint


Tom Ellison said:
Dear Glint:

1. I recommend deliberately sorting the results according to the way you
want to see them. You can put an ORDER BY clause after the last SELECT,
that is, at the very end of the whole thing.

2. How about saving the UNION query, then building other queries on that.
Consider creating a table ahead of time and using an append query instead of
a make table. You could have a parameter in the query that goes in the
SELECT clause and thereby groups all the results by its value:

INSERT MyTable
SELECT [Enter Set Name:], *
FROM MyUnionQuery

Just make sure you use a different Set Name every time!

Tom Ellison


Glint said:
Hi Guys,

I have two problems with the task that is killing me.

1. How do I ensure that a union query displays its result in the order
that
it was built? I made a union query from many tables and queries, carefully
placing the unions in a particular order, only to find that the results
were
displayed alphabetically. I had intended similar items to be next to each
other.

2. Secondly, I want the results of my union query (which is essentially a
foundation for what auditors and accountants refer to as TRIAL BALANCE)
stored in a table where future records will also be added, such records
that
can be retrieved at will. My poor understanding of a make-table query
however
suggests that a new table will be created each time I run the query. Is
there
a way to have only one table for all records, provided of course that each
set of records is properly identified for easy retrieval in future? The
numerous fields for the supertable have to come from several tables, and I
would not know in advance what users would have put there.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top