Using expression builder in Microsoft Access 2003?
I have a table regarding costs for trip (3 fields: cost, taxes, finance charge). I also have a second table regarding client info. (2 fields: amount already paid and amount remaining to be paid). I am trying to add the cost fields to get a total cost, then subtract the amount already paid to get what remains to be paid. Everytime I enter the expression, I get a box pop up about Entering Parameter Values. PLEASE HELP. Any ideas would be greatly appreciated!
- Anonymous1 decade agoFavorite Answer
Here are a few reasons you get prompted for parameters: You get the message about parameter if you deleted a parameter from the query design grid but not from the Query Parameters dialog box, or you renamed or misspelled a field in the query, or if a field in the query performs a calculation based on the calculated value, make sure the Show check box is selected for the calculated field in the query design grid.
If you list the exact names of your tables and fields, I could tell you how your query should be structured.
I don't really understand why you are using the expression builder or why you have a field in your 2nd table to store the amount remaining to be paid. You do not need a column to store the "amount remaining to be paid" (open balance). That is what queries are for...to add things up. You don't want to have to worry about updating that field everytime there is a payment made. Also, since more than one payment can be made you would have redundant information being stored in the open balance field.
This is how I woud calculate the open balance:
I will call your table for trip costs Table1. I will call your remaining balance table Table2. I will assume that you have an ID field in each table that will reference the client. I will call that column ClientNumber.
I will paste a query here that includes a subquery on Table2 that will sum up all payments each client has made and subtracts it from the trip info in Table1. If a client can have more than 1 trip, you really should have a trip number referenced as well or you will get incorrect results. But again, not knowing your structure I cannot be complete here...
SELECT Table1.ClientNumber, Sum([Cost]+[Taxes]+[Finance Charge]-(SELECT Sum([Paid]) FROM [Table2] WHERE [Table1].[ClientNumber] = [Table2].[ClientNumber])) AS OpenBalance
GROUP BY Table1.ClientNumber;