Reusing Union Query with different variable values

[Steve Stad]

I have a union query that appends fields from several tables based on a given
criteria, e.g., id like *30060.
Example:
Select [tblJAN].field1, [tblJAN].field2, [tblJAN].field3, [tblJAN].id
From [tblJAN]
HAVING [tblJAN].id LIKE "*30060"
UNION SELCECT [tblFEB].field1, [tblFEB].field2, [tblFEB].field3, [tblFEB].id
From [tblFEB]
HAVING [tblFEB].id LIKE "*30060"

The query actually appends more fields from about 12 or more monthly tables
based on the same criteria, e.g., id like *30060. I would like to be able
to change
the value of the id variable on the fly in order to reuse the union query
for other
id values - Without having to copy/edit/paste the entire query in and out of
access. Is there a way for the union query to read a variable in a table so
I can update the variable in the table and then run the query.

Thanks,

 

[KARL DEWEY]

This will prompt for a value ---
Select [tblJAN].field1, [tblJAN].field2, [tblJAN].field3, [tblJAN].id
From [tblJAN]
HAVING [tblJAN].id LIKE "*" & [Enter value]
UNION SELCECT [tblFEB].field1, [tblFEB].field2, [tblFEB].field3, [tblFEB].id
From [tblFEB]
HAVING [tblFEB].id LIKE "*" & [Enter value]

Some folks do not like prompts and say to use a form for the input so the
query would be like this ---
Select [tblJAN].field1, [tblJAN].field2, [tblJAN].field3, [tblJAN].id
From [tblJAN]
HAVING [tblJAN].id LIKE "*" & [Forms]![YourFormName]![TextBox]
UNION SELCECT [tblFEB].field1, [tblFEB].field2, [tblFEB].field3, [tblFEB].id
From [tblFEB]
HAVING [tblFEB].id LIKE "*" & [Forms]![YourFormName]![TextBox]

You would open the form, enter value, and then run the query.

 

[Steve Stad]

Thanks Karl - the parameter window works great - and I only have to enter
once and all of the select tables read it. This will save alot of time.

This will prompt for a value ---
Select [tblJAN].field1, [tblJAN].field2, [tblJAN].field3, [tblJAN].id
From [tblJAN]
HAVING [tblJAN].id LIKE "*" & [Enter value]
UNION SELCECT [tblFEB].field1, [tblFEB].field2, [tblFEB].field3, [tblFEB].id
From [tblFEB]
HAVING [tblFEB].id LIKE "*" & [Enter value]

Some folks do not like prompts and say to use a form for the input so the
query would be like this ---
Select [tblJAN].field1, [tblJAN].field2, [tblJAN].field3, [tblJAN].id
From [tblJAN]
HAVING [tblJAN].id LIKE "*" & [Forms]![YourFormName]![TextBox]
UNION SELCECT [tblFEB].field1, [tblFEB].field2, [tblFEB].field3, [tblFEB].id
From [tblFEB]
HAVING [tblFEB].id LIKE "*" & [Forms]![YourFormName]![TextBox]

You would open the form, enter value, and then run the query.
--
KARL DEWEY
Build a little - Test a little

I have a union query that appends fields from several tables based on a given
criteria, e.g., id like *30060.
Example:
Select [tblJAN].field1, [tblJAN].field2, [tblJAN].field3, [tblJAN].id
From [tblJAN]
HAVING [tblJAN].id LIKE "*30060"
UNION SELCECT [tblFEB].field1, [tblFEB].field2, [tblFEB].field3, [tblFEB].id
From [tblFEB]
HAVING [tblFEB].id LIKE "*30060"

The query actually appends more fields from about 12 or more monthly tables
based on the same criteria, e.g., id like *30060. I would like to be able
to change
the value of the id variable on the fly in order to reuse the union query
for other
id values - Without having to copy/edit/paste the entire query in and out of
access. Is there a way for the union query to read a variable in a table so
I can update the variable in the table and then run the query.

Thanks,

 

[John W. Vinson]

Thanks Karl - the parameter window works great - and I only have to enter
once and all of the select tables read it. This will save alot of time.

Just another note - I'd suggest using WHERE rather than HAVING since you're
not apparently doing any totalling. WHERE filters the records before doing
totals; HAVING waits until all the totalling is done.

John W. Vinson [MVP]

 

[Steve Stad]

John - Thanks for the info on using 'where' vs. 'having'. I am actually
using a 'group by' so that probably explains the 'having' statement.

 

[John W. Vinson]

John - Thanks for the info on using 'where' vs. 'having'. I am actually
using a 'group by' so that probably explains the 'having' statement.

In a Totals query, you can use WHERE, HAVING, or both - but in practice HAVING
should only be used if you want to limit the selection based on *the result of
a calculation*. If your criterion is based on selecting which records should
be totalled, use the WHERE clause.

John W. Vinson [MVP]

 

[Steve Stad]

Thanks John - When using select * the query builder defaults to 'where' for
criterion. When using 'total' or 'Group by' it defaults to 'HAVING'. Is
'HAVING' more optimal (faster) for totals queries?

 

[John W. Vinson]

Thanks John - When using select * the query builder defaults to 'where' for
criterion. When using 'total' or 'Group by' it defaults to 'HAVING'. Is
'HAVING' more optimal (faster) for totals queries?

Well, it depends on what you're doing, but generally, yes.

Suppose you're selecting 124 ID's out of a table containing 54992 ID's. If you
use WHERE, you'll do all the calculations, totals, counts, etc. on those 124
records and then display the results.

If you use HAVING, you'll do all the calculations, totals, counts, etc. on all
54992 records - and then throw away all but 124 records.

Pretty easy to see which will be faster!

John W. Vinson [MVP]