Query in Form Column
Posted on 2009-05-19
I have downloaded the Time and Billing template for Access 2007, and added a table called "Account Credits."
The table has four columns: id (autonumber) customer (lookup to the customer in the customers table) Credit Amount (currency) and credit date (date / time).
When I click on the Customer List, I want to add a column to the display columns that shows the total amount of credits that customer has. This is basically a simple summation of all the credit amount records in the account credits table for the given customer, but I don't know how to do this.
The best I could come up with is this
SELECT [Account Credits].[Credit Amount]
FROM Customers INNER JOIN [Account Credits] ON Customers.ID = [Account Credits].Customer
GROUP BY [Account Credits].[Credit Amount]
WHERE [Account Credits].Customer = [Company]; <-- company is the lookup to the customer in the customer's table.
That, of course, throws an error.
Note: In MySQL I would use something like this query:
SELECT SUM(`credit amount`) as total_credits FROM `account credits` WHERE `customer` = X;
then I would pass the current customer's customer number (id / primary key for the customer we are looking at) to substitute as X.