Solved

QReports and SQL Question

Posted on 2004-08-13
15
156 Views
Last Modified: 2010-04-05
Hi Experts, this should be an easy one.....

I have the following tables in an Access DB:

ACCOUNT TABLE
Account_ID (Join to Transactions Table)
Bank_Name
Account_Number
Account_Name
Account_Description
Account_Balance
Current_Ind

TRANSACTIONS TABLE
TranID
Account_ID (Join to Accounts Table)
Trans_Date
Trans_Description
Trans_Reference
Trans_Debit
Trans_Credit
Trans_Join_ID

What I want to do is generate a quick report that shows the sum of Trans_Credit for each account depending on the type of Trans_Decription the user selects.

For example:
If the user types in 'Interest', I want a query that will look at the Transactions table and Sum the Trans_Credit for each account where the Trans_Description is like 'Interest'

Output should look like:
Account Name      Description            Total
Account1            Interest            $50.00
Account2            Interest            $12.67
Account3            Interest            $79.20
...

I started it off like below but I think I am WAY off  :o(

SELECT Accounts.Account_Name, Transactions.Trans_Description, Sum(Transactions.Trans_Credit)
FROM Accounts, Transactions
WHERE Accounts.Account_ID = Transactions.Account_ID
AND Trans_Description like '%Interest%';
0
Comment
Question by:nzboss
  • 5
  • 4
  • 4
15 Comments
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
Could you send a zipped database at:
esoftbg_e_e@yahoo.com
please ?
0
 

Author Comment

by:nzboss
Comment Utility
Sure, will send it soon.
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
I will be done in 30 minutes ....
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
Hi,

Try this one:

SELECT Accounts.Account_Name, Transactions.Trans_Description, SUM(Transactions.Trans_Credit) AS Total
FROM Accounts, Transactions
WHERE Accounts.Account_ID = Transactions.Account_ID
AND Transactions.Trans_Description = 'Interest'
GROUP BY Accounts.Account_Name, Transactions.Trans_Description;

Regards, Geo
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
AND Transactions.Trans_Description = 'Interest'

above could be as you typed it, of course:

AND Trans_Description like '%Interest%'

Regards, Geo
0
 

Author Comment

by:nzboss
Comment Utility
Sorry Geo, not getting any results with the query below :o(

SELECT Accounts.Account_Name, Transactions.Trans_Description, SUM(Transactions.Trans_Credit) AS Total
FROM Accounts, Transactions
WHERE Accounts.Account_ID = Transactions.Account_ID
AND Transactions.Trans_Description like  '%Interest%'
GROUP BY Accounts.Account_Name, Transactions.Trans_Description;
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 12

Accepted Solution

by:
esoftbg earned 250 total points
Comment Utility
download a working example from :
page:        http://www.geocities.com/esoftbg/
  link:        Q_21093506.zip
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
OK. Try:

AND Transactions.Trans_Description like  '*Interest*'

instead.
0
 

Author Comment

by:nzboss
Comment Utility
Sorry esoftbg, not what I was wanting again unfortunately.....

I need to go through all the account and calculate the interest on ALL accounts, not just one....

Output should look like:
Account Name     Description          Total
Account1          Gross Interest        $50.00
Account1          Bonus Interest       $10.00
                                 TOTAL        $60.00
Account2          Interest                 $12.67
                                 TOTAL        $12.67
Account3          Interest                 $79.20
                                 TOTAL        $79.20
...

Your one calculates the total interest over all accounts.
0
 

Author Comment

by:nzboss
Comment Utility
Hi Geo, Great minds think alike, I just replaces with * and worked fine and then got your notice!  :o)  Let me play around with this a bit more....
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
I did improve the code,
Please download the example again ....
I think it is almost you want.
0
 

Author Comment

by:nzboss
Comment Utility
Hey sorry for the delay, I haven't really looked at this solution, life has been hecket and taken me away from programming for a while.  I appriciate your help esoftbg and have downloaded you solution.  I will offer you the points as it has some ideas that I can work off but it isn't a total working answer, hopefully it is enough to work with.  May post more questions when I get back into it.
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
Amazing ignorance of my efforts. Who actually helped you to solve the problem? Without using GROUP clause your sql statement would have never produced what you were after.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now