Import from excel multiple field problem

[Purge]

Hello,

Not sure I posted to the correct forum, but here goes

I would like to import an excel sheet into a new access table. However I am having problems
with multiple fields.

Lets say for instance that in my excel sheet I have the following fields

Field name Name Exam Exam_score Date
Purge Maths 60 7-01-2007

--------------------------

Then I import that into a table. Then that is no problem, but then going back to the excel sheet I have noticed that one study could take many different exams. Hence the below

Name Exam Exam_score Date
Purge English 70 7-01-2007


------------------------
we have a problem in the table because the fields begin to multiply if Purge has taken 20 exams

fieldname
name
exam
exam_score
date
exam2
exam_score2
exam3
exam_score3
exam4

and so on....can you imagine if I hit 40?

I was thinking maybe I could add some lookup field to a cell in excel or access? Am I on the correct path? Since I want to import, but the multiple fields/columns are getting in the way.

 

[GohDiamond]

Each record needs to be unique.
Try this:Split your information into two tables. All student names and identifiers in table A, Each Student/ExamTaker should have a StudentID/ExamTakerID. Then put all exam information in table B, be sure to include the related StudentID/ExamTaker ID.

Table A
007-001 Smith, John
007-002 Jones, George
007-003 Williams, Robert

Table B
007-001 Exam1 65% 12/01/06
007-001 Exam2 78% 12/04/06
007-001 Exam3 82% 12/09/06
007-002 Exam2 77% 12/04/06
007-003 Exam1 97% 12/01/06
007-003 Exam3 92% 12/09/06

Run a query with the two tables
Relate the two tables using the 007-00x columns (StudentID/ExamTakerID)

The result of the query will be that you'll see a record for all 6 exams in table B
But Table A will still have just the 3 Students listed

Maybe this wil help

Goh