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
Ken Sheridan said:
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