Updating and Creating Records

[Guest]

I have two tables as follows:

Client Balances
------------------
Contains.... Division ID; Client ID; Current Balance

Monthly Transactions
-------------------------
Contains..... Division ID; Client ID; Transaction Amount

The Monthly Transactions table may have more than one transaction per client.
Also some transactions relate to existing clients but others are new ie.
there is not a matching Client Balance record.

I need to

a. update the balances on the matching Client Balances records with data
from the Monthly Transaction records

b. create a new Client Balance record where one does not exist, using the
data from the Monthly Transactions record

Could you please describe the queries I need to carry out the above processes?


Thanks in anticipation.

 

[Guest]

First of all I will warn you from experience that a structure like you have
can be a headache if you ever need to correct any transactions after updating
the balances. I would definitely keep an archive of both tables from before
you try to update and append them so you can trace problems back - otherwise
once you merge them it can be hard to trace the sources of any errors or to
get the balances to reconcile if the transactions ever get edited. But the
process is outlined below:

The update:
First build a query from your Monthly Transactions table. Press the Totals
button on the toolbar to make it a totals query. For Division ID and Client
ID set the Total line to "Group By"; for Transaction amount make it Sum.
This will sum all the transactions by client and gives you the amounts to add
to the balances.

Now start a new query, and from the Query menu make it an Update Query.
Then join your first totals query to the Client Balances by both Division ID
and Client ID. From the Query menu make it an Update Query. In the grid
take the Current Balance and under "Update To" make it equal to [Current
Balance]+[SumOfTransactionAmount] (assuming that is how the field name
appears from your totals query) - this part updates the existing records
(where there is a join) so that the current balance will equal the current
balance plus the sum of the new transactions.

Finally make a third query. Start with your Totals query and join in the
Client Balances by both Division ID and Client ID. Right click on the join
to set the Join Properties to say "Include ALL records from Totals Query (or
whatever) and only those records from Client Balances where the joined fields
are equal." In the grid put the Division ID, Client ID, and SumOfTransaction
Amount from your totals query (important! not from Client Balances), then
add in Division ID or Client ID from your Client Balances. Now, if there is
no current client the information from Client Balances will be Null, so set
the criteria condition to specify that the field you have chosen from Client
Balances is Null. This makes sure you only select the new clients, so then
make it an Append query and append the fields from your totals query to
Client Balances, with the SumOfTransaction Amount becoming the new Current
Balance.

 

[Guest]

This worked fine.
Thanks for your help.

First of all I will warn you from experience that a structure like you have
can be a headache if you ever need to correct any transactions after updating
the balances. I would definitely keep an archive of both tables from before
you try to update and append them so you can trace problems back - otherwise
once you merge them it can be hard to trace the sources of any errors or to
get the balances to reconcile if the transactions ever get edited. But the
process is outlined below:

The update:
First build a query from your Monthly Transactions table. Press the Totals
button on the toolbar to make it a totals query. For Division ID and Client
ID set the Total line to "Group By"; for Transaction amount make it Sum.
This will sum all the transactions by client and gives you the amounts to add
to the balances.

Now start a new query, and from the Query menu make it an Update Query.
Then join your first totals query to the Client Balances by both Division ID
and Client ID. From the Query menu make it an Update Query. In the grid
take the Current Balance and under "Update To" make it equal to [Current
Balance]+[SumOfTransactionAmount] (assuming that is how the field name
appears from your totals query) - this part updates the existing records
(where there is a join) so that the current balance will equal the current
balance plus the sum of the new transactions.

Finally make a third query. Start with your Totals query and join in the
Client Balances by both Division ID and Client ID. Right click on the join
to set the Join Properties to say "Include ALL records from Totals Query (or
whatever) and only those records from Client Balances where the joined fields
are equal." In the grid put the Division ID, Client ID, and SumOfTransaction
Amount from your totals query (important! not from Client Balances), then
add in Division ID or Client ID from your Client Balances. Now, if there is
no current client the information from Client Balances will be Null, so set
the criteria condition to specify that the field you have chosen from Client
Balances is Null. This makes sure you only select the new clients, so then
make it an Append query and append the fields from your totals query to
Client Balances, with the SumOfTransaction Amount becoming the new Current
Balance.
--
- K Dales

I have two tables as follows:

Client Balances
------------------
Contains.... Division ID; Client ID; Current Balance

Monthly Transactions
-------------------------
Contains..... Division ID; Client ID; Transaction Amount

The Monthly Transactions table may have more than one transaction per client.
Also some transactions relate to existing clients but others are new ie.
there is not a matching Client Balance record.

I need to

a. update the balances on the matching Client Balances records with data
from the Monthly Transaction records

b. create a new Client Balance record where one does not exist, using the
data from the Monthly Transactions record

Could you please describe the queries I need to carry out the above processes?


Thanks in anticipation.