Solved

Query in Form Column

Posted on 2009-05-19
8
573 Views
Last Modified: 2013-11-28
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
Comment
Question by:DrDamnit
  • 3
  • 3
  • 2
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24428565
SELECT SUM([Credit Amount]) AS total_credits FROM [account credits] WHERE customer = X;

0
 
LVL 32

Author Comment

by:DrDamnit
ID: 24428570
How do I make "X" the current record?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24428591
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 6

Expert Comment

by:BALMUKUND KESHAV
ID: 24428614
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24428616
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
 
LVL 32

Author Comment

by:DrDamnit
ID: 24433536
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
 
LVL 6

Accepted Solution

by:
BALMUKUND KESHAV earned 500 total points
ID: 24438054
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
 
LVL 32

Author Closing Comment

by:DrDamnit
ID: 31583347
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question