RUNNING TOTALS

[Guest]

Hi Guys,

I have not found a good way to tackle this problem: How do I get a
calculated field on my form totaled automatically? I found I could not simply
set the record source of the field to: =Sum([MyFiled]). This method usually
returned #Error.

Now I have a form based on my SatsangClass. A Calculated field [Attendance]
has its record source as:
=IIf(IsNull([SatsangID]),Null,DCount("*","SatsangLesson","[SatsangID]=" &
[SatsangID])).

The SatsangLesson table is joined to the SatsangClass through the SatsangID
field (one to many). So far so good, because when I open my form,
[Attendance] is displayed correctly. The problem is that I want a total of
[Attendance] too, and I want this total to be displayed even when I have
filtered my records. Examples of the filtering I have are:

Me.Filter = "[Zone] <> Null"
If Not IsNull(Zone1) Then
Me.Filter = Me.Filter & " And [Zone]=Forms!SatsangCountSchedule!Zone1"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to zone.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
If Not IsNull(Area1) Then
Dim A As String
A = "SELECT Zones.ZoneID FROM Zones WHERE
(((Zones.Area)=Forms!SatsangCountSchedule!Area1))"
Me.Filter = Me.Filter & " And [Zone] IN (" & A & ")"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Area.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
If Not IsNull(Discourse1) Then
Me.Filter = Me.Filter & " And
[Discourse]=Forms!SatsangCountSchedule!Discourse1"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Discourse studied.",
vbInformation, "ECKANKAR AREA ADMIN"
End If
If Not IsNull(Book1) Then
If Book1 = 1 Then
Me.Filter = Me.Filter & " And [Discourse] IS Null"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered for book discussions.", vbInformation,
"ECKANKAR AREA ADMIN"
ElseIf Book1 = 2 Then
Me.Filter = Me.Filter & " And [Book] IS Null"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to discourse studied.",
vbInformation, "ECKANKAR AREA ADMIN"
End If
End If

The above is executed via a button. It starts by getting all records with
"[Zone]<>Null" because the Zone field cannot be null. Zone1, Discourse1,
Area1 are unbound fields I use to filter the records.

So what is a simple way to get totals for the [Attendance] field even when I
run the various filters?

 

[James A. Fortune]

Hi Guys,

I have not found a good way to tackle this problem: How do I get a
calculated field on my form totaled automatically? I found I could not simply
set the record source of the field to: =Sum([MyFiled]). This method usually
returned #Error.

Now I have a form based on my SatsangClass. A Calculated field [Attendance]
has its record source as:
=IIf(IsNull([SatsangID]),Null,DCount("*","SatsangLesson","[SatsangID]=" &
[SatsangID])).

The SatsangLesson table is joined to the SatsangClass through the SatsangID
field (one to many). So far so good, because when I open my form,
[Attendance] is displayed correctly. The problem is that I want a total of
[Attendance] too, and I want this total to be displayed even when I have
filtered my records. Examples of the filtering I have are:

Me.Filter = "[Zone] <> Null"
If Not IsNull(Zone1) Then
Me.Filter = Me.Filter & " And [Zone]=Forms!SatsangCountSchedule!Zone1"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to zone.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
If Not IsNull(Area1) Then
Dim A As String
A = "SELECT Zones.ZoneID FROM Zones WHERE
(((Zones.Area)=Forms!SatsangCountSchedule!Area1))"
Me.Filter = Me.Filter & " And [Zone] IN (" & A & ")"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Area.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
If Not IsNull(Discourse1) Then
Me.Filter = Me.Filter & " And
[Discourse]=Forms!SatsangCountSchedule!Discourse1"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Discourse studied.",
vbInformation, "ECKANKAR AREA ADMIN"
End If
If Not IsNull(Book1) Then
If Book1 = 1 Then
Me.Filter = Me.Filter & " And [Discourse] IS Null"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered for book discussions.", vbInformation,
"ECKANKAR AREA ADMIN"
ElseIf Book1 = 2 Then
Me.Filter = Me.Filter & " And [Book] IS Null"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to discourse studied.",
vbInformation, "ECKANKAR AREA ADMIN"
End If
End If

The above is executed via a button. It starts by getting all records with
"[Zone]<>Null" because the Zone field cannot be null. Zone1, Discourse1,
Area1 are unbound fields I use to filter the records.

So what is a simple way to get totals for the [Attendance] field even when I
run the various filters?

I sometimes use the following method to get totals for alternate
groupings on reports:

http://groups.google.com/group/comp.databases.ms-access/msg/af55f22d2bfed94e

The same technique should work for a filtered form as well but I have
not tried it yet.

James A. Fortune
(e-mail address removed)

 

[Guest]

Thanks James. I am not sure the link has been helpful however.

The problem boils down to this: HOW DO I USE MY FORM'S FILTER AS PARAMETERS
IN A QUERY? I do not know what the form filter will be at run-time because
the form has been designed with many filters that a user can pick from or
even combine. The ensuing filter would then involve several fields. How do I
design a query to capture such parameters ar run-time?

 

[Guest]

HOW STUPID CAN ONE BE! I forgot I could include the calculated field in a
query the form will be based on, to start with.

 

[Guest]

Thanx Chela,

I am indeed happy to see your post. Yes, I am an ECKist, and I am using
Access to develop a package to manage our activities at the local level. I
guess the language you are referring to is VB for Access. As an amateur
programmer (I am a full-time medic), there are a lot of problems I encounter.
The package is coming out nicely though, in spite of my huge handicap.

The idea is that the whole package will someday (hopefully soon) be deployed
on the web so that all those in Nigeria who manage local activities can have
a centralized database to work with. Therefore, migrating it to SQL is a
must. I am sure your help will be of immense benefit.

I appreciate you reaching me through (e-mail address removed)

 

[James A. Fortune]

Thanks James. I am not sure the link has been helpful however.

The problem boils down to this: HOW DO I USE MY FORM'S FILTER AS PARAMETERS
IN A QUERY? I do not know what the form filter will be at run-time because
the form has been designed with many filters that a user can pick from or
even combine. The ensuing filter would then involve several fields. How do I
design a query to capture such parameters ar run-time?

The point of my answer was that the technique shown does not have to be
connected with the filter unless you want it to. You can grab the
filter used for the form by using Me.Filter in the ControlSource:

=GetValue("SELECT Sum(Nz([Hrs])) AS theSum FROM tblHours WHERE " &
Me.Filter & ";", "theSum")

should sum all the [Hrs] selected by your filter.

But the real value of this technique, IMO, is when you need to do
something with records that have been filtered out or grouped differently.

For example, I filter some records to a certain week but would like to
see the totals for the previous week. Or on a report I have things
grouped by customer and InvoiceDate, but the boss needs to see a total
of their unpaid invoice amount without eliminating the paid ones.

Since your filter can get complex, if you need the individual parameters
I suggest reading them from the form the user uses to select them from
rather than parsing out Me.Filter.

I hope this helps.

James A. Fortune
(e-mail address removed)