Normalization, Append Query For Importing From Excel ???

[AAR]

Dear members

I am just a beginner so sorry beforehand I will describe what i have to do maybe someone could help me out.
I have big spreadsheets in excel which I have to transform into an access DB. I am attaching what I imagined could be the design of the DB, also see attached the sample excel file from where the data shall be transferred into access. The sheet "data" must be transferred into the table "Import" of the DB which is kind of a collective information on one specific transfer (from all the rest tables ) from 2000-2012. Please let me know if the tables and relations are correct. Afterwards what i tried to do was to do an append query and import from excel to access but i didnt succeed. I am kind of stuck now dont know if this is correct and how to proceed , one of the biggest problems is the different formats (not normalized ) in excel and access tables (of the "data" sheet)

Pls ask questions if a didnt manage to explain and let me know if u have any suggestions.


Thanks a lot

[June7]

Might be taking normalization one step farther than necessary. Not sure really need to split Year and Amount to separate table. If you do then store the ID of import table record in tblYearlyAmount, not tblYearlyAmount ID in Import.

Consider setting a link to the Excel worksheet then use it as source for queries to normalize.

A UNION query can rearrange the Year fields to normalized structure.

SELECT Direction AS DirectionID, Cargo AS CargoID, Partner AS PartnerID, Port_Coast AS PortCoastID, Country AS CountryID, 2000 AS YearID, [2000] AS Amount FROM tablelink
UNION SELECT Direction, Cargo, Partner, Port_Coast, Country, 2001, [2001] FROM tablelink
...;

Then can copy/paste records to the Import table or run an INSERT SELECT sql.

Can also use INSERT SELECT sql to populate the PK ID fields of each lookup table. What is SourceID for?

INSERT INTO tblCargo(CargoID) SELECT DISTINCT Cargo FROM tablelink;

[AAR]

Dear June7 , tnx for the answer. I rearranged the tables as you have suggested. Sorry for being slow but I didnt really understand what to do after what ant the sql that you wrote is it in the case if i dont separate the Year table ? I tried to link the file file (External Data / Linking ) than wrote for the import table
SELECT Direction AS DirectionID, Cargo AS CargoID, Partner AS PartnerID, Port_Coast AS PortCoastID, Country AS CountryID ;
but it didnt work. also what is the difference of SELECT and UNION SELECT ? and in stead of "tablelink" what do I write ?
Sorry for confusion , if you have time pls write some steps that I can follow. Tnx

[June7]

That SELECT is only the first line of the UNION query. The general syntax of a UNION is:

SELECT ...
UNION
SELECT ...
UNION
SELECT ...;

up to 50 SELECT lines can be in a UNION

more about UNION
http://office.microsoft.com/en-us/ac...010206109.aspx
http://www.techonthenet.com/sql/union.php

1. use the import/link wizard to set a link to the excel spreadsheet - this link can be used like a table except cannot edit the data - use the link as source of data to populate permanent tables

2. build a UNION query as described in previous link - must be typed into SQL View window of the query builder

3. create 2 more fields in the Import table: YearNum, Amount

4. manually copy/paste records from the UNION query to Import table or use INSERT SELECT query to populate the table or delete the empty Import table and do a MAKE TABLE query with the UNION as the source of records

5. manually create records in the other tables or use INSERT SELECT queries to populate, example:
INSERT INTO tblCargo(CargoID) SELECT DISTINCT Cargo FROM tablelink;

Getting the hang of building these queries will take a little practice and probably some more research and study on your part.

Instead of 'tablelink' use actual name of the link.

[AAR]

tnx a lot for the steps i will read about these and try to figure out ,
so in this case I dont need the table YearlyAmount ?

[June7]

I don't think need the separate table.

[AAR]

Hi June7
I transposed the original data that must be filled into the Import table, I thought maybe it can make the process easier ?

I have approx. 25 large spreadsheets do I have to link them all to the DB or maybe there is some other way to import this data ?

[June7]

I don't know any easier way because of the non-normalized data arrangement on the worksheets.

[rpeare]

part of this code shamelessly stolen from:

http://stackoverflow.com/questions/1...ccess-2007-vba

Here's an example database:

AAR.zip

Simple form with 2 buttons
1 to collect any file names you want to process (you can multi select files within a folder by holding down the ctrl key)
1 to process all the files and make them into a normalized structure

These are the files I used:

Import Files.zip

I basically used the same file but substituted a new country code.

NOTE: once the data is imported it is up to YOU to complete the information in the tables that support the normalized structure

For instance for every country code you'll have to put in the actual country name (or whatever other pieces of information you're gathering that is country specific)
This applies to ALL your fields except the year and shipping quantity

You only have to do this ONCE for each item, once it's set up you shouldn't ever have to change it again.

[AAR]

hi rpeare tnx a lot for this, I have questions though, for example now after importing a file it to shipping table it automatically fills in the other tables but partly, how do I add the rest of the information of this tables? I am sorry but I didnt understand this part

" For instance for every country code you'll have to put in the actual country name (or whatever other pieces of information you're gathering that is country specific)
This applies to ALL your fields except the year and shipping quantity

You only have to do this ONCE for each item, once it's set up you shouldn't ever have to change it again."

Also what about the relationships between the tables ? I dont need to set them anymore ? And one not very important question why if I delete some info and try to import again the autonumbers start already from high numbers can I set somehow so they start from 1 ?
Also now if I change some tables , like add the additional fields and stuff i dont have to change the code or anything right ?
Thanks a loooot for the help

[rpeare]

hi rpeare tnx a lot for this, I have questions though, for example now after importing a file it to shipping table it automatically fills in the other tables but partly, how do I add the rest of the information of this tables? I am sorry but I didnt understand this part

" For instance for every country code you'll have to put in the actual country name (or whatever other pieces of information you're gathering that is country specific)
This applies to ALL your fields except the year and shipping quantity

You only have to do this ONCE for each item, once it's set up you shouldn't ever have to change it again."

I dont need to set them anymore ? And one not very important question why if I delete some info and try to import again the autonumbers start already from high numbers can I set somehow so they start from 1 ?
Also now if I change some tables , like add the additional fields and stuff i dont have to change the code or anything right ?
Thanks a loooot for the help

Think of your data from the point of view of someone new coming into your business.
They are not going to know what most of your abbreviations mean, the support tables in that database allow you to define what the abbreviations mean.

For instance in your CARGO table what does TT mean? If you have to find that value and put in a description so let's assume TT is Train Ties, in the description (or any other data related to that cargo type) would be filled in. I put that in strictly so that as new things came on line (cargo, countries, ports, etc.) you could identify them and populate them with the relevant information.

Also what about the relationships between the tables ?

I don't force relationships in tables. I do not like them. All tables are related through fields with the same name so Cargo_ID in your main table (the one that actually stores shipping information) is linked to tblCargo through the field with the same name.

And one not very important question why if I delete some info and try to import again the autonumbers start already from high numbers can I set somehow so they start from 1 ?

The only table you should be deleting things from that won't corrupt your data is the main shipping table (the one that has year and tons of cargo) everything else should contain data you never delete.
It doesn't matter what the autonumbers are, they are irrelevant. The only thing they do is link one table to another. They do not affect your data, they do not change, they are, for all intents and purposes, something that will be invisible to your users and resetting them is a waste of time. If you really want to you would have to delete all data from all tables then compact/repair the database then import from scratch.

Also now if I change some tables , like add the additional fields and stuff i dont have to change the code or anything right ?

Mostly correct. This applies ONLY to the 'support' tables, the tables that do not contain your actual shipping data (the one with the shipping year and tonnage). Again, once you have the support tables populated you should never delete that data, ever, you can add whatever fields you want but if you start deleting tables from the support tables your data will become corrupt. The only reason you would need to change the 'code' (I'm assuming here you're talking about the code attached to the button on the form) is if your column names are not the same in practice, you have more columns in your actual data than your example showed or something changes to force either one of these in the future.

[AAR]

In this supporting tables, how can I add all this missing information ?(Please see attached sample of data ). Also is it possible to add in the tables data that is not generated by the form? ( so its not in the shipping table. for example, after running the form I have the filled data for Ports , but actually there are more Ports in my original list and I want to have it in the supporting tables )

Tnx a lot for such a detailed answer.

[rpeare]

In the file 'Copy of ARM.xlsm' you included. That data can all be cut and pasted into the database. The only reason I populated the data via queries on the import was to build the support tables as you went along. In other words, after the initial load of data it would catch any 'new' items that came through which you could update. It looks like you already have your code sets in a handy list in that excel file so you can do this:

1. Add the fields that are missing from your support structure to the tables in your database

In my case I had a table named tblCountry with the fields Country_ID (PK autonumber), country_code, Country_Desc

In your case you changed this table to tblGeo (I think?), so in that table if you want it to match up with your excel spreadsheet you would change your field names to match what you have on your spreadsheet but my suggestion is to leave the Country_ID field alone, let it continue to be your PK.

2. Open the table and shrink the PK field (country_ID) to a 0 width
3. Cut and paste your spreadsheet information into this table

just make sure your fields are in the same order as they appear on your spreadsheet.

Once you do this for all of your support tables (taking information from your spreadsheet) you'll have to modify the code I sent you to handle your new field names.

I don't see a reason why you separated your import and yearly amounts table, you're basically just tracking summaries of tonnage so this kind of normalized structure, though correct may not be worth your effort. If you decide to keep the structure you currently have (again, it's a normalized structure which is good) you'll also have to modify the code I sent you to populate your information into that new structure rather than the way I had it in my example.

[AAR]

No , sorry I am not separating the tables , I will use the same structure as you have sent to me (ignore the DB , just wanted to show you the spreadsheets) just wanted to know if there is any way to import the missing information for the support tables without copy paste , coz they are actually longer than you can see in the attached file.

[rpeare]

there's two ways,

1. you can import them directly into a table then add a primary key (autonumber) after the fact

2. you can link the spreadsheet into access, then use an append query to paste that information to your local table.