Link to home
Start Free TrialLog in
Avatar of bignellrp
bignellrpFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Union query wildcard

Again, im not sure if this is possible, but it would be a massive help if it is.

I'm trying to consolidate 5 tables into one.  Im currently using the union query:

SELECT *
FROM [imptTblForecast1]

UNION SELECT *
FROM [imptTblForecast2];

UNION SELECT *
FROM [imptTblForecast3];

UNION SELECT *
FROM [imptTblForecast4];

UNION SELECT *
FROM [imptTblForecast5];

but the problem is my table names wont always be the same, as they are now generated from the engineers initials.  So the table names will look more like this : [imptTblForecastFV]

Is there a way of setting a wildcard within this union query so all the tables with the name 'imptTblForecast??' are consolidated.

I'm quite desperate for this, so an answer asap would be great.
Avatar of RiverGuy
RiverGuy

I've never tried it, but if you turned ANSI SQL on in your DB, you might be able to use dynamic SQL in a stored procedure.  Let me check it out and see.
Avatar of bignellrp

ASKER

Sounds complicated, but if it works i'd be extremely greatful.
No, its not working.  You could always declare a recordset in VBA and construct the SQL statement from within VBA.  If they had a listbox of their tables, and you could pick the tables to manipulate yoru selection string.

What did you want to do, make a permanent view, or a source for a form, or what?
Im not sure what u mean.  If its possible to write a qry that takes in the tbl names, could i then use these in the union query?
Would it be possible for you to consolidate your data into a single table with an added column to identify the engineer's initials in each record?

That would solve the problem of putting everything together, while at the same time keeping the ability to differentiate records by engineer.

i.e. if imptTblForecastX has fields

field1 | field2 | field3

then make an overall table called imptTblForecast that has fields

field1 | field2 | field3 | initials

Then you can do selects on the overall table, using initials as a criteria, or you can get all the records by ignoring that field.

-Mark.

Im not sure what u mean.  If its possible to write a qry that takes in the tbl names, could i then use these in the union query?
Cheers for for your answer but its not exactly what im lookin for.  Im Consolidating from 5 different databases into one database, so ive already got an engineer field in each table.  Im using a make table rather than append query as submission needs to overwrite the last set of data.

This means i need separate import tables that once submitted are consolidated via a union query.
ASKER CERTIFIED SOLUTION
Avatar of RiverGuy
RiverGuy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
My suggestion was an attempt to avoid the problem rather than solve it.  Since it looks like you've already tried that and found that a parameterised union query is actually necessary, I'd say RiverGuy's suggestion is your best bet.

RiverGuy, I couldn't get that last comment to work in the end, so i had to create a work around by changing the way i generated my table names.

I appreciate ur help, so it looks like i'll have to accept ur last comment as the answer and give u the points.
I'll leave it a little while, so if anyone else can solve my problem i'd be much appreciated. If not i'll give RiverGuy the points.
As i said, i didn't exactly use this answer, but cheers for havin a go.
Thanks.  I think its mainly a limitation of Access.  In other DBMS's where Dynamic SQL is available, this would be much easier in a stored procedure.