Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

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.
0
DrDamnit
Asked:
DrDamnit
  • 3
  • 3
  • 2
1 Solution
 
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
 
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
 
BALMUKUND KESHAVCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now