SQL Query - What's wrong with code 1

[Nina2001]

SELECT VoucherHoursAll-330, VoucherHoursAll-450, VoucherHoursAll-505, VoucherHoursAll6C, VoucherHoursAllLM6000, VoucherHoursAll-Insul, VoucherHoursAll-CoolTech, VoucherHoursAll-Cost, VoucherHoursAll6FA, VoucherHoursAll7FB, VoucherHoursAll7FBS, VoucherHoursAll9A4, VoucherHoursAll9A5, VoucherHoursAll-Cable, VoucherHoursAll-CFRE, VoucherHoursAll-HTSC, VoucherHoursAll-LMX, VoucherHoursAll-Noise, VoucherHoursAll-Others, VoucherHoursAll-Stator INTO VoucherHoursAll;

That's my query. Unfortunately it doesn't work. All the above tables are linked tables and have the same fields. I want to combine them all into one table so I can total them. The error I get when trying to run this make table query is this

"Missing Destination field name in SELECT INTO statement."
Any help would be appreciated. NINA

 

[CJTyo]

Nina,

Are those all TABLE names or FIELD names?

I believe you have to put it like this:

SELECT tablename.fieldname, tablename.fieldname, <and so on> INTO tablename FROM tablename, tablename <and so on>

C
*~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[CJTyo]

Nina,

Since you're taking ALL the fields you could probably do tablename.*, tablename.* instead of all the individual fields. I forgot about that, I never (rarely) use all the fields.

BUT... If you just want to total everything, why not just run a query or report? Why are you making a new table? Just curious.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[JeremyNYC]

Nina,

Actually, following on C's point, why do you have more than one table with identical field names?

Check into &quot;data normalization&quot; on the web, and you'll see that this is not a great idea.

Hope this helps.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[blakezx1]

Nina, I think there is a simple solutions for your problem using unions in your query. This solution requires one important ingredient. ALL of your field names must be identical in every single table for this to work. (which you have already stated is true)

Using union as I've illustrated below, you will be able to select every record from all the tables you listed and combine them into one large result set. (Please note, this can only be created using the SQL view in query designer.) The statement would be created like this:

Select * from VoucherHoursAll-330
Union All
Select * from VoucherHoursAll-450
Union All
Select * from VoucherHoursAll-505
Union All
Select * from VoucherHoursAll6C
Union All
Select * from VoucherHoursAllLM6000
Union All
Select * from VoucherHoursAll-Insul
Union All
Select * from VoucherHoursAll-CoolTech
Union All
Select * from VoucherHoursAll-Cost
Union All
Select * from VoucherHoursAll6FA
Union All
Select * from VoucherHoursAll7FB
Union All
Select * from VoucherHoursAll7FBS
Union All
Select * from VoucherHoursAll9A4
Union All
Select * from VoucherHoursAll9A5
Union All
Select * from VoucherHoursAll-Cable
Union All
Select * from VoucherHoursAll-CFRE
Union All
Select * from VoucherHoursAll-HTSC
Union All
Select * from VoucherHoursAll-LMX
Union All
Select * from VoucherHoursAll-Noise
Union All
Select * from VoucherHoursAll-Others
Union All
Select * from VoucherHoursAll-Stator

And thats it. the result will be all records from all the tables concatinated in to one query result set. (Which you can then use to drive your report and create any summary totals you wish).

The &quot;UNION ALL&quot; puts all records in the result, no matter if there are any dups..everything goes in the result. If you would like to have dups taken out automatically, then just leave out the &quot;ALL&quot; part of the clauses. Simply use &quot;UNION&quot; by itself and it will remove any dups when the query finishes running.

Blake

 

[JeremyNYC]

Blake,

While the Union query will get the job done, it is relies on a poor design scheme. I've just posted Paul Litwin's &quot;Fundamentals of Relational Database Design&quot; to my website. I urge both you and Nina to take a look at that article. It's about 20 pages long, and it's the best 20 pages a new developer could read about how to design table schemes.

These tables should all be combined into a single table, with a field added to it to contain the bit of information that differentiates the different type of records.

Jeremy

PS: I think I messed up when I put up the new pages on my site. When you get to the main site, right click on the &quot;developer's section&quot; to bring that up in a new window and you'll be in good shape. You may want to save Paul's article to your hard drive. =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[Nina2001]

Do I have to put [] around my table names since they have dashes in the names?

 

[Nina2001]

Thank You so much blakezx1 - I got it to work with the union query! I tried to go to the website Jeremy mentioned but I couldn't find a download. If anyone gets it please let me know. THANK YOU ALL FOR YOUR HELP! These forums are my &quot;job&quot; saver!

Nina

 

[JeremyNYC]

Nina,

It's not normally set up for you to download, just for you to read the article on the site. But if you want to download it you can right click on the link and save the document.

In any case, the direct link is

http://www.alphabetcitydataworks.com/Developers/FundamentalsofRelationalDatabaseDesign.doc

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[blakezx1]

Jeremy,
I agree, While the unions aren't pretty the will work to solve her immediate problem. Its possible her &quot;problem&quot; was inherited from someone else's poor design work. Helping her out with that is the first priority. I agree it would be worthwhile for her to check into a redesign if she plans to keep using the database alot. I'll check out the link you have forwarded. Thanks,
Blake

 

[JeremyNYC]

Blake,

Cool, I think we're just looking at it from slightly different angles, and seeing basically the same thing.

I always make the highest priority helping people to make their databases solid from the foundation, because I think it helps them with so many other aspects of their database. I completely understand your inclination, and it's probably good that there are both types of folks around here.

In any case, cheers, and enjoy the reading.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.