Calculating Sum of limited data set

[Bob Quintal]

I have two tables: Measure and Score. The measure table is
the parent table containing details for various performance
tracking. The Score table contains the monthly scores for the
respective measure.

I've uploaded some screenshots to help with my inquiry:

http://www.flickr.com/photos/9731133 @N07/sets/72157600672829561
/

I need two separate controls for a Dsum of the numerator and
denominator for each individual measure which I can then
ultimately use to calculate a performance rate.

My current Dsum calculation (which I know is wrong) uses the
entire Score table as the domain which performs the
calculation on the entire data set and not just the data set
associated with that particular measure.

I thought something like this would work to limit the data set
but my logic is in err I believe:
=DSum("numerator","score","[measure_ID]='Me![measure_ID]'")

try
DSum("numerator","score","[measure_ID]=" & Me![measure_ID]& ")"

My next thought was to build a new query which sets criteria
as [Forms]! [formname]![ID], then create a form based on that
query and then pass that value to my main form. This
certainly seems like a roundabout way to do what I expect is a
fairly simple task. Since by my way of thinking, it would
require a macro to open and close this supplemental form while
navigating through each record.

I understand some very basic VBA and wouldn't mind trying to
tackle this by hard-coding a routine. I'm not sure I
understand how to do that with a control though, if I am able
to produce a value from a calculation, how do I assign that
value from the code to the control. I hope this makes
sense...I haven't programmed since the late 80's and am
learning as I go on a new job. I do have formal training in
database design..but that's a few years ago as well.

All you need to do is assign the results of the function to the
control in the current event of the form
Me!txtNumerator = Dsum(......)

I have some pretty good abilities in Access but have found
myself limited by my lack of knowledge on how to pass values
around between queries and forms. I'm completely confused
about the syntax and use of the Me statement. I've seen
examples used with the exclamation point and other times with
not. As best I understand...the purpose of using Me is to
make it easy to send a value from the active form to another
form or query? Is using [Me]! [controlname] the same as doing
[Forms]![FormName]![controlname].

me. is a very convenient way to refer to the object currently
executing class module., be that a form, a report...

It essentially is just a shortcut to the name of whatever object
you are currently in, so that it does return the same thing as
[Forms]![FormName]![controlname]. as long as youre in the code
module of FormName

the use of the dot(.) or bang (!) is theoretically predicated
upon whether you are referring to a property of an object or a
member of a collection of like objects.

So Forms is a collection (all open forms)
FormName is a collection of all the controls on that form.

Where it gets confusing is that [FormName].[controlname] refers
to a custom property of the form, a property which also happens
to be an object in the same form's controls collection.

Essentially, if what you want to refer to was named by
Microsoft, it's a property and needs the dot, if you named it,
use the bang. But most of the time, the dot will work.

Once this particular challenge is tackled, I'm interested in
conditionally formatting the individual points on the graph
based on value. For example, (using a column graph) if a
point lies above a targeted value, the bar would be color A
and if it lies below the target it would be color B. I'm not
sure about the level of sophistication/complication of such a
task, but if someone could point me in the right direction for
that, I'd be very appreciative.

Sorry, I export my data to Excel for graphing.

 

[linusb212]

I have two tables: Measure and Score. The measure table is the parent table
containing details for various performance tracking. The Score table
contains the monthly scores for the respective measure.

I've uploaded some screenshots to help with my inquiry:

http://www.flickr.com/photos/9731133@N07/sets/72157600672829561/

I need two separate controls for a Dsum of the numerator and denominator for
each individual measure which I can then ultimately use to calculate a
performance rate.

My current Dsum calculation (which I know is wrong) uses the entire Score
table as the domain which performs the calculation on the entire data set and
not just the data set associated with that particular measure.

I thought something like this would work to limit the data set but my logic
is in err I believe:
=DSum("numerator","score","[measure_ID]='Me![measure_ID]'")

My next thought was to build a new query which sets criteria as [Forms]!
[formname]![ID], then create a form based on that query and then pass that
value to my main form. This certainly seems like a roundabout way to do what
I expect is a fairly simple task. Since by my way of thinking, it would
require a macro to open and close this supplemental form while navigating
through each record.

I understand some very basic VBA and wouldn't mind trying to tackle this by
hard-coding a routine. I'm not sure I understand how to do that with a
control though, if I am able to produce a value from a calculation, how do I
assign that value from the code to the control. I hope this makes sense...I
haven't programmed since the late 80's and am learning as I go on a new job.
I do have formal training in database design..but that's a few years ago as
well.

I have some pretty good abilities in Access but have found myself limited by
my lack of knowledge on how to pass values around between queries and forms.
I'm completely confused about the syntax and use of the Me statement. I've
seen examples used with the exclamation point and other times with not. As
best I understand...the purpose of using Me is to make it easy to send a
value from the active form to another form or query? Is using [Me]!
[controlname] the same as doing [Forms]![FormName]![controlname].

Once this particular challenge is tackled, I'm interested in conditionally
formatting the individual points on the graph based on value. For example,
(using a column graph) if a point lies above a targeted value, the bar would
be color A and if it lies below the target it would be color B. I'm not sure
about the level of sophistication/complication of such a task, but if someone
could point me in the right direction for that, I'd be very appreciative.

 

[linusb212 via AccessMonster.com]

Bob,

I really appreciate your message. It was very helpful. For whatever reason,
I wasn't able to include the parameters directly into the DSUM statement but
instead had to include the parameters in a variable for it to work like so:

Dim stLinkCriteria As String

stLinkCriteria = "[measure_ID]=" & Me![ID]
cumulative_numerator = ("numerator","score",stLinkCriteria)

(I'm recalling this from memory and can't remember if that exact formatting
is correct in the last line. I had to play with using both double and single
quotes around the stLinkCriteria but don't remember which finally worked.)

I have two tables: Measure and Score. The measure table is
the parent table containing details for various performance

[quoted text clipped - 18 lines]

but my logic is in err I believe:
=DSum("numerator","score","[measure_ID]='Me![measure_ID]'")

try
DSum("numerator","score","[measure_ID]=" & Me![measure_ID]& ")"

My next thought was to build a new query which sets criteria
as [Forms]! [formname]![ID], then create a form based on that

[quoted text clipped - 12 lines]

learning as I go on a new job. I do have formal training in
database design..but that's a few years ago as well.

All you need to do is assign the results of the function to the
control in the current event of the form
Me!txtNumerator = Dsum(......)

I have some pretty good abilities in Access but have found
myself limited by my lack of knowledge on how to pass values

[quoted text clipped - 5 lines]

form or query? Is using [Me]! [controlname] the same as doing
[Forms]![FormName]![controlname].

me. is a very convenient way to refer to the object currently
executing class module., be that a form, a report...

It essentially is just a shortcut to the name of whatever object
you are currently in, so that it does return the same thing as
[Forms]![FormName]![controlname]. as long as youre in the code
module of FormName

the use of the dot(.) or bang (!) is theoretically predicated
upon whether you are referring to a property of an object or a
member of a collection of like objects.

So Forms is a collection (all open forms)
FormName is a collection of all the controls on that form.

Where it gets confusing is that [FormName].[controlname] refers
to a custom property of the form, a property which also happens
to be an object in the same form's controls collection.

Essentially, if what you want to refer to was named by
Microsoft, it's a property and needs the dot, if you named it,
use the bang. But most of the time, the dot will work.

Once this particular challenge is tackled, I'm interested in
conditionally formatting the individual points on the graph

[quoted text clipped - 4 lines]

task, but if someone could point me in the right direction for
that, I'd be very appreciative.

Sorry, I export my data to Excel for graphing.

 

[Bob Quintal]

Bob,

I really appreciate your message. It was very helpful. For
whatever reason, I wasn't able to include the parameters
directly into the DSUM statement but instead had to include
the parameters in a variable for it to work like so:

Dim stLinkCriteria As String

stLinkCriteria = "[measure_ID]=" & Me![ID]
cumulative_numerator =
("numerator","score",stLinkCriteria)

(I'm recalling this from memory and can't remember if that
exact formatting is correct in the last line. I had to play
with using both double and single quotes around the
stLinkCriteria but don't remember which finally worked.)

Glad you got it working.

I have two tables: Measure and Score. The measure table is
the parent table containing details for various performance

[quoted text clipped - 18 lines]

but my logic is in err I believe:
=DSum("numerator","score","[measure_ID]='Me![measure_ID]'")

try
DSum("numerator","score","[measure_ID]=" & Me![measure_ID]&
")"

My next thought was to build a new query which sets criteria
as [Forms]! [formname]![ID], then create a form based on
that

[quoted text clipped - 12 lines]

learning as I go on a new job. I do have formal training in
database design..but that's a few years ago as well.

All you need to do is assign the results of the function to
the control in the current event of the form
Me!txtNumerator = Dsum(......)

I have some pretty good abilities in Access but have found
myself limited by my lack of knowledge on how to pass values

[quoted text clipped - 5 lines]

form or query? Is using [Me]! [controlname] the same as
doing [Forms]![FormName]![controlname].

me. is a very convenient way to refer to the object currently
executing class module., be that a form, a report...

It essentially is just a shortcut to the name of whatever
object you are currently in, so that it does return the same
thing as [Forms]![FormName]![controlname]. as long as youre in
the code module of FormName

the use of the dot(.) or bang (!) is theoretically predicated
upon whether you are referring to a property of an object or a
member of a collection of like objects.

So Forms is a collection (all open forms)
FormName is a collection of all the controls on that form.

Where it gets confusing is that [FormName].[controlname]
refers to a custom property of the form, a property which also
happens to be an object in the same form's controls
collection.

Essentially, if what you want to refer to was named by
Microsoft, it's a property and needs the dot, if you named it,
use the bang. But most of the time, the dot will work.

Once this particular challenge is tackled, I'm interested in
conditionally formatting the individual points on the graph

[quoted text clipped - 4 lines]

task, but if someone could point me in the right direction
for that, I'd be very appreciative.

Sorry, I export my data to Excel for graphing.

 

[linusb212 via AccessMonster.com]

With this same db structure, I want to make a summary report showing the
measure name with a few associated fields and the related scores of the last
2 months which are part of the child table. I don't want to use a report
with groupings because I'm trying to get all of the data for each record
across one row. Something like:

Measure Name Previous Month Score This Month Score
Cumulative Score Change
Measure #1 80% 90%
88% Up
Measure #2 95% 84%
90% Down

Bob,

[quoted text clipped - 13 lines]

with using both double and single quotes around the
stLinkCriteria but don't remember which finally worked.)

Glad you got it working.

I have two tables: Measure and Score. The measure table is
the parent table containing details for various performance [quoted text clipped - 54 lines]

Sorry, I export my data to Excel for graphing.

 

[Bob Quintal]

I would build a crosstab query that groups by Measure name, as row
heading and Year(resultdate)*1000+month(resultdate) & " Score" as
column heading, sums or averages Score as the value, with a where
clause to limit the results to resultdate > dateadd("m",-3 date())

Note that that will result in "200705 score" and "200706 Score" as
headers, but that's a lot simpler than the code to return
"previous" and "Current" as headers. It also sorts correctly, and
is expandable to 3 or 6 months by changing one value.

Q

With this same db structure, I want to make a summary report
showing the measure name with a few associated fields and the
related scores of the last 2 months which are part of the
child table. I don't want to use a report with groupings
because I'm trying to get all of the data for each record
across one row. Something like:

Measure Name Previous Month Score This Month
Score Cumulative Score Change
Measure #1 80%
90% 88% Up
Measure #2 95%
84% 90% Down

Bob,

[quoted text clipped - 13 lines]

with using both double and single quotes around the
stLinkCriteria but don't remember which finally worked.)

Glad you got it working.

I have two tables: Measure and Score. The measure table
is the parent table containing details for various
performance

[quoted text clipped - 54 lines]

Sorry, I export my data to Excel for graphing.