Solved

QReports and SQL Question

Posted on 2004-08-13
15
158 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
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

785 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