DAO Recordset Nested Loops

[Quiver]

I'm probably going off in the wrong direction, so feel free to offer other suggestions here.

Overview: I need to look at a common field in two tables and find where they are equal. When equal, I need to UPDATE another table with a value, and when not equal, I need to UPDATE that table with a different value.

Details: The first table is tblKidCharges and the second table is tblTeach. Both tables contain the field FamilyID. When both a child and his parent (the Teacher) are present, their family should receive a $0.00 charge that day. However, if the child is present and the parent is not present, the family will receive a $15.00 charge.

Work Thus Far: I figure this will be done with 2 recordsets. I have experimented with cycling through the recordsets to find where they are equal, but I can't get them to then UPDATE the table (tblCharges).

Any thoughts would be greatly appreciated!

[ADezii]

I'm probably going off in the wrong direction, so feel free to offer other suggestions here.

Overview: I need to look at a common field in two tables and find where they are equal. When equal, I need to UPDATE another table with a value, and when not equal, I need to UPDATE that table with a different value.

Details: The first table is tblKidCharges and the second table is tblTeach. Both tables contain the field FamilyID. When both a child and his parent (the Teacher) are present, their family should receive a $0.00 charge that day. However, if the child is present and the parent is not present, the family will receive a $15.00 charge.

Work Thus Far: I figure this will be done with 2 recordsets. I have experimented with cycling through the recordsets to find where they are equal, but I can't get them to then UPDATE the table (tblCharges).

Any thoughts would be greatly appreciated!

1. Kindly post the Field Names and their Data Tyeps in tblKidCharges, tblTeach, and tblCharges.
2. List any Relationships among the Tables and the Keys involved, along with their Data Types.
3. Define the conditions under which a Child could be present in tblKidCharges and a Parent would also be present in tblTeach.
4. What condition would indicate that a Child was present in tblKidCharges and his/her Parent wasn't in tblTeach?
5. How would you know the specific Record to Update in tblCharges? [FamilyID]?

[Quiver]

1. Kindly post the Field Names and their Data Tyeps in tblKidCharges, tblTeach, and tblCharges.
2. List any Relationships among the Tables and the Keys involved, along with their Data Types.
3. Define the conditions under which a Child could be present in tblKidCharges and a Parent would also be present in tblTeach.
4. What condition would indicate that a Child was present in tblKidCharges and his/her Parent wasn't in tblTeach?
5. How would you know the specific Record to Update in tblCharges? [FamilyID]?

1)
tblKidCharges
KidBatchID (AutoNumber)
BatchID (Number)
ChildID (Number)
Dte (Date/Time)
FamilyID (Number)
LName (Text)
Group (Text)

tblTeach
TeachBatchID (AutoNumber)
BatchID (Number)
FamilyID (Number)
Dte (Date/Time)
FamilyLName (Text)

tblCharges
BatchID (Number)
Dte (Date/Time)
ChildID (Number)
DateStamp (Date/Time)
RateID (Number)
FamilyID (Number)

2)
There are no relationships currently between these tables, but there is a one-to-many between tblFamily and tblKids that link by the FamilyID.

3) & 4)
The data ends up in tblKidCharges and tblTeach by way of 2 multi-select listboxes on frmMAIN. When "charges" are being entered, the user will choose all of the kids present, and then all of the teachers present. A teacher will rarely be present if her children are not, but kids may be present without their teacher/parent being present. When a teacher is present, all of her kids that are also present that day are not charged for the day.

5)
I am envisioning that the charges will ultimately be posted to tblCharges along with the rate that was charged. I don't want the user to have to choose a rate. I want the single click of a button on frmMAIN to take all of the kids and teachers present that day (from the list boxes) and put them in their respective tables. Then begin the process of looking for kids who have their parent/teacher also present, so that the $0.00 rate can be chosen, and subsequently appending that data to tblCharges. I would then like to take the remaining kids, and charge them the rate that is detailed in tblRates (I'm pretty sure I have this step covered).


I hope that clears things up. Let me know if you need more info.

Thanks for your help!

[ADezii]

1)
tblKidCharges
KidBatchID (AutoNumber)
BatchID (Number)
ChildID (Number)
Dte (Date/Time)
FamilyID (Number)
LName (Text)
Group (Text)

tblTeach
TeachBatchID (AutoNumber)
BatchID (Number)
FamilyID (Number)
Dte (Date/Time)
FamilyLName (Text)

tblCharges
BatchID (Number)
Dte (Date/Time)
ChildID (Number)
DateStamp (Date/Time)
RateID (Number)
FamilyID (Number)

2)
There are no relationships currently between these tables, but there is a one-to-many between tblFamily and tblKids that link by the FamilyID.

3) & 4)
The data ends up in tblKidCharges and tblTeach by way of 2 multi-select listboxes on frmMAIN. When "charges" are being entered, the user will choose all of the kids present, and then all of the teachers present. A teacher will rarely be present if her children are not, but kids may be present without their teacher/parent being present. When a teacher is present, all of her kids that are also present that day are not charged for the day.

5)
I am envisioning that the charges will ultimately be posted to tblCharges along with the rate that was charged. I don't want the user to have to choose a rate. I want the single click of a button on frmMAIN to take all of the kids and teachers present that day (from the list boxes) and put them in their respective tables. Then begin the process of looking for kids who have their parent/teacher also present, so that the $0.00 rate can be chosen, and subsequently appending that data to tblCharges. I would then like to take the remaining kids, and charge them the rate that is detailed in tblRates (I'm pretty sure I have this step covered).


I hope that clears things up. Let me know if you need more info.

Thanks for your help!

Quiver, I must admit that I am having a difficult time following your logic, and I do believe that it is a little unorthodox, but let's take it 1 step at a time.

1. tblKidsCharges will be populated via multiple selections made from a Combo Box on frmMain. This Combo Box must then display all the Fields in tblKidCharges except Date. If this is true:
   1. What is the Name of the Combo Box?
   2. What is the Row Source for the Combo Box?
   3. How many Columns are there in the Combo Box (displayed and non-displayed)?
   4. What are the Column Widths (basically same question as previous)?
   5. Does the Combo Box have a Control Source? If so, what is it?
   6. Does the Combo Box have a Bound Field? If so, what is it?
2. Forget tblTeach and tblCharges for now. If the User makes 4 Kid selections from the Combo Box, you would then want a Button Click() to write 4 Records to tblKidCharges, correct?
3. Fill in the missing blanks, and we'll take it from there.

[BradHodge]

Quiver, I must admit that I am having a difficult time following your logic, and I do believe that it is a little unorthodox, but let's take it 1 step at a time.

1. tblKidsCharges will be populated via multiple selections made from a Combo Box on frmMain. This Combo Box must then display all the Fields in tblKidCharges except Date. If this is true:
   1. What is the Name of the Combo Box?
   2. What is the Row Source for the Combo Box?
   3. How many Columns are there in the Combo Box (displayed and non-displayed)?
   4. What are the Column Widths (basically same question as previous)?
   5. Does the Combo Box have a Control Source? If so, what is it?
   6. Does the Combo Box have a Bound Field? If so, what is it?
2. Forget tblTeach and tblCharges for now. If the User makes 4 Kid selections from the Combo Box, you would then want a Button Click() to write 4 Records to tblKidCharges, correct?
3. Fill in the missing blanks, and we'll take it from there.

If I could count the number of times my wife has said she was having trouble following my logic :)


1a) The selections on frmMAIN are actually made from List Boxes. The one for the kids is ListKids and is made up of 3 columns (ChildID, LName and FName) with Child ID being bound and invisible to the user.

The one for the teachers is ListTeach and is made up of 4 columns (FamilyID, FamilyLName, MomFName and TeacherSub(this is a parameter column that just narrows the lists to those parents that are either parents or subs)). FamilyID is the bound column and also invisible.

1b) The Row Source for ListKids is tblKids.

1c) See 1a

1d) The exact column widths are ListKids: 0";0.625";0.677 1" and ListTeach: 0"; 0.625";0.6771"; 0"

1e) Neither List Box has a Control Source

1f) See 1a

2) This is the code that writes the records to tblKidCharges

Dim strSQL as String
Dim strSQL2 as String
Dim strSQL3 as String
Dim ctl as Control
DoCmd.SetWarnings False
    Set ctl = Forms![frmMain]![ListKids]
    strSQL = "INSERT INTO tblKidCharges (BatchID, ChildID, Dte) VALUES("
    strSQL = strSQL & Me.BatchID & ", "
    strSQL2 = Me.txtDate & "#"
    For Each varItem In ctl.ItemsSelected
        strSQL3 = strSQL & ctl.ItemData(varItem) & ", #" & strSQL2 & ");"
        CurrentDb.Execute strSQL3
    Next varItem
    DoCmd.RunSQL "UPDATE tblKidCharges LEFT JOIN tblKids ON tblKidCharges.childID = tblKids.ChildID " _
                    & "SET tblKidCharges.FamilyID = tblKids.FamilyId, tblKidCharges.LName = " _
                    & "tblKids.LName, tblKidCharges.[Group] = tblKids.Group;"
	DoCmd.SetWarnings True

3) In case it helps, the related tables are as follows:

tblFamily
FamilyID (Number)
FamilyLName (Text)
DadFName (Text)
MomFName (Text)
TeacherSub (Text) --Can have value of Null, Teacher or Sub

tblRates
RateID (Number)
RateDescrip (Text)
Rate (Currency)


Thanks for your time on this. I will be interested in hearing your thoughts even if it's to scrap this design and head off in a different direction :)

[BradHodge]

If I could count the number of times my wife has said she was having trouble following my logic :)


1a) The selections on frmMAIN are actually made from List Boxes. The one for the kids is ListKids and is made up of 3 columns (ChildID, LName and FName) with Child ID being bound and invisible to the user.

The one for the teachers is ListTeach and is made up of 4 columns (FamilyID, FamilyLName, MomFName and TeacherSub(this is a parameter column that just narrows the lists to those parents that are either parents or subs)). FamilyID is the bound column and also invisible.

1b) The Row Source for ListKids is tblKids.

1c) See 1a

1d) The exact column widths are ListKids: 0";0.625";0.677 1" and ListTeach: 0"; 0.625";0.6771"; 0"

1e) Neither List Box has a Control Source

1f) See 1a

2) This is the code that writes the records to tblKidCharges

Dim strSQL as String
Dim strSQL2 as String
Dim strSQL3 as String
Dim ctl as Control
DoCmd.SetWarnings False
    Set ctl = Forms![frmMain]![ListKids]
    strSQL = "INSERT INTO tblKidCharges (BatchID, ChildID, Dte) VALUES("
    strSQL = strSQL & Me.BatchID & ", "
    strSQL2 = Me.txtDate & "#"
    For Each varItem In ctl.ItemsSelected
        strSQL3 = strSQL & ctl.ItemData(varItem) & ", #" & strSQL2 & ");"
        CurrentDb.Execute strSQL3
    Next varItem
    DoCmd.RunSQL "UPDATE tblKidCharges LEFT JOIN tblKids ON tblKidCharges.childID = tblKids.ChildID " _
                    & "SET tblKidCharges.FamilyID = tblKids.FamilyId, tblKidCharges.LName = " _
                    & "tblKids.LName, tblKidCharges.[Group] = tblKids.Group;"
	DoCmd.SetWarnings True

3) In case it helps, the related tables are as follows:

tblFamily
FamilyID (Number)
FamilyLName (Text)
DadFName (Text)
MomFName (Text)
TeacherSub (Text) --Can have value of Null, Teacher or Sub

tblRates
RateID (Number)
RateDescrip (Text)
Rate (Currency)


Thanks for your time on this. I will be interested in hearing your thoughts even if it's to scrap this design and head off in a different direction :)

One more piece of info...

My initial thoughts were to begin the append to tblCharges from frmMAIN (instead of doing the interim append to tblKidCharges and tblTeach). It seems like this would be the more straight forward approach, but I couldn't figure out how to loop through the List Boxes to evaluate the selections, and determine which kids had their parent/teachers present as well.

Incase that's of any benefit.

Thanks again.

[ADezii]

If I could count the number of times my wife has said she was having trouble following my logic :)


1a) The selections on frmMAIN are actually made from List Boxes. The one for the kids is ListKids and is made up of 3 columns (ChildID, LName and FName) with Child ID being bound and invisible to the user.

The one for the teachers is ListTeach and is made up of 4 columns (FamilyID, FamilyLName, MomFName and TeacherSub(this is a parameter column that just narrows the lists to those parents that are either parents or subs)). FamilyID is the bound column and also invisible.

1b) The Row Source for ListKids is tblKids.

1c) See 1a

1d) The exact column widths are ListKids: 0";0.625";0.677 1" and ListTeach: 0"; 0.625";0.6771"; 0"

1e) Neither List Box has a Control Source

1f) See 1a

2) This is the code that writes the records to tblKidCharges

Dim strSQL as String
Dim strSQL2 as String
Dim strSQL3 as String
Dim ctl as Control
DoCmd.SetWarnings False
    Set ctl = Forms![frmMain]![ListKids]
    strSQL = "INSERT INTO tblKidCharges (BatchID, ChildID, Dte) VALUES("
    strSQL = strSQL & Me.BatchID & ", "
    strSQL2 = Me.txtDate & "#"
    For Each varItem In ctl.ItemsSelected
        strSQL3 = strSQL & ctl.ItemData(varItem) & ", #" & strSQL2 & ");"
        CurrentDb.Execute strSQL3
    Next varItem
    DoCmd.RunSQL "UPDATE tblKidCharges LEFT JOIN tblKids ON tblKidCharges.childID = tblKids.ChildID " _
                    & "SET tblKidCharges.FamilyID = tblKids.FamilyId, tblKidCharges.LName = " _
                    & "tblKids.LName, tblKidCharges.[Group] = tblKids.Group;"
	DoCmd.SetWarnings True

3) In case it helps, the related tables are as follows:

tblFamily
FamilyID (Number)
FamilyLName (Text)
DadFName (Text)
MomFName (Text)
TeacherSub (Text) --Can have value of Null, Teacher or Sub

tblRates
RateID (Number)
RateDescrip (Text)
Rate (Currency)


Thanks for your time on this. I will be interested in hearing your thoughts even if it's to scrap this design and head off in a different direction :)

I'm headed off to work and have only a couple of minutes to look at this, but the following code should, (hasn't been tested), loop throught all the Selected Items in ListKids and check each Item against ALL Selected Items in ListTeach. If an equality exists between any Item, based on the Bound Column only, you can then take some action. The logic may have to be changed somewhat, but is this basically what you are referring to?
[CODE=vb]Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm As Variant, varItm_2 As Variant, intI As Integer

Set frm = Forms!YourFormN ame

Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach

'Check every Selected Item in ListKids against every Selected Item in ListTeach
For Each varItm In ctl.ItemsSelect ed
For Each varItm_2 In ctl_2.ItemsSele cted
If ctl.ItemData(va rItm) = ctl_2.ItemData( varItm_2) Then
'indicates equality based on the Bound Columns of both List Boxes
End If
Next varItm_2
Next varItm[/CODE]

[BradHodge]

I'm headed off to work and have only a couple of minutes to look at this, but the following code should, (hasn't been tested), loop throught all the Selected Items in ListKids and check each Item against ALL Selected Items in ListTeach. If an equality exists between any Item, based on the Bound Column only, you can then take some action. The logic may have to be changed somewhat, but is this basically what you are referring to?
[CODE=vb]Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm As Variant, varItm_2 As Variant, intI As Integer

Set frm = Forms!YourFormN ame

Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach

'Check every Selected Item in ListKids against every Selected Item in ListTeach
For Each varItm In ctl.ItemsSelect ed
For Each varItm_2 In ctl_2.ItemsSele cted
If ctl.ItemData(va rItm) = ctl_2.ItemData( varItm_2) Then
'indicates equality based on the Bound Columns of both List Boxes
End If
Next varItm_2
Next varItm[/CODE]

That is in the direction that I was headed. It's nice to see the proper way to nest the two (that always throws me).

Since the bound column in ListKids is ChildID and the bound in ListTeach is FamilyID, I can't use it without modifications. I was however thinking of using a DLookUp for the 1st part of the expression to get the applicable FamilyID. Here is what I have so far...

Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm1 As Variant, varItm2 As Variant, intI As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim strFam As String
Dim strTeach As String
Dim strKid As String
Dim strRate, strRate2 As String
 
Set frm = Forms!frmMain
Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach

DoCmd.SetWarnings False

For Each varItm1 In ctl_1.ItemsSelected
    For Each varItm2 In ctl_2.ItemsSelected
        strFam = DLookup("FamilyID", "tblKids", "ChildID =" & ctl_1.ItemData(varItm1))
        strTeach = Nz(ctl_2.ItemData(varItm2), 0)
        strRate = DLookup("RateID", "tblRates", "[RateDescrip]= '" & _
                    DLookup("Teacher_Sub", "tblFamily", "[FamilyID]= " & strFam) & "'")
       
            If strFam = strTeach Then
                DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID, FamilyID, RateID) VALUES (#" & _
                Me.txtDate & "#, " & ctl_1.ItemData(varItm1) & ", " & strFam & ", " & strRate & ");"
                strFam = Nz(ctl_2.ItemData(varItm2), 0)
            Else:
                strRate2 = DLookup("RateID", "tblRates", "[RateDescrip]= '" & _
                DLookup("Group", "tblKids", "[ChildID]= " & ctl_1.ItemData(varItm1)) & "'")
                DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID, FamilyID, RateID) VALUES (#" & _
                Me.txtDate & "#, " & ctl_1.ItemData(varItm1) & ", " & strFam & ", " & strRate2 & ");"
        End If
    Next varItm2
Next varItm1

DoCmd.SetWarnings True

Here are the current scenarios...
1) If you select 1 or more children and 1 parent/teacher, they will append
correctly.

2) If you select 2 or more children and 2 or more parent/teachers, the append
will double
- The child with a parent/teacher present will append at $0.00 and then
again at the regular rate.
- The child without a parent/teacher present will append twice at the correct
rate).

3) If you select 1 or more children without any parent/teachers selected, no
append occurs.

Any thoughts?

As always... Thanks!

My apologies for the strange indenting... Couldn't get it look as pretty as I wanted

[ADezii]

That is in the direction that I was headed. It's nice to see the proper way to nest the two (that always throws me).

Since the bound column in ListKids is ChildID and the bound in ListTeach is FamilyID, I can't use it without modifications. I was however thinking of using a DLookUp for the 1st part of the expression to get the applicable FamilyID. Here is what I have so far...

Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm1 As Variant, varItm2 As Variant, intI As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim strFam As String
Dim strTeach As String
Dim strKid As String
Dim strRate, strRate2 As String
 
Set frm = Forms!frmMain
Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach

DoCmd.SetWarnings False

For Each varItm1 In ctl_1.ItemsSelected
    For Each varItm2 In ctl_2.ItemsSelected
        strFam = DLookup("FamilyID", "tblKids", "ChildID =" & ctl_1.ItemData(varItm1))
        strTeach = Nz(ctl_2.ItemData(varItm2), 0)
        strRate = DLookup("RateID", "tblRates", "[RateDescrip]= '" & _
                    DLookup("Teacher_Sub", "tblFamily", "[FamilyID]= " & strFam) & "'")
       
            If strFam = strTeach Then
                DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID, FamilyID, RateID) VALUES (#" & _
                Me.txtDate & "#, " & ctl_1.ItemData(varItm1) & ", " & strFam & ", " & strRate & ");"
                strFam = Nz(ctl_2.ItemData(varItm2), 0)
            Else:
                strRate2 = DLookup("RateID", "tblRates", "[RateDescrip]= '" & _
                DLookup("Group", "tblKids", "[ChildID]= " & ctl_1.ItemData(varItm1)) & "'")
                DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID, FamilyID, RateID) VALUES (#" & _
                Me.txtDate & "#, " & ctl_1.ItemData(varItm1) & ", " & strFam & ", " & strRate2 & ");"
        End If
    Next varItm2
Next varItm1

DoCmd.SetWarnings True

Here are the current scenarios...
1) If you select 1 or more children and 1 parent/teacher, they will append
correctly.

2) If you select 2 or more children and 2 or more parent/teachers, the append
will double
- The child with a parent/teacher present will append at $0.00 and then
again at the regular rate.
- The child without a parent/teacher present will append twice at the correct
rate).

3) If you select 1 or more children without any parent/teachers selected, no
append occurs.

Any thoughts?

As always... Thanks!

My apologies for the strange indenting... Couldn't get it look as pretty as I wanted

I'll take everything into work tomorrow, and hopefully, I will give it a good look and see. I will get back to you as soon as the time allows.

[ADezii]

That is in the direction that I was headed. It's nice to see the proper way to nest the two (that always throws me).

Since the bound column in ListKids is ChildID and the bound in ListTeach is FamilyID, I can't use it without modifications. I was however thinking of using a DLookUp for the 1st part of the expression to get the applicable FamilyID. Here is what I have so far...

Dim frm As Form, ctl_1 As Control, ctl_2 As Control
Dim varItm1 As Variant, varItm2 As Variant, intI As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim strFam As String
Dim strTeach As String
Dim strKid As String
Dim strRate, strRate2 As String
 
Set frm = Forms!frmMain
Set ctl_1 = frm!ListKids
Set ctl_2 = frm!ListTeach

DoCmd.SetWarnings False

For Each varItm1 In ctl_1.ItemsSelected
    For Each varItm2 In ctl_2.ItemsSelected
        strFam = DLookup("FamilyID", "tblKids", "ChildID =" & ctl_1.ItemData(varItm1))
        strTeach = Nz(ctl_2.ItemData(varItm2), 0)
        strRate = DLookup("RateID", "tblRates", "[RateDescrip]= '" & _
                    DLookup("Teacher_Sub", "tblFamily", "[FamilyID]= " & strFam) & "'")
       
            If strFam = strTeach Then
                DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID, FamilyID, RateID) VALUES (#" & _
                Me.txtDate & "#, " & ctl_1.ItemData(varItm1) & ", " & strFam & ", " & strRate & ");"
                strFam = Nz(ctl_2.ItemData(varItm2), 0)
            Else:
                strRate2 = DLookup("RateID", "tblRates", "[RateDescrip]= '" & _
                DLookup("Group", "tblKids", "[ChildID]= " & ctl_1.ItemData(varItm1)) & "'")
                DoCmd.RunSQL "INSERT INTO tblCharges (Dte, ChildID, FamilyID, RateID) VALUES (#" & _
                Me.txtDate & "#, " & ctl_1.ItemData(varItm1) & ", " & strFam & ", " & strRate2 & ");"
        End If
    Next varItm2
Next varItm1

DoCmd.SetWarnings True

Here are the current scenarios...
1) If you select 1 or more children and 1 parent/teacher, they will append
correctly.

2) If you select 2 or more children and 2 or more parent/teachers, the append
will double
- The child with a parent/teacher present will append at $0.00 and then
again at the regular rate.
- The child without a parent/teacher present will append twice at the correct
rate).

3) If you select 1 or more children without any parent/teachers selected, no
append occurs.

Any thoughts?

As always... Thanks!

My apologies for the strange indenting... Couldn't get it look as pretty as I wanted

After taking a closer look at everything, I feel as though your iinitial impression in Post #5 may be correct:

I will be interested in hearing your thoughts even if it's to scrap this design and head off in a different direction

You are working with 4 Independent Tables, 2 Related Tables, and attempting to integrate them all into an Application. There seems to be too many unrelated processes and intermediate steps. Personally, i feel as though a Redesign is in order but I'll call on some of the other Experts/Moderators for their viewpoints on the subject.

[nico5038]

I agree with a redesign.
1) Start with defining the needed output like the report "Charges"
2) Use the report fields to reorganize into normalized tables.
3) Design the needed processes and use a form per process like:
Add Child /Update Child/Delete Child
Add Teacher /Update Teacher/Delete Teacher
Record attendance

As far as I see you would need a tblTeacher and a tblChild and for those children with a "teaching parent" a relation table tblTeacherChild . Finally a tblAttendance can be used to record the data for the report.
For the attendance you need to record only the children and add an indication or the parent is a teacher.

When you have multiple groups you might add a "class" table.

Nic;o)

[BradHodge]

I agree with a redesign.
1) Start with defining the needed output like the report "Charges"
2) Use the report fields to reorganize into normalized tables.
3) Design the needed processes and use a form per process like:
Add Child /Update Child/Delete Child
Add Teacher /Update Teacher/Delete Teacher
Record attendance

As far as I see you would need a tblTeacher and a tblChild and for those children with a "teaching parent" a relation table tblTeacherChild . Finally a tblAttendance can be used to record the data for the report.
For the attendance you need to record only the children and add an indication or the parent is a teacher.

When you have multiple groups you might add a "class" table.

Nic;o)

Thanks ADezii and Nic;o) for your input. It sounds as if I was at least partially on the right track. My current tables are...

tblKids
tblFamily (which includes an indicator if there is a teacher or sub in the family)
tblTeachers (which runs my List Box)
tblRates (with 2 different class rates, a teacher rate and a sub rate)
tblCharges

A little more info on how everything currently works.
1) The user chooses all kids present and all teachers/subs present from 2 different list boxes on one form.
2) On the click of a button, the app compares ListKids to ListTeachers and appends the appropriate Kids to tblCharges with the ChildID, FamilyID (both from tblKids), the Date (from frmMAIN), and the RateID from the series of DLookups to tblRates.
3) The append is happening, with the exceptions mentioned in the post above

Here are the current scenarios...
1) If you select 1 or more children and 1 parent/teacher, they will append
correctly.
2) If you select 2 or more children and 2 or more parent/teachers, the append
will double
- The child with a parent/teacher present will append at $0.00 and then
again at the regular rate.
- The child without a parent/teacher present will append twice at the correct
rate).
3) If you select 1 or more children without any parent/teachers selected, no
append occurs.

In addition, Nic;o), I do have separate forms for adding families, children and teachers, and all of them are working well.

I would assume that my tables are fairly normalized (although I'm sure that "fairly normalized" is an oxymoron ), and that the fields that I am wanting to input into tblCharges are good fields (mostly ID fields that can I can reference when generating reports).

Am I way off base here?

Thanks!

Brad.

[ADezii]

I agree with a redesign.
1) Start with defining the needed output like the report "Charges"
2) Use the report fields to reorganize into normalized tables.
3) Design the needed processes and use a form per process like:
Add Child /Update Child/Delete Child
Add Teacher /Update Teacher/Delete Teacher
Record attendance

As far as I see you would need a tblTeacher and a tblChild and for those children with a "teaching parent" a relation table tblTeacherChild . Finally a tblAttendance can be used to record the data for the report.
For the attendance you need to record only the children and add an indication or the parent is a teacher.

When you have multiple groups you might add a "class" table.

Nic;o)

Thanks for the Input, Nico, it is appreciated.

[NeoPa]

Brad,

Firstly (on a matter of simple curiosity) I assume that Quiver (OP) is simply a Nom de Plume?

As to the structure of your database. I would agree with Nico's suggestion and just clarify a little something to do with table structures. It's often tempting to organise things such that a relationship you know to exist is reflected in the structure. In this case Teacher & Parent as a single 'Relationship' as many children have that situation in the classroom. These are however, two distinct relationships, and need to be handled separately in a database for it to be able to help you as much as it can (the database that is). Databases aren't as bright as humans so where you or I could handle this extra level of complexity in our brains, for the most part, a database needs extra (more complicated) programming to make sense of this if the two are tied together in a way that is natural for human thought. This is where Normalisation can help. It keeps it all much simpler.

As a second (technical) comment, I find that when I need to doctor any SQL in my applications I use the Replace() function. This way I can use a template set of SQL and replace those items within it which are changeable. As an illustration :

Private Sub ???
  Dim strSQLTemplate As String
  Dim strSQL as String
  Dim varItem as Variant

  DoCmd.SetWarnings False
  strSQLTemplate = "INSERT INTO tblKidCharges " & _
                   "(BatchID, ChildID, Dte) " & _
                   "VALUES(%B, %C, #%D#)"
  strSQLTemplate = Replace(strSQLTemplate, "%B", Me.BatchID)
  strSQLTemplate = Replace(strSQLTemplate, "%C", Me.ChildID)
  For Each varItem In Me.ListKids.ItemsSelected
    strSQL = Replace(strSQLTemplate, "%D", varItem)
    CurrentDb.Execute strSQL3
  Next ctl
  ...
  DoCmd.SetWarnings True
End Sub

As a bonus (in case you're interested) I've written a short function to handle multiple replaces in one go. Feel free to copy and use. I find this invaluable when I have a whole bunch of fields to insert into a template. I also use it for configuring messages to the operator.

'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
'Using VbBinaryCompare means that case is not ignored.
Public Function MultiReplace(ByRef strMain As String, _
                             ByVal varParam As Variant, _
                             ByVal varReplace As Variant, _
                             ParamArray avarArgs())
    Dim intIdx As Integer

    If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
    MultiReplace = Replace(Expression:=strMain, _
                           Find:=Nz(varParam, ""), _
                           Replace:=Nz(varReplace, ""), _
                           Compare:=vbBinaryCompare)
    For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarArgs(intIdx), ""), _
                               Replace:=Nz(avarArgs(intIdx + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intIdx
End Function

[ADezii]

...
As a bonus (in case you're interested) I've written a short function to handle multiple replaces in one go. Feel free to copy and use. I find this invaluable when I have a whole bunch of fields to insert into a template. I also use it for configuring messages to the operator.

'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
'Using VbBinaryCompare means that case is not ignored.
Public Function MultiReplace(ByRef strMain As String, _
                             ByVal varParam As Variant, _
                             ByVal varReplace As Variant, _
                             ParamArray avarArgs())
    Dim intIdx As Integer

    If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
    MultiReplace = Replace(Expression:=strMain, _
                           Find:=Nz(varParam, ""), _
                           Replace:=Nz(varReplace, ""), _
                           Compare:=vbBinaryCompare)
    For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarArgs(intIdx), ""), _
                               Replace:=Nz(avarArgs(intIdx + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intIdx
End Function

Hello NeoPa, I'm a little confused on the Stop and Step Statements (Lines 9 and 14). I'm assuming the Step is needed because the Find and Replace Arguments occupy consecutive positions in avarArgs, but the Stop has me confused, probably because it is rare that you see it in action (LOL). If you have a minute, please expand on this.

BTW, thanks for your assistance in this Thread.