Adding up to a problem problem

[Chris Ratcliffe]

I have a problem with an inherited database and need a quick fix.

The DB is a customer tracking system for donors to a not for profit. Each
year end the database is rolled and some information from "tblPeople" is
either stored to history or zeroed out.

"tblContributionHistory" contains three fields PersonID, Date, Amount
(eg)
PersonID Date Amount
-123 11/11/02 $50.00
-123 11/11/01 $30.00
1 11/11/02 $20.00
1 11/11/01 $50.00
1 11/10/00 $350.00
2 12/10/02 $20.00
2 12/11/01 $10.00

In the current year, the LifeTotal for a person is displayed in a field in
the donor's personal record in "tblPeople".

My problem is that at some point the database has failed to update the
LifeTotal field for some donors and has left the field blank.

I have a form "frmPerson" to view/edit individual records. Is there a
"simple" coding (or other) solution I could use to check the history table,
add up previous contributions (Amount) for each donor's PersonID and put
this amount into the "LifeTotal" field in "tblPeople" for that donor.

It has been some time since I did any VBA and I'd be greatful for any help

Chris Ratcliffe

 

[Kelvin]

See below:

I have a problem with an inherited database and need a quick fix.

The DB is a customer tracking system for donors to a not for profit. Each
year end the database is rolled and some information from "tblPeople" is
either stored to history or zeroed out.

It is a bad idea to remove data. It would be better to archive it to
another table in another database.

"tblContributionHistory" contains three fields PersonID, Date, Amount
(eg)
PersonID Date Amount
-123 11/11/02 $50.00
-123 11/11/01 $30.00
1 11/11/02 $20.00
1 11/11/01 $50.00
1 11/10/00 $350.00
2 12/10/02 $20.00
2 12/11/01 $10.00

In the current year, the LifeTotal for a person is displayed in a field in
the donor's personal record in "tblPeople".

My problem is that at some point the database has failed to update the
LifeTotal field for some donors and has left the field blank.

I have a form "frmPerson" to view/edit individual records. Is there a
"simple" coding (or other) solution I could use to check the history table,
add up previous contributions (Amount) for each donor's PersonID and put
this amount into the "LifeTotal" field in "tblPeople" for that donor.

A quick fix would be to update LifeTotal for everyone. Take the history
table and the current table in a union query then repopulate tblPeople with
the sum from the 2 tables using an update qeury.

The long fix would be to add a sum to the footer of your form and a field to
lookup the toal from tblPeople using DLookup. Add a button to the form that
will update the value in tblPeople with the sum shown on the form for that
specific person only.

Or create a main form linked to tblPeople to show the PersonID and
LifeTotal. Add frmPerson as a subform to this form. Add a sum to the
footer of the subform. Add a button with code to copy the sum in the footer
to the LifeTotal field on the main form.

It has been some time since I did any VBA and I'd be greatful for any help

Chris Ratcliffe

Kelvin

 

[Chris Ratcliffe]

Thanks for the input Kelvin,

The quick fix to update LifeTotal for everyone is the way I'd like to treat
it. However, I don't think I explained it very well. The LifeTotal
represents the ammount given prior to the current year (I know it sounds
strange but that's how whoever originally designed it wrote it....)

So, I understand from what you say that, in effect, I need to add up the
History for each PersonID and put this into the LifeTotal field in
tblPeople. I assume I can do this by writing a query that adds all the
amounts for each PersonID in the history table and stores them in a new
table showing PersonID and a TotalAmount. Then i can link the LifeTotal in
tblPeople to this total amount.

My blind spot is in writing the expression to work out the total amount for
each PersonID in the history table. I know it can't be that difficult but I
just can't see it...

Any suggestions?

Chris

Is there a website that gives a dictionary of "commonly used expressions"
for occassions like this?

 

[Kelvin]

So you want to add up the amounts then add this total to the number already
in LifeTotal. That's pretty easy. First you will need to add the names
from tblHistory to tblPeople just incase some names are not there.

Create a query of tblHistory. Include tblPeople and link PersonID betwen
the 2 tables. Double clik on the link and set to Show all records from
tblHistory. Set the query to grouping. Include PersonID from tblHistory in
the query and set to Group By. Also include PersonID from tblPeople, turn
show off, and set the criteria to Is Null. Set the query to an append
query. Append to tblPeople. Run the query adn this aprt is done.

Now to update the totals.

Create a query of tblHistory ans set to group by PersonID and sum Amounts
(I'll call this query qryTotal). Create an update query of tblPeople.
Include qrytotal and link PersonID from the 2 tables. Select LifeTotal from
tblPeople to be in the query. Set the Update To to
[LifeTotal]+[SumOfAmounts]. Run this query and your done.

Kelvin

 

[Chris Ratcliffe]

Thanks again for the input, I'm going to go and give it try.

So you want to add up the amounts then add this total to the number already
in LifeTotal. That's pretty easy. First you will need to add the names
from tblHistory to tblPeople just incase some names are not there.

Create a query of tblHistory. Include tblPeople and link PersonID betwen
the 2 tables. Double clik on the link and set to Show all records from
tblHistory. Set the query to grouping. Include PersonID from tblHistory in
the query and set to Group By. Also include PersonID from tblPeople, turn
show off, and set the criteria to Is Null. Set the query to an append
query. Append to tblPeople. Run the query adn this aprt is done.

Now to update the totals.

Create a query of tblHistory ans set to group by PersonID and sum Amounts
(I'll call this query qryTotal). Create an update query of tblPeople.
Include qrytotal and link PersonID from the 2 tables. Select LifeTotal from
tblPeople to be in the query. Set the Update To to
[LifeTotal]+[SumOfAmounts]. Run this query and your done.

Kelvin

Thanks for the input Kelvin,

The quick fix to update LifeTotal for everyone is the way I'd like to treat
it. However, I don't think I explained it very well. The LifeTotal
represents the ammount given prior to the current year (I know it sounds
strange but that's how whoever originally designed it wrote it....)

So, I understand from what you say that, in effect, I need to add up the
History for each PersonID and put this into the LifeTotal field in
tblPeople. I assume I can do this by writing a query that adds all the
amounts for each PersonID in the history table and stores them in a new
table showing PersonID and a TotalAmount. Then i can link the LifeTotal in
tblPeople to this total amount.

My blind spot is in writing the expression to work out the total amount for
each PersonID in the history table. I know it can't be that difficult

but

I
just can't see it...

Any suggestions?

Chris