Calculated fields in main form based on criteria in sub form

[Data Tim]

Using Access 2003, I am trying to get calculated fields in a main form from a
subform based on criteria in the subform. I’m trying to run this based on
queries (which may not be the best option). Below is the structure on the
Main and Sub forms.

Main form (goat survey table and Total queries)
Survey key (primary key, autonumber)
Other information associated with survey (date, time, weather)
NE Sum Query (uses information from the subform, SQL posted farther down
in message, other queries differ in what Quadrant equals)
Survey Key (group by) – used to join to Survey table
Quadrant = NE (group by)
Group Total (Sum) AS NE Total
NW Sum Query
SE Sum Query
SW Sum Query
Sub form (goats detected, qry fields)
Detected key (primary key, autonumber)
Survey key (foreign key, from goat survey)
Time
Quadrant (area where goat was seen: compass point - NE, NW, SE, and SW)
Adults (number of adults, integer)
Young (number of young, integer)
Group Total (Adults + Young; total number of goat in a group)

What I would like to have on the Main form is a total field for each
Quadrant (e.g. Calculated total fields: NE Quad, NW Quad…)

The SQL statement used in the Total queries that Sums on a Quadrant (example
is from the NE quadrant):

SELECT qryGoat_Detection.Goat_Survey_Key, qryGoat_Detection.Quadrant,
Sum(qryGoat_Detection.Group_Total) AS NE_Total
FROM qryGoat_Detection
GROUP BY qryGoat_Detection.Goat_Survey_Key, qryGoat_Detection.Quadrant
HAVING (((qryGoat_Detection.Quadrant)="NE"));

The trouble I have is that if no goats were seen in a quadrant (quadrant not
entered into Goats Detected), no values for the whole query are returned
(e.g. Survey Key =1 (survey data entered); with that survey, no goats seen in
Quad SW, so SW is not entered into the Detected table. Because SW is Null,
the query returns Null for All fields (if the null sum is removed the query
returns in the values for goat survey).

I tried entering a NZ function into the query, but did not know how to
insert it (if possible) into the query statement and could not get it to
work. Would a Sum or DSum hidden in the subform’s footer and passed to the
main form work better?

All help is much appreciated. Thanks in advance.
Tim

 

[NKTower]

How about something like this...

In your MAIN form, add code like this. I've used DAO, use other access
method as you wish. DAO is just simplest to describe here.

Private Sub Form_Timer()
Dim RS As DAO.Recordset
Dim SQL AS String

Me.TimerInterval = 0 ' turn off the timer
' the next is all one line, but you can wrap using quotes, ampersand and
continue
SQL =
"Select Sum(Iif(quadrant='NE',NZ(Adults)+NZ(Kids),0)) As NE_Count,
Sum(Iif(quadrant='SE',NZ(Adults)+NZ(Kids),0)) As SE_Count,
Sum(Iif(quadrant='SW',NZ(Adults)+NZ(Kids),0)) As SW_Count,
Sum(Iif(quadrant='NW',NZ(Adults)+NZ(Kids),0)) As NW_Count
FROM tblSurvey
WHERE ( blah_blah_blah )
GROUP BY blah_blah_blah, blah_blah_blah;"

Set RS = CurrentDb.OpenRecordset(SQL)
RS.MoveFirst
' move the calculated stuff into the main form
Me.NE = RS("NE_Count")
Me.SE = RS("SE_Count")
ME.SW = RS("SW_Count")
ME.NW = RS("NW_Count")
RS.Close
Set RS = Nothing
End Sub

Access will look at Adults. If it is NULL, it will replace with 0 for the
calculation. Same for Kids. It will then add them. It will evaluate the
Immediate If (IIF) and determine if it is, say, "NE". If so, it will put the
sum into the totaller for NE (NE_Count) otherwise it will add a 0. Does the
same for the other quadrants. Does it all in one pass of the record set
defined by the WHERE clause, and groups (if necessary) by the GROUP BY
clause. (If your WHERE clause defines the grouping, then you don't need a
GROUP BY as you are adding across all records selected by the WHERE clause,
right?

Now, in your sub-form as part of the data entry, when you want to force a
re-calculation, perhaps as part of the Form_AfterUpdate() event, or even a
field AfterUpdate() event...

If Me.Dirty THEN Me.Dirty = False ' force post
Forms![Main Form].TimerInterval = 10 ' milliseconds

That should do it. Setting the timer from the sub-form triggers the timer
event in the main form, which causes a re-calculation. I suspect some of the
wizards here may find an easier way to do it, but you can try this if you
don't get another/beter response.

 

[Tim Shepherd]

Thanks, I'll give this a try and see how it goes.

How about something like this...

In your MAIN form, add code like this. I've used DAO, use other access
method as you wish. DAO is just simplest to describe here.

Private Sub Form_Timer()
Dim RS As DAO.Recordset
Dim SQL AS String

Me.TimerInterval = 0 ' turn off the timer
' the next is all one line, but you can wrap using quotes, ampersand and
continue
SQL =
"Select Sum(Iif(quadrant='NE',NZ(Adults)+NZ(Kids),0)) As NE_Count,
Sum(Iif(quadrant='SE',NZ(Adults)+NZ(Kids),0)) As SE_Count,
Sum(Iif(quadrant='SW',NZ(Adults)+NZ(Kids),0)) As SW_Count,
Sum(Iif(quadrant='NW',NZ(Adults)+NZ(Kids),0)) As NW_Count
FROM tblSurvey
WHERE ( blah_blah_blah )
GROUP BY blah_blah_blah, blah_blah_blah;"

Set RS = CurrentDb.OpenRecordset(SQL)
RS.MoveFirst
' move the calculated stuff into the main form
Me.NE = RS("NE_Count")
Me.SE = RS("SE_Count")
ME.SW = RS("SW_Count")
ME.NW = RS("NW_Count")
RS.Close
Set RS = Nothing
End Sub

Access will look at Adults. If it is NULL, it will replace with 0 for the
calculation. Same for Kids. It will then add them. It will evaluate the
Immediate If (IIF) and determine if it is, say, "NE". If so, it will put the
sum into the totaller for NE (NE_Count) otherwise it will add a 0. Does the
same for the other quadrants. Does it all in one pass of the record set
defined by the WHERE clause, and groups (if necessary) by the GROUP BY
clause. (If your WHERE clause defines the grouping, then you don't need a
GROUP BY as you are adding across all records selected by the WHERE clause,
right?

Now, in your sub-form as part of the data entry, when you want to force a
re-calculation, perhaps as part of the Form_AfterUpdate() event, or even a
field AfterUpdate() event...

If Me.Dirty THEN Me.Dirty = False ' force post
Forms![Main Form].TimerInterval = 10 ' milliseconds

That should do it. Setting the timer from the sub-form triggers the timer
event in the main form, which causes a re-calculation. I suspect some of the
wizards here may find an easier way to do it, but you can try this if you
don't get another/beter response.

Using Access 2003, I am trying to get calculated fields in a main form from a
subform based on criteria in the subform. I’m trying to run this based on
queries (which may not be the best option). Below is the structure on the
Main and Sub forms.

Main form (goat survey table and Total queries)
Survey key (primary key, autonumber)
Other information associated with survey (date, time, weather)
NE Sum Query (uses information from the subform, SQL posted farther down
in message, other queries differ in what Quadrant equals)
Survey Key (group by) – used to join to Survey table
Quadrant = NE (group by)
Group Total (Sum) AS NE Total
NW Sum Query
SE Sum Query
SW Sum Query
Sub form (goats detected, qry fields)
Detected key (primary key, autonumber)
Survey key (foreign key, from goat survey)
Time
Quadrant (area where goat was seen: compass point - NE, NW, SE, and SW)
Adults (number of adults, integer)
Young (number of young, integer)
Group Total (Adults + Young; total number of goat in a group)

What I would like to have on the Main form is a total field for each
Quadrant (e.g. Calculated total fields: NE Quad, NW Quad…)

The SQL statement used in the Total queries that Sums on a Quadrant (example
is from the NE quadrant):

SELECT qryGoat_Detection.Goat_Survey_Key, qryGoat_Detection.Quadrant,
Sum(qryGoat_Detection.Group_Total) AS NE_Total
FROM qryGoat_Detection
GROUP BY qryGoat_Detection.Goat_Survey_Key, qryGoat_Detection.Quadrant
HAVING (((qryGoat_Detection.Quadrant)="NE"));

The trouble I have is that if no goats were seen in a quadrant (quadrant not
entered into Goats Detected), no values for the whole query are returned
(e.g. Survey Key =1 (survey data entered); with that survey, no goats seen in
Quad SW, so SW is not entered into the Detected table. Because SW is Null,
the query returns Null for All fields (if the null sum is removed the query
returns in the values for goat survey).

I tried entering a NZ function into the query, but did not know how to
insert it (if possible) into the query statement and could not get it to
work. Would a Sum or DSum hidden in the subform’s footer and passed to the
main form work better?

All help is much appreciated. Thanks in advance.
Tim

 

[Tim Shepherd]

Thanks NK Tower,

I think I missed something. The code with the SQL statement you said to put
it in the MAIN form but the code you posted is under Sub Form_Timer(). I've
tried in Sub Form_Enter(), and I get a bug at "Set=RS..." Moved to the
SubForm code on Timer, and I don't get the bug.
Which do you recommend?

I've changed the SQL by droping the WHERE statement and setting GroupBy to
[Survey_Key]. I'm wanting to add across each survey event.

I'm unsure how to get the values onto the Main form. It looks like I should
have text boxes named NE... (the Me.NE=RS("NE_Count') moves the value to the
corresponding text box?) to accept the value. Is this correct? If so, I'm
not getting the values to show in the text boxes. They stay blank. I've
tried setting the control source for NE "=[Sub Form].form![NE_Count]" and get
"#Error" in the box.

Thanks for your time,
tim

How about something like this...

In your MAIN form, add code like this. I've used DAO, use other access
method as you wish. DAO is just simplest to describe here.

Private Sub Form_Timer()
Dim RS As DAO.Recordset
Dim SQL AS String

Me.TimerInterval = 0 ' turn off the timer
' the next is all one line, but you can wrap using quotes, ampersand and
continue
SQL =
"Select Sum(Iif(quadrant='NE',NZ(Adults)+NZ(Kids),0)) As NE_Count,
Sum(Iif(quadrant='SE',NZ(Adults)+NZ(Kids),0)) As SE_Count,
Sum(Iif(quadrant='SW',NZ(Adults)+NZ(Kids),0)) As SW_Count,
Sum(Iif(quadrant='NW',NZ(Adults)+NZ(Kids),0)) As NW_Count
FROM tblSurvey
WHERE ( blah_blah_blah )
GROUP BY blah_blah_blah, blah_blah_blah;"

Set RS = CurrentDb.OpenRecordset(SQL)
RS.MoveFirst
' move the calculated stuff into the main form
Me.NE = RS("NE_Count")
Me.SE = RS("SE_Count")
ME.SW = RS("SW_Count")
ME.NW = RS("NW_Count")
RS.Close
Set RS = Nothing
End Sub

Access will look at Adults. If it is NULL, it will replace with 0 for the
calculation. Same for Kids. It will then add them. It will evaluate the
Immediate If (IIF) and determine if it is, say, "NE". If so, it will put the
sum into the totaller for NE (NE_Count) otherwise it will add a 0. Does the
same for the other quadrants. Does it all in one pass of the record set
defined by the WHERE clause, and groups (if necessary) by the GROUP BY
clause. (If your WHERE clause defines the grouping, then you don't need a
GROUP BY as you are adding across all records selected by the WHERE clause,
right?

Now, in your sub-form as part of the data entry, when you want to force a
re-calculation, perhaps as part of the Form_AfterUpdate() event, or even a
field AfterUpdate() event...

If Me.Dirty THEN Me.Dirty = False ' force post
Forms![Main Form].TimerInterval = 10 ' milliseconds

That should do it. Setting the timer from the sub-form triggers the timer
event in the main form, which causes a re-calculation. I suspect some of the
wizards here may find an easier way to do it, but you can try this if you
don't get another/beter response.

Using Access 2003, I am trying to get calculated fields in a main form from a
subform based on criteria in the subform. I’m trying to run this based on
queries (which may not be the best option). Below is the structure on the
Main and Sub forms.

Main form (goat survey table and Total queries)
Survey key (primary key, autonumber)
Other information associated with survey (date, time, weather)
NE Sum Query (uses information from the subform, SQL posted farther down
in message, other queries differ in what Quadrant equals)
Survey Key (group by) – used to join to Survey table
Quadrant = NE (group by)
Group Total (Sum) AS NE Total
NW Sum Query
SE Sum Query
SW Sum Query
Sub form (goats detected, qry fields)
Detected key (primary key, autonumber)
Survey key (foreign key, from goat survey)
Time
Quadrant (area where goat was seen: compass point - NE, NW, SE, and SW)
Adults (number of adults, integer)
Young (number of young, integer)
Group Total (Adults + Young; total number of goat in a group)

What I would like to have on the Main form is a total field for each
Quadrant (e.g. Calculated total fields: NE Quad, NW Quad…)

The SQL statement used in the Total queries that Sums on a Quadrant (example
is from the NE quadrant):

SELECT qryGoat_Detection.Goat_Survey_Key, qryGoat_Detection.Quadrant,
Sum(qryGoat_Detection.Group_Total) AS NE_Total
FROM qryGoat_Detection
GROUP BY qryGoat_Detection.Goat_Survey_Key, qryGoat_Detection.Quadrant
HAVING (((qryGoat_Detection.Quadrant)="NE"));

The trouble I have is that if no goats were seen in a quadrant (quadrant not
entered into Goats Detected), no values for the whole query are returned
(e.g. Survey Key =1 (survey data entered); with that survey, no goats seen in
Quad SW, so SW is not entered into the Detected table. Because SW is Null,
the query returns Null for All fields (if the null sum is removed the query
returns in the values for goat survey).

I tried entering a NZ function into the query, but did not know how to
insert it (if possible) into the query statement and could not get it to
work. Would a Sum or DSum hidden in the subform’s footer and passed to the
main form work better?

All help is much appreciated. Thanks in advance.
Tim