Generate an autonumber for a group of records. (1 Viewer)

[Zak14]

I've got a table, where each record is a group of students. So the fields include Student1, Student2, Student3, etc. I know this is bad database design, but I've done this because I want the group number (primary key) to be automatically generated.
Now, I want to add more information for each of these students as if they're seperate records but I also want an autonumber for the group.
How do I go about doing this. Or is there a workaround to this?

 

[CJ_London]

The reason you give for a bad database design is not a good reason to have a bad database design.

A bad database design will give you many issues, the first of which you have already identified.

Do it properly - you need 3 tables

tblGroups
GroupPK autonumber
GroupName text
..etc

tblStudents
StudentPK autonumber
StudentName text
...etc

tblAssigned
AssignedPK autonumber
GroupFK number
StudentFK number

 

[Zak14]

The reason you give for a bad database design is not a good reason to have a bad database design.

A bad database design will give you many issues, the first of which you have already identified.

Do it properly - you need 3 tables

tblGroups
GroupPK autonumber
GroupName text
..etc

tblStudents
StudentPK autonumber
StudentName text
...etc

tblAssigned
AssignedPK autonumber
GroupFK number
StudentFK number

Okay, I've done that.
Here are my tables:

tblCourses
CourseID (PK)
CourseName
...CourseCode

|
one-to-many
|

tblGroups
GroupID (PK)
CourseID (FK)
...CourseStartDate
...CourseEndDate

|
one-to-many
|

tblStudentCourseRecord
RecordID (PK)
GroupID (FK)
StudentID (FK from main table)
...Score
...Result

But I don't know how to implement these tables in my form (from the main table).
The reason I wanted to use the badly designed method, was because it would've been easier for me to put it in my form.

I want my form to display a subform which lists all the records from tblStudentCourseRecord for each student.
How do I do that?

 

[Zak14]

Here's further explanation.

On my main form (with students details - Primary Key: StudentID), I will have a link to a popup form, where the user adds/edits student groups (tblGroups records).

Now, within the main form, I'd also like a subform for tblStudentCourseRecord (that is filtered to the individual student) where the user can manually assign the GroupID for the individual records.

I've rewritten this 3 times to make it easier to understand. Please ask if you don't understand something.
Thanks. I know it's complicated. I'd be really grateful.

 

[CJ_London]

From what you have descibed in your post, you are missing a tblStudents -I presume this is an oversight?

On my main form (with students details - Primary Key: StudentID), I will have a link to a popup form, where the user adds/edits student groups (tblGroups records).

OK so there are two forms, you main form will have a recordsource of tbleStudents and the popup form with a recordsource of tbleGroups. I presume this is what you require - basically there is no requirement to filter the popup form based on something in the main form.

Now, within the main form, I'd also like a subform for tblStudentCourseRecord (that is filtered to the individual student) where the user can manually assign the GroupID for the individual records.

To do this create a form based on tblStudentCourseRecord and add it you your main form as a subform. In your subform control properties set the LinkChild property to StudentID and LinkMaster property to StudentID.

I'm not quite clear what the relationship is between groups and courses. The way you have structured it implies that one student can belong to many groups and one group can only have one course - is this correct?

 

[Zak14]

Thanks a lot! It works.
And yes, that's correct.