Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query in Form Column

Posted on 2009-05-19
8
Medium Priority
?
586 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

824 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