?
Solved

Union query wildcard

Posted on 2003-03-31
13
Medium Priority
?
784 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

765 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