Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Union query wildcard

Posted on 2003-03-31
13
Medium Priority
?
872 Views
Last Modified: 2008-03-17
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.
0
Comment
Question by:bignellrp
  • 7
  • 4
  • 2
13 Comments
 
LVL 3

Expert Comment

by:RiverGuy
ID: 8241544
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.
0
 
LVL 2

Author Comment

by:bignellrp
ID: 8241593
Sounds complicated, but if it works i'd be extremely greatful.
0
 
LVL 3

Expert Comment

by:RiverGuy
ID: 8241677
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:bignellrp
ID: 8241723
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?
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8241902
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.

0
 
LVL 2

Author Comment

by:bignellrp
ID: 8241907
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?
0
 
LVL 2

Author Comment

by:bignellrp
ID: 8241944
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.
0
 
LVL 3

Accepted Solution

by:
RiverGuy earned 450 total points
ID: 8242017
You can't write a query to take the table names.  You could send a string to the Jet engine.  You could manipulate that string, like:

Dim sql as string
sql = "SELECT * FROM " & TableVariable1 & " UNION SELECT * FROM " & TableVariable2
....etc

You would alter the string through a form or something.  Pass that string to a database connection to populate a recordset.
0
 
LVL 3

Expert Comment

by:wide_awake
ID: 8242118
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.

0
 
LVL 2

Author Comment

by:bignellrp
ID: 8255269
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.
0
 
LVL 2

Author Comment

by:bignellrp
ID: 8255279
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.
0
 
LVL 2

Author Comment

by:bignellrp
ID: 8283479
As i said, i didn't exactly use this answer, but cheers for havin a go.
0
 
LVL 3

Expert Comment

by:RiverGuy
ID: 8285312
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.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question