loop thru records to create a union query

[lackeysc]

Thanks in advance for any help on this.

I have a form with a combo box which has a list of customers. When a
customer is picked from the list, a subform lists price books which
apply to that customer. Each cusotmer can have anywhere from 1 to 6
price books that apply to them. The price books themselves are stored
in db5 files (created through our ERP system) which I have linked to
in Access. I need to be able to either union the listed price books
together or create a temporary table which combineds those price books
so I can run a query which would pull the min(price) for each item in
the price books and create a report from there.

Thanks again!

 

[lackeysc]

I will elaborate a bit here if it helps. I have multiple price book
tables(db5 files) which we will call PB1, PB2, PB3 etc. I have a form
and subform which will return price book names for a specified
customer. Lets say the customerid is A1 and the values in the subform
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
union:

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

I can handle the rest once the table or query is created.

Thanks again!