DSum Issues

[brad]

I have a form that is saving data to a table. Specifically PointPoss and
PointsEarned datevalue (date work checked) and person ckd . My problem is
that i am trying to calculate the sum of pointsposs and points earned based
on the month([datevalue]) and also the ytd for both. I can't get the DSum
fuction to pull the values for each person. how do i get it to keep the DSum
for each person? Thanks

 

[John Vinson]

I have a form that is saving data to a table. Specifically PointPoss and
PointsEarned datevalue (date work checked) and person ckd . My problem is
that i am trying to calculate the sum of pointsposs and points earned based
on the month([datevalue]) and also the ytd for both. I can't get the DSum
fuction to pull the values for each person. how do i get it to keep the DSum
for each person? Thanks

By including the personID in the criteria argument (the third one) of
the DSum.

Since you chose not to post the expression you're using or any
indication of how your tables are structured, I can't be any more
specific than that; if you do post such info someone should be able to
give a more specific answer.

Note that it's generally a Bad Idea to store such calculated sums.
Recalculate them as needed instead.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Randy]

I have a form that is saving data to a table. Specifically PointPoss and
PointsEarned datevalue (date work checked) and person ckd . My problem is
that i am trying to calculate the sum of pointsposs and points earned
based
on the month([datevalue]) and also the ytd for both. I can't get the DSum
fuction to pull the values for each person. how do i get it to keep the
DSum
for each person? Thanks

Brad, It is bad design to add or store redundant data to tables (ex. Totals,
Subtotals, etc.) Those kind of fields are easily calculated on queries
and/or reports (ex. Total= price*qty). When used in Forms, they should be
used as indicators and not to save data to a table. However, since I lack of
details about your project, consider the following:

DSum("[PointPoss] + [PointsEarned]", "MyTable", "[person ckd] = '" & [person
ckd] & "' AND Format([datevalue],'mm/yyyy') = " &
format([datevalue],"mm/yyyy")

-Randy

 

[brad]

This database is for quality checking employees. The table
CSRCaseQualityTable stores info on each time a person is evaluated along with
the type of quality(the answers to each item ckd along with total possible
and earned for each date) I can get the totals for each month by using the
Totals line in the query. For the YTD info i tried the expression:

=DSum('TotalPointsPoss', 'CSRCaseQualityTable', 'QualityType = "Inquiry"')

Basically I don't know how to reference every person's name as there isn't
anything in the Table(domain) to compare it to. Thanks.

I have a form that is saving data to a table. Specifically PointPoss and
PointsEarned datevalue (date work checked) and person ckd . My problem is
that i am trying to calculate the sum of pointsposs and points earned based
on the month([datevalue]) and also the ytd for both. I can't get the DSum
fuction to pull the values for each person. how do i get it to keep the DSum
for each person? Thanks

By including the personID in the criteria argument (the third one) of
the DSum.

Since you chose not to post the expression you're using or any
indication of how your tables are structured, I can't be any more
specific than that; if you do post such info someone should be able to
give a more specific answer.

Note that it's generally a Bad Idea to store such calculated sums.
Recalculate them as needed instead.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[brad]

in the equation what does this mean? (the quotes and listing the name twice?)
"[person ckd] = '" & [person ckd] & "'
sorry still fairly new to programming.

I have a form that is saving data to a table. Specifically PointPoss and
PointsEarned datevalue (date work checked) and person ckd . My problem is
that i am trying to calculate the sum of pointsposs and points earned
based
on the month([datevalue]) and also the ytd for both. I can't get the DSum
fuction to pull the values for each person. how do i get it to keep the
DSum
for each person? Thanks

Brad, It is bad design to add or store redundant data to tables (ex. Totals,
Subtotals, etc.) Those kind of fields are easily calculated on queries
and/or reports (ex. Total= price*qty). When used in Forms, they should be
used as indicators and not to save data to a table. However, since I lack of
details about your project, consider the following:

DSum("[PointPoss] + [PointsEarned]", "MyTable", "[person ckd] = '" & [person
ckd] & "' AND Format([datevalue],'mm/yyyy') = " &
format([datevalue],"mm/yyyy")

-Randy

 

[John Vinson]

This database is for quality checking employees. The table
CSRCaseQualityTable stores info on each time a person is evaluated along with
the type of quality(the answers to each item ckd along with total possible
and earned for each date) I can get the totals for each month by using the
Totals line in the query. For the YTD info i tried the expression:

=DSum('TotalPointsPoss', 'CSRCaseQualityTable', 'QualityType = "Inquiry"')

Basically I don't know how to reference every person's name as there isn't
anything in the Table(domain) to compare it to. Thanks.

Please post some information about your table structure, then.
Remember: YOU can see your database; you know what tables you have;
you (I hope!) know where the person's name or (better) unique personal
identifier are locaed. We don't.

I *presume* that [CSRCaseQualityTable] is linked to a personnel table
in some manner, and that it contains a date field as well. The DSum
that you list will NOT collect YTD information - it will sum all
records in CSRCaseQualityTable which contain 'Inquiry' in the
QualityType field. I'm sure this can be fixed, but fixing it will
require an understanding of the data that you're working with - and I
do not have that understanding!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Randy]

in the equation what does this mean? (the quotes and listing the name
twice?)
"[person ckd] = '" & [person ckd] & "'
sorry still fairly new to programming.

It means that it will pick the current value of [person ckd].

If the field is within a text string (quotes) means static. Without the text
string means dynamic or the current field value at the moment of displaying
the form on the current record. The final SQL statement translates as:

SELECT ([PointPoss] + [PointsEarned]) As DSumResult FROM MyTable WHERE
[person ckd] = 'XXXXX' AND Format([datevalue],'mm/yyyy') = 'YYYYY'

XXXXX and YYYYY are the current values of [person ckd] and [datevalue]
respectively. Also reading back the formula I have found I missed quotations
around the final date and a closed parenthesis.

The suggested string is:

DSum("[PointPoss] + [PointsEarned]", "CSRCaseQualityTable", "[person ckd] =
'" & [person ckd] & "' AND Format([datevalue],'mm/yyyy') = '" &
format([datevalue],"mm/yyyy") & "'")

Does that work for you?

-Randy