Hello all.

I have been trying to get some data into a usable format and I am hoping you all can assist me. I received several files in text format that I was able to import into access and eventally get into a single database. All together I have 13 tables that I would like to merge into one. There are several issues that may prevent this from being a simple project.

1) the data contains only about 50 columns, but over one million rows.

2) I am trying to combine data that does not overlap. For example, I want the data to be sorted by id number, the one common column heading in all tables. However, the ids do not all overlap. The ids are linked to individuals, and because it spans a long time period not all individuals appear in all tables. That's okay for me, I would like access to use all id numnbers and just leave the subsequent columns blank, where data does not exist.

3) Some of the other column headings overlap because the same data for different years was stored in separate files.

This may seem very confusing, so I'll try to give an example.

File 1 contains this information (only alot more of it):

ID Gender Ethnicity
123 F W
678 M W
187 F A

File 2 contains this information:

ID degree awarded year awarded
678 AA 1995
456 BA 2001
678 BA 2003

File 3 contains this data:

ID course enrolled year
678 history I 1996
120 algebra III 2004
319 english remedial 2007

I would like these to read like so in one file:

ID gender ethnicity degree awarded year awarded course enrolled year
123 F W
678 M W AA 1995 history I 1996
678 M W BA 2003 hsitory I 1996
187 F A
456 BA 2001
120 algebra III 2004
319 english remedial 2007

I would like a better way to work with individuals like number 678 who holds two degrees from different years without repeating other information. I'm afraid the only way to do that would be to redo all the column headings, but I would appreciate any input on that as well. Also, please take the word "merge" lightly. I am not set on any one way of doing this, any suggestions are appreciated. Just an FYI, I am new to access, so step by step instructions would be appreciated. Thanks in advance. Eventually this will be imported to a statistical software program, but at the moment I need to it to be one table before I can proceed. I tried to line up these columns, but am inexperienced with that as well, so I apologize if this is difficult to read.

Hi,



What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

A relational database, such as Access, is rarely useful with just one huge table. It is better if you can design the database in a "relational model."

If you are not familiar with that term, you might take a look at this Wiki article for some insights: Normalization

Just my 2 cents...

It looks like you're trying to denormalize a set of tables that is essentially normalized.
If you're trying to slam all rows for user 678 into one row so you can see everything for that person by scanning one row, you're going to run into major problems.

User 678 has 2 degrees which would look like:
678 M W AA 1995 history I 1996 BA 2003 hsitory I 1996

painful, but what about "classes" he might have taken 30 classes which would make for a VERY long row.
Then you'll have the people who took 1 class, for a very short row.

Best to leave the data as-is and use queries to join the tables as needed.
I'm also guessing that any statistical analysis program would work better with normalized data rather than 1 row per person with all info on the one row.

Maybe I'm not hearing what you're asking.
Thoughts?
Rob

On re-reading, looks like I WAS reading it wrong. sorry.
Although even looking at your current take on it, you're going to get 25 rows or more for the first degree for the person
and another 25 rows or more for the second degree for the same person. Seems kinda painful to me.
I still think you can leave the tables as-is and join when you need to combine more info about a particular person.

Rob

Here are some links to articles that will help you get your feet under you.

You are now working in a relational database. Anything you might have learned from working with Excel is going to impede your progress, so try not to even think about it now.

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS Basics for Programming
• Database Answers (free models)
• MSKB 283878: Description of the database normalization basics
  
• Presentation and sample db