DataBase - Combining Tables

Asked By scottydont2121

20-Nov-09 01:17 PM

Hello,

I have 5 files in excel.  These files will have the same fields, but the
information will change in them every week.  I am then going to import the
the excel files into access into my five tables that I have created.  What I
need is from all those 5 five tables to be combined into one big table.  I
want to keep all the same fields, just a combination of the five.

I have to do some modifications on that on that huge file.  But my main
concern is how to combine all that data into one huge file.

Thanks

20-Nov-09 02:45 PM

It sounds as though you are expecting Access to be Excel ("same fields... 5
files in Excel ... five tables ...").

Access is a relational database, not a spreadsheet.

You are (probably) correct in looking to put all the data in one table (but
I cannot tell for sure, because I cannot tell what data you are talking about).
Here is one approach to doing that:

* link to each of the spreadsheets
* create a query against each of the links
* create a table to hold the full set of data
* if the 'source' is important data, modify each query to include a value
that mentions its source
* convert the queries to "Append" queries, appending to the new table

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

20-Nov-09 03:08 PM

No need for five tables.  Use one and add a field to identify the date of
import.
Append the new data each week using Date() as the input for import week.
--
Build a little, test a little.

20-Nov-09 03:36 PM

Hey ,

The files are coming from 5 different places.  I mean i guess I could copy
and past them in excel, but that may get messy.  I just wanted to have some
sort of automated query, where I could put the info into each of teh tables
in access and then do some sort of query where I can combine all the
information.  Because after I get the huge table I have to run a make table
query to format and sort out the data that i need extracted.  I think my best
bet is the append query, but i am fairly new to access an do not know much
about it.

Thanks for you help by the way.

22-Nov-09 05:14 AM

Hi scotty doontt

you can import all the data to ms access by importing option which you
find in tables..
click on the tables then right click on the white space to import data
from excel. a small wizard will help us to import the data..

after importing all 5 files in access.. open the first table and copy
whole and paste all in 1 table you desired then process is over..

now you can query to get the results..

bye..