Query in Form Column

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.
LVL 32
DrDamnitAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BALMUKUND KESHAVConnect With a Mentor Commented:
Do it like this :

Select Create form using wizard and select Accounts Table, then select ID,Company,contact, email field and finish the wizard.
Now open this form in Design View, Selet Sub form /Sub report option from toolbox which is available under View Menu, Select this option and drag on Detail section of the opened form.
Another wizard will be opened, here select your Account Credit Table, and Select the Desired field and a relation/link option will also be available here in which you will have to select your both the tables and common fields like ID from both the table and set a relation, and finish the form.
Again go to design view and drag a text box in footer of the sub form and right click on this text box and select property here, now click on data and in data control source type =sum(amount)
Your form is now ready, try to run this form.

Bm Keshav



0
 
Jeffrey CoachmanMIS LiasonCommented:
SELECT SUM([Credit Amount]) AS total_credits FROM [account credits] WHERE customer = X;

0
 
DrDamnitAuthor Commented:
How do I make "X" the current record?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jeffrey CoachmanMIS LiasonCommented:
DrDamnit,

<That, of course, throws an error. >
"Of course"?
Shall the myterious "Error" forever be a mystery, or can you tell us what it is?


Try this to:
SELECT [Account Credits].Customer, Sum([Account Credits].[Credit Amount]) AS SumOfCreditAmt
FROM Customers INNER JOIN [Account Credits] ON Customers.ID = [Account Credits].Customer
GROUP BY [Account Credits].[Credit Amount]
WHERE [Account Credits].Customer = [Company];

Since you never actually posted a simple example of exactly what you are looking for it is difficult to be sure....


JeffCoachman
0
 
BALMUKUND KESHAVCommented:
In After Update property of Customer list put the following :
Lets assume your Customer list box has name = lstCustomer

SELECT SUM([Credit Amount]) AS total_credits FROM [account credits] WHERE customer = lstCustomer;

You must bind the above query to any text box in your form and refresh the form after this query like
yourformname.refresh

Bm Keshav
0
 
Jeffrey CoachmanMIS LiasonCommented:
Again, I can only presume if you are asking for the current record, that you are using a form.

In Access SQL:
SELECT SUM([Credit Amount]) AS total_credits FROM [account credits] WHERE YourUniqueField=Forms!YourForm!YourUniqueField

Or

SELECT [Account Credits].Customer, Sum([Account Credits].[Credit Amount]) AS SumOfCreditAmt
FROM Customers INNER JOIN [Account Credits] ON Customers.ID = [Account Credits].Customer
GROUP BY [Account Credits].[Credit Amount]
HAVING YourUniqueField=Forms!YourForm!YourUniqueField;

The Criteria in a Group By query is "HAVING" not "WHERE"

JeffCoachman
0
 
DrDamnitAuthor Commented:
I must be making this too hard.....

I have slimmed down the database to the bare minimum tables to wrap my brain around this.

Here's the goal:

1. I need to record when someone pays money for service. This goes in the [Account Credits] table.
2. I need to link the [Credit Accounts] table to the [Accounts] table so I know whose money that was.

When I open up the Accounts form, I want to see the account information + a sum of all the credits that account has.

So the table might look like what is in the code snippet...

How do I make the account report form with the summation of the credits?


[Accounts]
ID            Company       Contact         Email
7             Acme          Joe Smith       jsmith@acme.com
 
[Account Credits]
ID          Company         Amount          Date
13          7               825.00          3/1/2009
14          7               825.00          4/1/2009
 
[Account Report Form]
Company      Contact          Email                    Available Credit
Acme         Joe Smith        jsmith@acme.com          $1,650.00

Open in new window

0
 
DrDamnitAuthor Commented:
Keshav: Your solution pointed me in the right direction. What I actually want is reports and sub reports, and I still have some figuring out to do, but thank you for your help!
0
All Courses

From novice to tech pro — start learning today.