Sub Records

[Help4me]

Hello and Merry Christmas!

I have a relatively simple database to track assignments. I need to track
sub assignments from a main assignment. For example, there is the main
assignment and several sub assignments that are required to complete the main
assignment. How can I create the sub assignments that are linked to the main
assignment? I need the sub assignments to appear on reports.

It should be noted that I do not know how to write code.

Thank you for any and all assistance in this matter.

 

[Jeanette Cunningham]

Hi,
the form wizard will help you build a form with a subform where you can add
the sub assignments

Jeanette Cunningham

 

[Klatuu]

You need two tables. The Assignments table and a SubAssignments table.
The SubAssignment table should carry the primary key of the Assignment table
as a foreign key.

Then to enter data, use a form/subform construct where the form's
recordsource is the Assignment table and the subform's recordsource is the
SubAssignment table.

 

[Help4me]

Thank you so much. I originally thought of two tables, but hesitated due to
not wanting to have two tables with identical fields. Should the
SubAssignments table have the same fields as the Assignments table? Also,
will this method work for multiple sub assignments under the one assignment?

 

[Jeanette Cunningham]

Hi,
2 tables like this.

tblAssignments ...
AssignmentID >Primary Key
AssignmentName
AssignmentDate
Other Assignment details


tblSubassignments ...
SubassignmentID >Primary Key
AssignmentID >Foreign Key from tblAssignments
Subassignment Name
Other subassignment details

One assignment can have many subassignments
Any subassignment can only belong to one assignment

tblAssignments related 1 to Many to tblSubassignments


If any subassignment can be in more than one assignment, you would need to
use a junction table to handle the many to many relationship.

Jeanette Cunningham

 

[Help4me]

Thank you so much. This information was very helpful.

On reports, I am attempting to count unique AssignmentIDs under groups (for
example different months the assignments are due). Unfortunately, if the
assignment has two sub assignments, the count function counts the
AssignmentID twice (since it lists the AssignmentID with the SubAssignment
ID). Do you know a way to get around this?

 

[Jeanette Cunningham]

I need to brush up on reports, maybe someone else can advise with this step.

Jeanette Cunningham