Importing and Normalizing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I recieve a number of unrelated tables from the internet which are usually
updated monthly or quarterly, and which i copy and paste into excel. These
tables typically have the date as row headings and various different 'items'
as column headings (ie. workplace absences per month in Canada, Ontario,
etc).

I also have an Access database with a main data table in this form: ID
(autonumber field), KEY (abbreviation for 'item' name, ie. ABS_CAN for
absences in canada), DATE, and VALUE (the number).

At present, I have to manually convert these excel files into the form
appropriate for the database, and then copy and paste them in, which is
time-consuming and annoying. Furthermore, this creates more annoying
updating hassle's - Access crosstab queries to look like the excel tables
cannot be updated. My central problem is the different table styles.

From what I see here, you can 'link' tables. How do you do this? Would it
work for me? Would writing some macro automate the import of the data from
excel, the reformation of it into a four field format, and the appending of
it onto the main data table in order to update my tables?

Any suggestions would be greatly appreciated...
 
I thnk what you want to do is populate various tables from data in the one
spreadsheet. If this is what you are after, then, yes, linking to the Excel
spreadsheet and using queries to move the data into the tables would be a way
to do that.
 
Kind of...I want to move the data from multiple tables in excel into one
table in access. Also, I don't know how to link tables, nor write the
code/make the queries to do what I want....This is what I'm after.


Thx,
 
Sorry - to clarify (I figured out how to link tables):

I want to write a query to transform this:

DATE ABS_CAN ABS_ONT............etc
12/1/1995 45 12
12/1/1996 50 17
.....etc

to this:

ID KEY DATE VALUE
1 ABS_CAN 12/1/1995 45
2 ABS_CAN 12/1/1996 50
3 ABS_ONT 12/1/1995 12
4 ABS_ONT 12/1/1996 17

Thx,
 
Yes it's possible. You must create to many queries to every possible
ABS_ situations and all with the same fields. Finally you use the
UNION and UNION ALL.
Query_1 Where KEY=ABS_CAN
KEY DATE VALUE
ABS_CAN 12/1/1995 45
ABS_CAN 12/1/1996 50

Query_2 Where KEY=ABS_ONT
KEY DATE VALUE
ABS_ONT 12/1/1995 12
ABS_ONT 12/1/1996 17

Query_3 UNION and UNION ALL
KEY DATE VALUE
ABS_CAN 12/1/1995 45
ABS_CAN 12/1/1996 50
ABS_ONT 12/1/1995 12
ABS_ONT 12/1/1996 17

For the ID is more difficult, may be use the count() aggregate
function.

[]'s
BD
 
Only problem is that KEY in the original table is actually the field name...
I dont know how to get it out of there and make it duplicate itself to run
down the left.

Thanks for your help though..
 
Back
Top