loop thru records to create a union query

[lackeysc]

I put this in the formscoding group but didn't get a response so I am
trying it here. Thanks in advance for any help.

I basically have a parameter query in which a customerID is entered
and returns up to 6 records containing the names of price books that
apply to that customer. The price books are each in their own tables
(db5 files created by our ERP system) which I have linked to in
Access. I'd like to use the pricebook field in the parameter query to
union the price books together.

For example, if the the price books were named PB1, PB2,
PB3... ...PB99 and the CustomerID entered was A1, the parameter query
results would look something like this:

A1 PB6
A1 PB17
A1 PB32
A1 PB64

From those results I would like to click a button which would create
a union query or create a temporary table with the results of the
following
union:


Select * from PB6
union
Select * from PB17
union
Select * from PB32
union
Select * from PB64


Assuming that the above union query can be created, I would then need
to run a grouped query against the parameter query which would be
grouped by the ItemNumber field and return the
min(price) for each item.

Thanks again for your help!

 

[Armen Stein]

Select * from PB6
union
Select * from PB17
union
Select * from PB32
union
Select * from PB64


Assuming that the above union query can be created, I would then need
to run a grouped query against the parameter query which would be
grouped by the ItemNumber field and return the
min(price) for each item.

I'm not sure exactly what your question is, but if you're asking
whether a Union query like that can be created, then yes. You could
use some VBA code to loop through a recordset of each price book
relating to a customer and build up the SQL statement by concatenating
strings.

However, have you thought of condensing all the price books into one
large table (with PriceBookNumber as one field)? Then you could do
all this without Union query acrobatics. Union queries are great when
you need them, but they can be cumbersome to build this way and
perform slowly also.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[lackeysc]

Thank you for your response.

I'm not sure exactly what your question is, but if you're asking
whether a Union query like that can be created, then yes.  You could
use some VBA code to loop through a recordset of each price book
relating to a customer and build up the SQL statement by concatenating
strings.

That is exactly what I am asking... How do I set up that loop?

However, have you thought of condensing all the price books into one
large table (with PriceBookNumber as one field)?  Then you could do
all this without Union query acrobatics.  Union queries are great when
you need them, but they can be cumbersome to build this way and
perform slowly also.

I have considered this and have not ruled it out completely. There are
over 100 price books and they are modified fairly often. I'm not sure
if it is more cumbersome to run the union queries or to rebuild the
master table. I'm certainly willing to take advise on the better of
the two options.

Thanks

 

[John W. Vinson]

I have considered this and have not ruled it out completely. There are
over 100 price books and they are modified fairly often. I'm not sure
if it is more cumbersome to run the union queries or to rebuild the
master table. I'm certainly willing to take advise on the better of
the two options.

It certainly sounds like you have (or should have!!!) a one to many
relationship from a hundred-row table of Price books to another table (your
current tables combined). Unless you're talking about tens of millions of
records, a two table solution like this would be MUCH better than UNION
queries with potentially a hundred tables!!!!!

 

[Armen Stein]

It certainly sounds like you have (or should have!!!) a one to many
relationship from a hundred-row table of Price books to another table (your
current tables combined). Unless you're talking about tens of millions of
records, a two table solution like this would be MUCH better than UNION
queries with potentially a hundred tables!!!!!

Yes, you haven't said how many records there are in all of the price
book tables combined. If the total is less than a million or so, it
would be better to run a routine periodically to clear out a new
consolidated price book table and reload it with all the records. You
could do that with a macro that calls a series of queries.

Then, you can run regular Totals queries to find minimum pricing for a
given customer and product.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[lackeysc]

It certainly sounds like you have (or should have!!!) a one to many

Yes, you haven't said how many records there are in all of the price
book tables combined.  If the total is less than a million or so, it
would be better to run a routine periodically to clear out a new
consolidated price book table and reload it with all the records.  You
could do that with a macro that calls a series of queries.

Then, you can run regular Totals queries to find minimum pricing for a
given customer and product.

Armen Stein
Microsoft Access MVPwww.JStreetTech.com

Thanks again for your responses.

You are correct that I would have a 1 to many relationship and the
tables are reasonably small.

There are approximately 150 tables that I would need to append to a
master table. The only catch now is to create a TableName field in the
master table and somehow populate that field with the names of the
tables I am appending from.

Is there a way to pick up those table names automatically?

 

[John W. Vinson]

You are correct that I would have a 1 to many relationship and the
tables are reasonably small.

There are approximately 150 tables that I would need to append to a
master table. The only catch now is to create a TableName field in the
master table and somehow populate that field with the names of the
tables I am appending from.

Is there a way to pick up those table names automatically?

For certain values of automatically... <g>

You could use a query like

SELECT "ThisTable" AS TableName, * FROM Thistable
UNION ALL
SELECT "ThatTable", * FROM THatTable
UNION ALL
SELECT "TheOtherTable", * FROM TheOtherTable
<etc>

Or you could write VBA code to loop through the Tables collection and build
this SQL, or even populate the master table.

 

[Armen Stein]

For certain values of automatically... <g>

You could use a query like

SELECT "ThisTable" AS TableName, * FROM Thistable
UNION ALL
SELECT "ThatTable", * FROM THatTable
UNION ALL
SELECT "TheOtherTable", * FROM TheOtherTable
<etc>

Or you could write VBA code to loop through the Tables collection and build
this SQL, or even populate the master table.

Are the 150 table names static, or do they change? If they're static,
I would just build the 150 append queries one time and run them all
from a macro (starting with one Delete query that clears out the
master table). Each append query can load an ID field that identifies
which price book the records came from.

If the names change, then John's suggestion is right. Look at Help
for using the Tables collection and use VBA to build a For Each loop
to go through it. I wouldn't build a Union query with it though -
instead I would build and execute a separate Append query for each
table. Each one will go really fast and they will be easier to debug
than one giant Union query.

Remember that after you have it working, you can SetWarnings to False
to skip the warning messages for each action query. Remember to turn
SetWarnings back on at the end.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com