Calculation at subforms

[kul]

I've got a main form (with donors' info) and a subform (with donation
details: year, month > coz the donors donate every month, donation amount for
each month) within it.

The subform is supposed to show the donations based on a year, so that we
can track the history. I've got the subform as Single Form in Default View.
The fields there are: Year (with label and control), Months (from Jan to
Dec)> as Labels and the associated controls (from [JAN], [FEB],... 12
controls in total representing the amount donated each month) are the amount
..

I'm trying to get the total donation amount for each year to add up from Jan
to Dec. So, I've put an unbound control at the subform, with having
=Sum([JAN]+[FEB]+...+[DEC]) However, the total amount at the subform adds up
not just the current year, but also rest of the years when the donors have
donated.

Q: How can I get a calculation/total/sum for each year? (so that i can track
the history easily)

Thanks.

 

[Ken Sheridan]

As you have the monthly donations stored as separate fields in each record
you simply need to add the values; no summation is involved, so the
ControlSource would be:

=[JAN]+[FEB]+...+[DEC]

Note that each monthly donation field should have a DefaultValue property of
zero and its Required property set to True ('Yes' in the properties sheet of
the field in table design view) to prohibit Nulls. If any of the fields were
Null the result of the additions would be Null as anything + Null = Null.
You could get round this by using the Nz function to return a zero for a
Null, but generally speaking its preferable that currency fields should have
a default value of zero and prohibit Nulls as Null, not being a value, has no
real meaning, its semantically ambiguous. This can be problematical. For
instance what would a Null credit limit for a customer mean; zero credit?,
unlimited credit?, there is simply no way of knowing from the data per se.

However, having separate fields per month is not a good design. It is
what's known as 'encoding data as column headings' whereas in a relational
database data should be stored as values at column positions in rows in
tables and in no other way. The way to do this sort of thing is to have
fields such as DonorID, DonationYear, DonationMonth and DonationAmount.
There would then be separate rows per month in the Donations table. The
subform would then be in Continuous form view and can be restricted to one
year by having an unbound combo box on the main form in which a year can be
selected, perhaps defaulting to the current year. The LinkMasterFields
property of the subform control would be the name of the combo box and
DonorID (or whatever the primary key of the parent form's table is called),
e.g.

DonorID;cboYear

while the LinkChildFields property would be the relevant fields in the
Donations table:

DonorID;DonationYear

The total yearly amount would in this case involve the summation of the
DonationAmount values as it is now dealing with a set of values in a single
field:

=Sum([DonationAmount])

With the data stored in this way its easy to aggregate it in other ways of
course, e.g. the Max, Min or Avg monthly donation per donor.

Ken Sheridan
Stafford, England

I've got a main form (with donors' info) and a subform (with donation
details: year, month > coz the donors donate every month, donation amount for
each month) within it.

The subform is supposed to show the donations based on a year, so that we
can track the history. I've got the subform as Single Form in Default View.
The fields there are: Year (with label and control), Months (from Jan to
Dec)> as Labels and the associated controls (from [JAN], [FEB],... 12
controls in total representing the amount donated each month) are the amount
.

I'm trying to get the total donation amount for each year to add up from Jan
to Dec. So, I've put an unbound control at the subform, with having
=Sum([JAN]+[FEB]+...+[DEC]) However, the total amount at the subform adds up
not just the current year, but also rest of the years when the donors have
donated.

Q: How can I get a calculation/total/sum for each year? (so that i can track
the history easily)

Thanks.

 

[kul]

Hi Ken,

thanks for the suggestions..
i've got another problem in entering the data at the subform. pls help...
the question is at the bottom of my reply....

Cheers,
Kul

As you have the monthly donations stored as separate fields in each record
you simply need to add the values; no summation is involved, so the
ControlSource would be:

=[JAN]+[FEB]+...+[DEC]

Thanks very much for your help. It works !!!
But, now, how can I get the Grand Total for all the years??
I've something like =[Donation_History].Form!Total before at the mainform to
show the overall donation amount received by each donor. However, it's now
showing the amount as per year since I've changed the "formula" at the
subform.

Note that each monthly donation field should have a DefaultValue property of
zero and its Required property set to True ('Yes' in the properties sheet of
the field in table design view) to prohibit Nulls. If any of the fields were
Null the result of the additions would be Null as anything + Null = Null.
You could get round this by using the Nz function to return a zero for a
Null, but generally speaking its preferable that currency fields should have
a default value of zero and prohibit Nulls as Null, not being a value, has no
real meaning, its semantically ambiguous. This can be problematical. For
instance what would a Null credit limit for a customer mean; zero credit?,
unlimited credit?, there is simply no way of knowing from the data per se.

icccc..... I've managed to get the Default Value as "0" and the Required
Property as "Yes", and straight away all the blanks are now being set to
zero. That means I dont have to type every single zero if we dont have
anything from the donors, which is smart. However, I'm not sure how to make
the Null thing. I sort of understand what u have been trying to explain, but
sorry that I donno how to make that happen at my table.

However, having separate fields per month is not a good design. It is
what's known as 'encoding data as column headings' whereas in a relational
database data should be stored as values at column positions in rows in
tables and in no other way. The way to do this sort of thing is to have
fields such as DonorID, DonationYear, DonationMonth and DonationAmount.
There would then be separate rows per month in the Donations table. The
subform would then be in Continuous form view and can be restricted to one
year by having an unbound combo box on the main form in which a year can be
selected, perhaps defaulting to the current year. The LinkMasterFields
property of the subform control would be the name of the combo box and
DonorID (or whatever the primary key of the parent form's table is called),
e.g.

DonorID;cboYear

while the LinkChildFields property would be the relevant fields in the
Donations table:

DonorID;DonationYear

The total yearly amount would in this case involve the summation of the
DonationAmount values as it is now dealing with a set of values in a single
field:

=Sum([DonationAmount])

With the data stored in this way its easy to aggregate it in other ways of
course, e.g. the Max, Min or Avg monthly donation per donor.

I can see what u mean. In fact, i did have the fields like u've suggested at
the very beginning when I made the table. However, I had trouble in making
the form. I forget what went wrong now. But the thing is, we have been doing
this kind of records on cards for ages... And now, we are trying to get rid
of those cards and put everything into computers so as to generate whatever
kind of reports for reference easily. That's why I have the subform as in
Single Form for Default View. In fact, we have several categories of the
donors, and I'll make it in the way u've suggested. I agree that it will be
easier to store data and more diversified in reports.

At the moment, I have to make the forms (for this particular category) look
exactly the same to the cards we used to have, coz the info will be later
handled by another senior workmate who is very green to computer. In fact,
she is old but will to work w computers. So, I have to make it simple for
her.

What she needs to do is to key in the donation amounts according to the
donors and the months.

Another problem here: When I give a few trial tests in data entry at the
form, how come when I hit "Enter" at a control, it doesnt go to the next
control as "Tab" does? Rather, it goes to the next line within the same
control. After I've hit "Tab", things go normal, ie. the cursor moves to the
next control, AND, if I hit "Enter", the cursor moves to the next control as
well... I used to use Excel and I know if I hit "Enter" or "Down" button,
the data will be there at the cell. What should I do at Access in order to
have the same result?? Would it be something to do with "On Key Down"/ "On
Enter" at the Property Sheet of the Form Design View?? But I donno how to
make all those codes there.... Pls help. Thanks a million !!!!!!

 

[Ken Sheridan]

Kul:

Two things control what happens when the Enter key is pressed.

1. On the Keyboard tab of the Options dialogue (opened from the options
item on the Tools menu of the main database menu bar in my version of Access,
though it might differ in others) there are three 'Move after enter' options.
You should select 'Next field' for the behaviour you want.

2. Access text box controls have an EnterKeyBehavior property which also
determines what happens when Enter is pressed while the control has focus.
This should be set to 'Default'. The other available setting is 'New line in
Field', which is normally used with memo fields, but not with other text
fields. You'll find this property on the 'Other' tab of the control's
properties sheet.

As regards 'the Null thing' you don't need to do anything else. Setting a
field's Required property to True is all that's necessary to prevent Nulls.
By also setting the DefaultValue property the only way that a Null could be
entered would be if a user deletes the value in the field. This would
produce an error, so it would not be possible to save a record with the Null
in the 'required' field.

Finally, don't underestimate the ability of old ladies (or men!) to learn a
different way of entering data from that which they've been used to with
cards. I'd bet that if you explained to your colleague that this is the best
way for the database to handle the data she'd soon get the hang of it.

Ken Sheridan
Stafford, England

Hi Ken,

thanks for the suggestions..
i've got another problem in entering the data at the subform. pls help...
the question is at the bottom of my reply....

Cheers,
Kul

As you have the monthly donations stored as separate fields in each record
you simply need to add the values; no summation is involved, so the
ControlSource would be:

=[JAN]+[FEB]+...+[DEC]

Thanks very much for your help. It works !!!
But, now, how can I get the Grand Total for all the years??
I've something like =[Donation_History].Form!Total before at the mainform to
show the overall donation amount received by each donor. However, it's now
showing the amount as per year since I've changed the "formula" at the
subform.

Note that each monthly donation field should have a DefaultValue property of
zero and its Required property set to True ('Yes' in the properties sheet of
the field in table design view) to prohibit Nulls. If any of the fields were
Null the result of the additions would be Null as anything + Null = Null.
You could get round this by using the Nz function to return a zero for a
Null, but generally speaking its preferable that currency fields should have
a default value of zero and prohibit Nulls as Null, not being a value, has no
real meaning, its semantically ambiguous. This can be problematical. For
instance what would a Null credit limit for a customer mean; zero credit?,
unlimited credit?, there is simply no way of knowing from the data per se.

icccc..... I've managed to get the Default Value as "0" and the Required
Property as "Yes", and straight away all the blanks are now being set to
zero. That means I dont have to type every single zero if we dont have
anything from the donors, which is smart. However, I'm not sure how to make
the Null thing. I sort of understand what u have been trying to explain, but
sorry that I donno how to make that happen at my table.

However, having separate fields per month is not a good design. It is
what's known as 'encoding data as column headings' whereas in a relational
database data should be stored as values at column positions in rows in
tables and in no other way. The way to do this sort of thing is to have
fields such as DonorID, DonationYear, DonationMonth and DonationAmount.
There would then be separate rows per month in the Donations table. The
subform would then be in Continuous form view and can be restricted to one
year by having an unbound combo box on the main form in which a year can be
selected, perhaps defaulting to the current year. The LinkMasterFields
property of the subform control would be the name of the combo box and
DonorID (or whatever the primary key of the parent form's table is called),
e.g.

DonorID;cboYear

while the LinkChildFields property would be the relevant fields in the
Donations table:

DonorID;DonationYear

The total yearly amount would in this case involve the summation of the
DonationAmount values as it is now dealing with a set of values in a single
field:

=Sum([DonationAmount])

With the data stored in this way its easy to aggregate it in other ways of
course, e.g. the Max, Min or Avg monthly donation per donor.

I can see what u mean. In fact, i did have the fields like u've suggested at
the very beginning when I made the table. However, I had trouble in making
the form. I forget what went wrong now. But the thing is, we have been doing
this kind of records on cards for ages... And now, we are trying to get rid
of those cards and put everything into computers so as to generate whatever
kind of reports for reference easily. That's why I have the subform as in
Single Form for Default View. In fact, we have several categories of the
donors, and I'll make it in the way u've suggested. I agree that it will be
easier to store data and more diversified in reports.

At the moment, I have to make the forms (for this particular category) look
exactly the same to the cards we used to have, coz the info will be later
handled by another senior workmate who is very green to computer. In fact,
she is old but will to work w computers. So, I have to make it simple for
her.

What she needs to do is to key in the donation amounts according to the
donors and the months.

Another problem here: When I give a few trial tests in data entry at the
form, how come when I hit "Enter" at a control, it doesnt go to the next
control as "Tab" does? Rather, it goes to the next line within the same
control. After I've hit "Tab", things go normal, ie. the cursor moves to the
next control, AND, if I hit "Enter", the cursor moves to the next control as
well... I used to use Excel and I know if I hit "Enter" or "Down" button,
the data will be there at the cell. What should I do at Access in order to
have the same result?? Would it be something to do with "On Key Down"/ "On
Enter" at the Property Sheet of the Form Design View?? But I donno how to
make all those codes there.... Pls help. Thanks a million !!!!!!

Ken Sheridan
Stafford, England

 

[kul]

Thanks Ken,

The EnterKeyBehavior property is good.. I actually didnt notice that there's
a Default setting for that.. Maybe I've got "Next Line in Field" by accident..

However, I still cant get to the next field by hitting the down key. What
should I do?

cheers,
kul

Kul:

Two things control what happens when the Enter key is pressed.

1. On the Keyboard tab of the Options dialogue (opened from the options
item on the Tools menu of the main database menu bar in my version of Access,
though it might differ in others) there are three 'Move after enter' options.
You should select 'Next field' for the behaviour you want.

2. Access text box controls have an EnterKeyBehavior property which also
determines what happens when Enter is pressed while the control has focus.
This should be set to 'Default'. The other available setting is 'New line in
Field', which is normally used with memo fields, but not with other text
fields. You'll find this property on the 'Other' tab of the control's
properties sheet.

As regards 'the Null thing' you don't need to do anything else. Setting a
field's Required property to True is all that's necessary to prevent Nulls.
By also setting the DefaultValue property the only way that a Null could be
entered would be if a user deletes the value in the field. This would
produce an error, so it would not be possible to save a record with the Null
in the 'required' field.

Finally, don't underestimate the ability of old ladies (or men!) to learn a
different way of entering data from that which they've been used to with
cards. I'd bet that if you explained to your colleague that this is the best
way for the database to handle the data she'd soon get the hang of it.

Ken Sheridan
Stafford, England

Hi Ken,

thanks for the suggestions..
i've got another problem in entering the data at the subform. pls help...
the question is at the bottom of my reply....

Cheers,
Kul

As you have the monthly donations stored as separate fields in each record
you simply need to add the values; no summation is involved, so the
ControlSource would be:

=[JAN]+[FEB]+...+[DEC]

Thanks very much for your help. It works !!!
But, now, how can I get the Grand Total for all the years??
I've something like =[Donation_History].Form!Total before at the mainform to
show the overall donation amount received by each donor. However, it's now
showing the amount as per year since I've changed the "formula" at the
subform.

Note that each monthly donation field should have a DefaultValue property of
zero and its Required property set to True ('Yes' in the properties sheet of
the field in table design view) to prohibit Nulls. If any of the fields were
Null the result of the additions would be Null as anything + Null = Null.
You could get round this by using the Nz function to return a zero for a
Null, but generally speaking its preferable that currency fields should have
a default value of zero and prohibit Nulls as Null, not being a value, has no
real meaning, its semantically ambiguous. This can be problematical. For
instance what would a Null credit limit for a customer mean; zero credit?,
unlimited credit?, there is simply no way of knowing from the data per se.

icccc..... I've managed to get the Default Value as "0" and the Required
Property as "Yes", and straight away all the blanks are now being set to
zero. That means I dont have to type every single zero if we dont have
anything from the donors, which is smart. However, I'm not sure how to make
the Null thing. I sort of understand what u have been trying to explain, but
sorry that I donno how to make that happen at my table.

However, having separate fields per month is not a good design. It is
what's known as 'encoding data as column headings' whereas in a relational
database data should be stored as values at column positions in rows in
tables and in no other way. The way to do this sort of thing is to have
fields such as DonorID, DonationYear, DonationMonth and DonationAmount.
There would then be separate rows per month in the Donations table. The
subform would then be in Continuous form view and can be restricted to one
year by having an unbound combo box on the main form in which a year can be
selected, perhaps defaulting to the current year. The LinkMasterFields
property of the subform control would be the name of the combo box and
DonorID (or whatever the primary key of the parent form's table is called),
e.g.

DonorID;cboYear

while the LinkChildFields property would be the relevant fields in the
Donations table:

DonorID;DonationYear

The total yearly amount would in this case involve the summation of the
DonationAmount values as it is now dealing with a set of values in a single
field:

=Sum([DonationAmount])

With the data stored in this way its easy to aggregate it in other ways of
course, e.g. the Max, Min or Avg monthly donation per donor.

I can see what u mean. In fact, i did have the fields like u've suggested at
the very beginning when I made the table. However, I had trouble in making
the form. I forget what went wrong now. But the thing is, we have been doing
this kind of records on cards for ages... And now, we are trying to get rid
of those cards and put everything into computers so as to generate whatever
kind of reports for reference easily. That's why I have the subform as in
Single Form for Default View. In fact, we have several categories of the
donors, and I'll make it in the way u've suggested. I agree that it will be
easier to store data and more diversified in reports.

At the moment, I have to make the forms (for this particular category) look
exactly the same to the cards we used to have, coz the info will be later
handled by another senior workmate who is very green to computer. In fact,
she is old but will to work w computers. So, I have to make it simple for
her.

What she needs to do is to key in the donation amounts according to the
donors and the months.

Another problem here: When I give a few trial tests in data entry at the
form, how come when I hit "Enter" at a control, it doesnt go to the next
control as "Tab" does? Rather, it goes to the next line within the same
control. After I've hit "Tab", things go normal, ie. the cursor moves to the
next control, AND, if I hit "Enter", the cursor moves to the next control as
well... I used to use Excel and I know if I hit "Enter" or "Down" button,
the data will be there at the cell. What should I do at Access in order to
have the same result?? Would it be something to do with "On Key Down"/ "On
Enter" at the Property Sheet of the Form Design View?? But I donno how to
make all those codes there.... Pls help. Thanks a million !!!!!!

Ken Sheridan
Stafford, England