bignellrp
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.
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.
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.
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?
What did you want to do, make a permanent view, or a source for a form, or what?
ASKER
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.
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.
ASKER
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?
ASKER
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.
This means i need separate import tables that once submitted are consolidated via a union query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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 appreciate ur help, so it looks like i'll have to accept ur last comment as the answer and give u the points.
ASKER
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.
ASKER
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.