• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

QReports and SQL Question

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
nzboss
Asked:
nzboss
  • 5
  • 4
  • 4
1 Solution
 
esoftbgCommented:
Could you send a zipped database at:
esoftbg_e_e@yahoo.com
please ?
0
 
nzbossAuthor Commented:
Sure, will send it soon.
0
 
esoftbgCommented:
I will be done in 30 minutes ....
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
geobulCommented:
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
 
geobulCommented:
AND Transactions.Trans_Description = 'Interest'

above could be as you typed it, of course:

AND Trans_Description like '%Interest%'

Regards, Geo
0
 
nzbossAuthor Commented:
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
 
esoftbgCommented:
download a working example from :
page:        http://www.geocities.com/esoftbg/
  link:        Q_21093506.zip
0
 
geobulCommented:
OK. Try:

AND Transactions.Trans_Description like  '*Interest*'

instead.
0
 
nzbossAuthor Commented:
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
 
nzbossAuthor Commented:
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
 
esoftbgCommented:
I did improve the code,
Please download the example again ....
I think it is almost you want.
0
 
nzbossAuthor Commented:
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
 
geobulCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now