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

x
?
Solved

QReports and SQL Question

Posted on 2004-08-13
15
Medium Priority
?
163 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview
Suggested Courses

971 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