Beginner's question about combining tables

[Guest]

I have three Excel spreadsheets with identical column names (Name, Address,
email, etc.) that I need to combine into one table or report. The fields are
the same but the data is different for each spreadsheet. Cutting and pasting
is not an option because the tables are updated on a regular basis. I was
hoping to use Access to do this - I linked all of the tables into one Access
database, but I have no idea how to get the data from all of them into one
table or report. I've tried looking using help, but I'm having little luck
understanding append or union queries....all of the explanations I've read so
far have just gone right over my head. Any simple solutions would be
appreciated.

 

[Ed Warren]

Assume you have three spreadsheets
link them as spreadsheet1, spreadsheet2, spreadsheet3

then open a query and copy/paste the following sql code into the sql view.

This will return your 'combined' table (view). If ALL the fields are named
the same you can get by with

"Select * from spreadsheet1;" rather than the extended spreadsheet1.xxxx
line.
Also the 'all' keyword return duplicate rows if they exist. If you do not
want duplicate rows, then remove the 'all'

----------------------sql code------------
SELECT spreadsheet1.Name, spreadsheet1.Address, spreadsheet1.email,
spreadsheet1.Etc
FROM spreadsheet1;

union all

SELECT spreadsheet2.Name, spreadsheet2.Address, spreadsheet2.email,
spreadsheet2.Etc
FROM spreadsheet2;

union all

SELECT spreadsheet3.Name, spreadsheet3.Address, spreadsheet3.email,
spreadsheet3.Etc
FROM spreadsheet3;

----------------------------sql code end--------------------------------


Ed Warren

 

[PC Datasheet]

Use a Union query to pull all the data from the three tables together and
use the Union query as the basis of your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Contact me.

 

[StopThisAdvertising]

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Contact me.

These 1000 (if at all a real figure..) is only the result of
-- 4 years abusing the newsgroups.
-- 4 years blatantly advertising and job hunting.

You only care about making money, and you act as if the groups are your private hunting ground.
So why would ANYBODY ever trust a person like you and hire you?
********************************************************

Explanation and more on this answer to Steve:
http://home.tiscali.nl/arracom/stopsteve.html

Arno R

 

[Guest]

Thanks so much for your help. I am still having some problems, though.

I have linked the tables as 041305, 122704, and 102705 (the dates of the
conferences we are tracking). I entered your code and changed it to reflect
my table information, but I keep getting this error message:

"Syntax error (missing operator) in query expression
041305.Conference_Date."

I have tried the name of the field with both a space and an underline
between words, but continue to ge tthe same message. Any suggestions?

For your reference, the code I am using is as follows:

--------------SQL code begins-------------------
SELECT 041305.Conference_Date, 041305.NAME, 041305.ADDRESS_1,
041305.ADDRESS_2, 041305.CITY, 041305.STATE, 041305.ZIP_CODE, 041305.PHONE,
041305.EMAIL, 041305.Road_to_Recycling, 041305.Giving_Nature_a_Hand,
041305.Planning_a_Highway
FROM 041305;

union all

SELECT 122704.Conference_Date, 122704.NAME, 122704.ADDRESS_1,
122704.ADDRESS_2, 122704.CITY, 122704.STATE, 122704.ZIP_CODE,
122704.Road_to_Recycling, 122704.Giving_Nature_a_Hand,
122704.Planning_a_Highway
FROM 122704;

union all

SELECT 102705.Conference_Date, 102705.NAME, 102705.ADDRESS_1,
102705.ADDRESS_2, 102705.CITY, 102705.STATE, 102705.ZIP_CODE,
102705.Road_to_Recycling, 102705.Giving_Nature_a_Hand,
102705.Planning_a_Highway
FROM 102705;
--------------SQL code ends--------------------

 

[Ed Warren]

Most likely you have a difference between your naming of "Conference Date"

if in your excel files you have a field named

Conference Date

(with a space), then in access you will need to use the brackets to indicate
a field with a space

041305.[Conference Date]

The easy way to 'get it right' is to build a simple query based on table
041305
1. open a new query
2. add the table 041305
3. add each field to the query designer in order
4. select sql view
This will give you the required syntax.

Now you have to make sure all the tables have exactly the same field names.

Ed Warren

 

[Ed Warren]

Looks like you have a difference in naming the fields.
1. open a new query in design mode
2. add table 041305
3. select each field in turn
4. run the query
-if it works you can see what the right syntax is by going back to the
design view and selecting view (sql).
make the corrections and it should work.

Ed Warren.

 

[John Vinson]

Thanks so much for your help. I am still having some problems, though.

I have linked the tables as 041305, 122704, and 102705 (the dates of the
conferences we are tracking). I entered your code and changed it to reflect
my table information, but I keep getting this error message:

"Syntax error (missing operator) in query expression
041305.Conference_Date."

Try either changing the names of your tables to start with a letter,
or enclose the table names in square brackets:
[041305].Conference_Date. I think Access is getting confused because
the value looks like a number.

John W. Vinson[MVP]