Hi All,

How can I make multiple data for the same column go in access.

eg: here is what My excel table looks like:

Student_ID Subjects Grades

123456 Eng A
123456 Hist B
123456 Math B+
123456 Bio B-

here is what I have in access for recordset:

With rs
.AddNew
.Fields("Student_ID") = Range("A3").Value
.Fields("Subject") = Range("B3").Value
.Fields("Grades") = Range("C3").Value

.Update
End With
r = r + 1

Thanks in advance

I presume you mean you are getting an error inserting rows into the table.
The field Student_ID cannot be set as the primary key if you want many rows
to contain the same ID. Remove the primary key. Also, if the field is
indexed select "Yes (Duplicates OK)"

A better way may be to have Student_ID as the primary key (only one row per
student) and have multiple fields for each subject, Math, Engrish, Spolling,
Gramor, etc (he he he). Then you only need to save the grade under each
subject field.

"sam" wrote:

Hi All,

How can I make multiple data for the same column go in access.

eg: here is what My excel table looks like:

Student_ID Subjects Grades

123456 Eng A
123456 Hist B
123456 Math B+
123456 Bio B-

here is what I have in access for recordset:

With rs
.AddNew
.Fields("Student_ID") = Range("A3").Value
.Fields("Subject") = Range("B3").Value
.Fields("Grades") = Range("C3").Value

.Update
End With
r = r + 1

Thanks in advance

Oops.. Sorry I fogot to mention one thing.

There is a button "Submit" on the excel sheet that exports all of the
student data at once in the database.. and yea,, I DONT want the student ID
as the primary Key.. But I want to insert multiple student data in a seperate
row.. all at once..



"Charlie" wrote:

I presume you mean you are getting an error inserting rows into the table.
The field Student_ID cannot be set as the primary key if you want many rows
to contain the same ID. Remove the primary key. Also, if the field is
indexed select "Yes (Duplicates OK)"

A better way may be to have Student_ID as the primary key (only one row per
student) and have multiple fields for each subject, Math, Engrish, Spolling,
Gramor, etc (he he he). Then you only need to save the grade under each
subject field.

"sam" wrote:

Hi All,

How can I make multiple data for the same column go in access.

eg: here is what My excel table looks like:

Student_ID Subjects Grades

123456 Eng A
123456 Hist B
123456 Math B+
123456 Bio B-

here is what I have in access for recordset:

With rs
.AddNew
.Fields("Student_ID") = Range("A3").Value
.Fields("Subject") = Range("B3").Value
.Fields("Grades") = Range("C3").Value

.Update
End With
r = r + 1

Thanks in advance