Solved

QReports and SQL Question

Posted on 2004-08-13
15
160 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
[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
  • 5
  • 4
  • 4
15 Comments
 
LVL 12

Expert Comment

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

Author Comment

by:nzboss
ID: 11798956
Sure, will send it soon.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11799414
I will be done in 30 minutes ....
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:geobul
ID: 11799440
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
ID: 11799461
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
ID: 11799509
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
 
LVL 12

Accepted Solution

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

Expert Comment

by:geobul
ID: 11799709
OK. Try:

AND Transactions.Trans_Description like  '*Interest*'

instead.
0
 

Author Comment

by:nzboss
ID: 11799733
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
ID: 11799740
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
ID: 11800033
I did improve the code,
Please download the example again ....
I think it is almost you want.
0
 

Author Comment

by:nzboss
ID: 13368708
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
ID: 13369564
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
control image tags in a string ? 12 171
Adoquery sql  left join does not work 25 121
Firemonkey webbrowser scrollbars ? 1 73
firemonkey keyboard covers the controls 1 83
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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