Sort/Odering on field in a UNION Qruery

[Steve Stad]

I have a UNION Qry (example below). After I run the qry and sort on a field
(eg invoice date) in the datasheet view - the next time I run the qry it asks
for the parameter a second time. Can you tell me the difference between
'Order by' and 'Sort' and where to put a sort or order statement in this qry
so the qry will still run optimally (i.e., without a second paremeter window
popping up).

SELECT [SEP07].*
FROM [SEP07]
Where ((([SEP07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Oct07].*
FROM [Oct07]
Where ((([Oct07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Nov07].*
FROM [Nov07]
Where ((([Nov07].[Circuit ID]) Like "*" & [ENTER VALUE]));

 

[Marshall Barton]

I have a UNION Qry (example below). After I run the qry and sort on a field
(eg invoice date) in the datasheet view - the next time I run the qry it asks
for the parameter a second time. Can you tell me the difference between
'Order by' and 'Sort' and where to put a sort or order statement in this qry
so the qry will still run optimally (i.e., without a second paremeter window
popping up).

SELECT [SEP07].*
FROM [SEP07]
Where ((([SEP07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Oct07].*
FROM [Oct07]
Where ((([Oct07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Nov07].*
FROM [Nov07]
Where ((([Nov07].[Circuit ID]) Like "*" & [ENTER VALUE]));

Use ORDER BY after the last SELECT.

The Sort menu item recreates your query with an order by
clause and reruns it, thus you get another prompt.

When you get tired of those prompt things, create a simple
form with a text box where you can enter the parameter
value. Then change the query parameter from a prompt string
to a reference to the text box (e.g. Forms![the form].[the
text box]

You can even add a button to open the query, or better yet,
display the query records in the form by binding the form to
the query.

 

[Steve Stad]

I tried to add the 'Order by Invoice Date' after the last statement - but get
an error message saying 'Syntax error (missing operator) in query expression
'Invoice Date'

UNION SELECT [FRSATMFLATALL-Nov07].*
FROM [FRSATMFLATALL-Nov07]
Where ((([FRSATMFLATALL-Nov07].[Circuit ID]) Like "*" & [ENTER VALUE]))
Order by Invoice Date;

I have a UNION Qry (example below). After I run the qry and sort on a field
(eg invoice date) in the datasheet view - the next time I run the qry it asks
for the parameter a second time. Can you tell me the difference between
'Order by' and 'Sort' and where to put a sort or order statement in this qry
so the qry will still run optimally (i.e., without a second paremeter window
popping up).

SELECT [SEP07].*
FROM [SEP07]
Where ((([SEP07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Oct07].*
FROM [Oct07]
Where ((([Oct07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Nov07].*
FROM [Nov07]
Where ((([Nov07].[Circuit ID]) Like "*" & [ENTER VALUE]));

Use ORDER BY after the last SELECT.

The Sort menu item recreates your query with an order by
clause and reruns it, thus you get another prompt.

When you get tired of those prompt things, create a simple
form with a text box where you can enter the parameter
value. Then change the query parameter from a prompt string
to a reference to the text box (e.g. Forms![the form].[the
text box]

You can even add a button to open the query, or better yet,
display the query records in the form by binding the form to
the query.

 

[John W. Vinson]

I have a UNION Qry (example below). After I run the qry and sort on a field
(eg invoice date) in the datasheet view - the next time I run the qry it asks
for the parameter a second time. Can you tell me the difference between
'Order by' and 'Sort' and where to put a sort or order statement in this qry
so the qry will still run optimally (i.e., without a second paremeter window
popping up).

SELECT [SEP07].*
FROM [SEP07]
Where ((([SEP07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Oct07].*
FROM [Oct07]
Where ((([Oct07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Nov07].*
FROM [Nov07]
Where ((([Nov07].[Circuit ID]) Like "*" & [ENTER VALUE]));

I'd really have to question your entire database structure - having separate
tables for each month is A Bad Idea, for the very reason you're now
experiencing! Any chance you could combine these tables into one, with a
datefield? Are the SEP07 records those where Invoice Date is in September?

That said... you can put an ORDER BY clause in the *last* UNION SELECT
statement and it will apply to the entire query. I'd recommend explicitly
naming the fields rather than using the * and just put

ORDER BY [Invoice Date]

at the very bottom, before the semicolon.

John W. Vinson [MVP]

 

[Douglas J. Steele]

That's due to the space in the field name.

As John Vinson points out, you need

Order by [Invoice Date];

or, better yet, rename the field so that it doesn't have a space in its
name.

 

[Marshall Barton]

Names with spaces or other funky characters must be enclosed
in [ ]

See John's reply.
--
Marsh
MVP [MS Access]

I tried to add the 'Order by Invoice Date' after the last statement - but get
an error message saying 'Syntax error (missing operator) in query expression
'Invoice Date'

UNION SELECT [FRSATMFLATALL-Nov07].*
FROM [FRSATMFLATALL-Nov07]
Where ((([FRSATMFLATALL-Nov07].[Circuit ID]) Like "*" & [ENTER VALUE]))
Order by Invoice Date;

I have a UNION Qry (example below). After I run the qry and sort on a field
(eg invoice date) in the datasheet view - the next time I run the qry it asks
for the parameter a second time. Can you tell me the difference between
'Order by' and 'Sort' and where to put a sort or order statement in this qry
so the qry will still run optimally (i.e., without a second paremeter window
popping up).

SELECT [SEP07].*
FROM [SEP07]
Where ((([SEP07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Oct07].*
FROM [Oct07]
Where ((([Oct07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Nov07].*
FROM [Nov07]
Where ((([Nov07].[Circuit ID]) Like "*" & [ENTER VALUE]));

Use ORDER BY after the last SELECT.

The Sort menu item recreates your query with an order by
clause and reruns it, thus you get another prompt.

When you get tired of those prompt things, create a simple
form with a text box where you can enter the parameter
value. Then change the query parameter from a prompt string
to a reference to the text box (e.g. Forms![the form].[the
text box]

You can even add a button to open the query, or better yet,
display the query records in the form by binding the form to
the query.

 

[Steve Stad]

Thanks John - the brackets solved the sorting question. Combining the
monthly tables is a good idea. But they are very large - so for now the
'union' qry is able to extract a smaller subset of selected data from all
tables based on a given criteria.

I have a UNION Qry (example below). After I run the qry and sort on a field
(eg invoice date) in the datasheet view - the next time I run the qry it asks
for the parameter a second time. Can you tell me the difference between
'Order by' and 'Sort' and where to put a sort or order statement in this qry
so the qry will still run optimally (i.e., without a second paremeter window
popping up).

SELECT [SEP07].*
FROM [SEP07]
Where ((([SEP07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Oct07].*
FROM [Oct07]
Where ((([Oct07].[Circuit ID]) Like "*" & [ENTER VALUE]))
UNION SELECT [Nov07].*
FROM [Nov07]
Where ((([Nov07].[Circuit ID]) Like "*" & [ENTER VALUE]));

I'd really have to question your entire database structure - having separate
tables for each month is A Bad Idea, for the very reason you're now
experiencing! Any chance you could combine these tables into one, with a
datefield? Are the SEP07 records those where Invoice Date is in September?

That said... you can put an ORDER BY clause in the *last* UNION SELECT
statement and it will apply to the entire query. I'd recommend explicitly
naming the fields rather than using the * and just put

ORDER BY [Invoice Date]

at the very bottom, before the semicolon.

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks John - the brackets solved the sorting question. Combining the
monthly tables is a good idea. But they are very large - so for now the
'union' qry is able to extract a smaller subset of selected data from all
tables based on a given criteria.

How large is "very large"? With proper indexing, Access should be able to
handle into the tens of millions of records.

And your UNION query will *INEVITABLY* be slower and less efficient than an
equivalent query on a single table - generally, much slower! You can make it a
bit faster by using UNION ALL rather than UNION; the default UNION must search
all twelve (or however many) recordsets for duplicates and remove them, and I
suspect that if you have date fields in these sets, there won't be any
duplicates anyway; but a single table will be faster yet. Just be sure there
is an index on each field used for searching or sorting; and be aware that a
criterion such as

Like "*" & [ENTER VALUE]

will NOT use the index since it has a wildcard at the start. If there is any
other way to subset your data... use it, this is the worst possible way!

John W. Vinson [MVP]

 

[Steve Stad]

Thank you John - You are RIGHT!!! One large table is faster. The table size
will not approach 1 million records for a few years. The db is larger in
megabytes vs linking multiple tables but It should be OK. Also, for now the
union and 'union all' take about the same time.

Thanks John - the brackets solved the sorting question. Combining the
monthly tables is a good idea. But they are very large - so for now the
'union' qry is able to extract a smaller subset of selected data from all
tables based on a given criteria.

How large is "very large"? With proper indexing, Access should be able to
handle into the tens of millions of records.

And your UNION query will *INEVITABLY* be slower and less efficient than an
equivalent query on a single table - generally, much slower! You can make it a
bit faster by using UNION ALL rather than UNION; the default UNION must search
all twelve (or however many) recordsets for duplicates and remove them, and I
suspect that if you have date fields in these sets, there won't be any
duplicates anyway; but a single table will be faster yet. Just be sure there
is an index on each field used for searching or sorting; and be aware that a
criterion such as

Like "*" & [ENTER VALUE]

will NOT use the index since it has a wildcard at the start. If there is any
other way to subset your data... use it, this is the worst possible way!

John W. Vinson [MVP]