HELP WITH EXPRESSION BUILDER 2

[bryand]

I am just starting with Access97. I would like to set up a form that allows me to enter a transaction (currency) for a customer and then display a correct balance for that customer. I have two tables one with "Customer ID" "Name" "Address" and then another table with "Id" "Customer ID" "Transaction Amount" Payment Amount" and "balance". I tried to set up a form with Customer ID, Name, Transaction Amount, Payment, Balance and ID. I would like the Balance to be the sum of the Payments minus the transactions, but I want it to be a balance that relates to each user. Right now all I can get is the total balnce for all the transactions from all users minus the total payments from all users. If anyone has some advice on how to write the correct expression for this operation I would greatly appreciate it.

 

[gol4]

there are several ways to accomplish this but the easied may be for you to check into the dlookup function it would read something like

dlookup("sum([payments]-[transactions]","yourtableor query","[customerid]= me.customerid&quot

 

[Tmconsult]

if you would like the balance = (Sum of Payments) - (Sum of Transactions), you need to add some unbound text boxes. first set one text box's "control Source" =Sum([Payments]) and name it something like "paymentsum" then set another text box's "control source" =Sum([transactions]) and name it "transactionsum" lastly set the remaining text box's "control source" = [paymentsum]-[transactionsum] this should work. Control source and Name for unbound texts are found in your properties window. You may need to place these texts on a form footer. hope this helps.

 

[bryand]

Thanks guys I'll give that a try and hopefully it'll work.

 

[bryand]

gol4-I tried that formula but it is not working for me. I am using information from 2 to three different tables and I'm not sure if that affects the formula.In addition to getting the correct balance I also need to get the customer Id to associate with a specific name. I have that working but I could only do it with information from two seperate tables. If you have anymore advice I would appreciate it.

 

[bryand]

Tmconsult- I tried to do what you told me to and it worked fine, however now back in the form it will not let me enter any type of data in new record and keeps repeating the original transaction. Do you have any more help for me ?

 

[gol4]

First this all can be done by creating a query

on the QBE grid add both tables
join the 2 tables on customerid add the fields you want to see custid,name
create 2 other fields
sumpayments:Sum([payments]) and
sumtranactions:Sum([transactions)
what will be displayed will be the sum for that customer

now your forms recordsource can be set to this query

then to show balance you can in a textbox
= [sumtranactions] - [sumpayments]

you can even add the 2 fields to you form if you like.

As far as the balance field in you table. Balance is a calculated field and unless there is a need to show what it was at the time of a transaction I would just delete it.

 

[bryand]

gol4-I have never used a query before and I am not having much luck following your directions. I keep getting an error concerning the customer id that says I didn't include the specified function as part of an aggregate function. I have no idea what to do.

 

[gol4]

On the menu under view click on SQL View. This will display what you have in your query. Please copy and paste that code here. It will help me to better see what is going on. Thanks

 

[Tmconsult]

If there is only one record on a form at a time, and if the values of the payment and transaction come from a table then just have one unbound text that is the balance and set its control source = [payment] - [transaction]. Hopefully this will work.

 

[bryand]

gol4- This is what it said, I thought I got rid fot that error but then it wouldn't allow to open the query because of the same problem

SELECT Customers.[Serial#], Transactions.Name, Sum([Payment]) AS sumpayments, Sum([Transaction]) AS sumtransactions
FROM Customers INNER JOIN Transactions ON Customers.[Serial#] = Transactions.[Serial#];

 

[bryand]

Tmconsult-I am trying to set up the form so that the transactions and payments will be entered in the form and then the correct balance will be updated. Your method looked like it was going to work fine, I just don't understand why it wouldn't let me enter new data afterwards.

 

[gol4]

my apologies on the Query. The best way to do the sum is to add the fields then click on the sum button then under the fields you want to add choose sum. It will then automatically cause group by clause for you.
the query should look like

SELECT Customers.[Serial#], Transactions.Name, Sum([Payment]) AS sumpayments, Sum([Transaction]) AS sumtransactions
FROM Customers INNER JOIN Transactions ON Customers.[Serial#] = Transactions.[Serial#]
GROUP BY customers.[serial#], transactions.name;

I think with both persons offering input it may cause confussion. I will check the thread but it seems TMconsult is closer to your solution them I am. So I will step aside and let him (or her) continue.