Importing large amount of data into multi-table database? HELP!

[Cosmos75]

I have a database with a multi-table structure.

Main Form – Student
Second Form – Marks
Third Form – Classes

Student has a one-to-many relationship with Marks.
Classes has a one-to-many relationship with Marks.
Therefore, Student as a many-to-many relationship with Classes via a junction table (Marks)

I have all the ,forms, queries and reports I need.

The problem I have run into is that when I get data (Marks) in the form of an excel spreadsheet, I have to enter the data (Marks) one by one using a form I have created that has all the tables in one nice form.

Is there anyway to import this data and maintain the relationship between all tables?

I’ve tried to copy the marks but then I have to manually go and choose which mark belongs to which student and class!

Please help!

 

[Cosmos75]

Edit Poll

Last option in poll should read

"NO! This is something I already know how to do"!

or

"NO! This is something I know how to do"!

Any grammar experts lurking about?

 

[pcs]

cosmos75,

why are you adding a poll to all your posts?

al

 

[cogent1]

Surely you don't just get the raw marks. Nothing to identify the student or class?

 

[Cosmos75]

pcs,

I put a poll just to see it at work and to see if other people had an similiar question to mine. Am I supposed to use the poll only in special cases?

cogent,

My table structure is actually much more complicated than my example. Didn't want to give my entire data structure as I wanted to get an general idea on how to do this.

I may or may not get something to identify the raw marks, but it may be in the form of an excel sheet name or the name of the table?

Maybe just a list of the raw data with a comment in the email that it is a list of one student's marks sorted by class name alphabetically? or vice versa?

Maybe a table with the class in a rows and the student's name as the header or vice versa?

Maybe each class on a sheet and students in rows or vice versa?

I want to know if there is a basic way to handle data that imported from excel.

I can import raw marks into the Marks table but then there is no identifier back to the Students and Classes table.

 

[cogent1]

I am interested in this topic because I work in an academic instuitute and face similar sorts of problems. But there are three common elements in all the mark sheets I receive.

1) A student registration Number

2) A module (course) code

3) A mark or grade


If you have all these factors, (regardless of how they might be presented to you,) it is possible to automate the import process by using code. It may be that you might need different procedures depending on how your data is formatted, but it's still possible one way or another.

The crucial part of the process is preserving the referential integrity of your data and that means carefully planning the sequence in which data is added to your tables. In Access97, key violations during a coded import procedure do not generate an error message, so you could end up with missing data if you don't get it right. Of course , using code, you can temporarily remove relationships while importing , if you are a brave soul.


It might be possible to pivot incoming data in excel to give a more uniform import format. Bu that's impossible to judge without more specific details of your incoming data.

Anyway, those three elements must be present in your data or you can't even enter data manually without violating referential integrity.


HTH.

 

[Cosmos75]

cogent1,

So, say I have manipulated the data to be an un-normalized version of my data-base? How do I import the data to my multi-table database? I know I can import data to ONE table but how to I import data from an excel table into a multi-table database?

THANKS!

 

[cogent1]

It is possible to import a specified range of cells and append them to an existing table, provided certain criteria are satisfied. (See Help under Importing or Linking Excel Spreadsheets). You can do this for each affected table in turn, it doesn't have to be one operation.

Or you can import the data into a temporary holding table and use append queries or code to distribute the fields to your main database tables as necessary.


I know this can be done, because I have managed it with DBF and txt files in the past. But maybe my database wasn't as complex as yours.

Perhaps you can give a specific example of the type of fields you are importing and how you wish them to be distributed, to give me a better idea of your problem?

 

[Cosmos75]

I'll post my database here when I get the chance.

It's not really for keeping track of Students and their class scores (although someday I hope to be doing that! But that's another story!).

I'm creating a simple project management database (nothing like MS Project of Primavera).

 

[Cosmos75]

Here it is...

I had to take a lot out to get it to 100KB so feel free to ask me anything about the database!

THANKS!!

 

[Cosmos75]

Here's a sample database with an excel file and access file that contains the same data that I would like to import into an existing database. Hopfully this will help illustrate my problem.

It is base on Sport (Football/Basketball) / League (CFL/NFL/NBA/WNBA) / Position (QB/WR/RB/PG/SG/C) / Player (NAME/Jersey Number)

Please help! I am baffled as to how to do this!

In this database, I use the fields as Primary Keys, but more often then not, I just assigned an autonumber as the primary key, are there any problems with doing this?

 

[Cosmos75]

Is this how?

Since my tables are all broken up, do I need my data to be an excel version of my tables (un-normaliazed ) and then import that into a query that gathers back data from my tables that matches the excel version of my tables? Which kind of query would I use?