nzboss
asked on
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_Descrip tion, Sum(Transactions.Trans_Cre dit)
FROM Accounts, Transactions
WHERE Accounts.Account_ID = Transactions.Account_ID
AND Trans_Description like '%Interest%';
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_Descrip
FROM Accounts, Transactions
WHERE Accounts.Account_ID = Transactions.Account_ID
AND Trans_Description like '%Interest%';
ASKER
Sure, will send it soon.
I will be done in 30 minutes ....
Hi,
Try this one:
SELECT Accounts.Account_Name, Transactions.Trans_Descrip tion, SUM(Transactions.Trans_Cre dit) AS Total
FROM Accounts, Transactions
WHERE Accounts.Account_ID = Transactions.Account_ID
AND Transactions.Trans_Descrip tion = 'Interest'
GROUP BY Accounts.Account_Name, Transactions.Trans_Descrip tion;
Regards, Geo
Try this one:
SELECT Accounts.Account_Name, Transactions.Trans_Descrip
FROM Accounts, Transactions
WHERE Accounts.Account_ID = Transactions.Account_ID
AND Transactions.Trans_Descrip
GROUP BY Accounts.Account_Name, Transactions.Trans_Descrip
Regards, Geo
AND Transactions.Trans_Descrip tion = 'Interest'
above could be as you typed it, of course:
AND Trans_Description like '%Interest%'
Regards, Geo
above could be as you typed it, of course:
AND Trans_Description like '%Interest%'
Regards, Geo
ASKER
Sorry Geo, not getting any results with the query below :o(
SELECT Accounts.Account_Name, Transactions.Trans_Descrip tion, SUM(Transactions.Trans_Cre dit) AS Total
FROM Accounts, Transactions
WHERE Accounts.Account_ID = Transactions.Account_ID
AND Transactions.Trans_Descrip tion like '%Interest%'
GROUP BY Accounts.Account_Name, Transactions.Trans_Descrip tion;
SELECT Accounts.Account_Name, Transactions.Trans_Descrip
FROM Accounts, Transactions
WHERE Accounts.Account_ID = Transactions.Account_ID
AND Transactions.Trans_Descrip
GROUP BY Accounts.Account_Name, Transactions.Trans_Descrip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK. Try:
AND Transactions.Trans_Descrip tion like '*Interest*'
instead.
AND Transactions.Trans_Descrip
instead.
ASKER
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.
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.
ASKER
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....
I did improve the code,
Please download the example again ....
I think it is almost you want.
Please download the example again ....
I think it is almost you want.
ASKER
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.
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.
esoftbg_e_e@yahoo.com
please ?