Solved

Query in Form Column

Posted on 2009-05-19
8
574 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 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