Please help me simplify this query

[markramos]

i have created a query that solves for the difference of the debit and credit of the accounts receivables of a certain member or person..

SELECT DISTINCT (
(SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction
FROM tblJournal
WHERE JournalEntryNumber
IN ( SELECT JournalEntryNumber
FROM tblAccountsReceivableFromMembers
WHERE MemberID = '2008-0001')
AND AccountNumber = 120
) WHERE AccountAction='Debit')
-
(SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction
FROM tblJournal
WHERE JournalEntryNumber
IN ( SELECT JournalEntryNumber
FROM tblAccountsReceivableFromMembers
WHERE MemberID = '2008-0001')
AND AccountNumber = 120
) WHERE AccountAction='Credit')
) AS ['TotalAccountsReceivables']
FROM (SELECT tblJournal.Amount, tblJournal.AccountAction
FROM tblJournal
WHERE JournalEntryNumber
IN ( SELECT JournalEntryNumber
FROM tblAccountsReceivableFromMembers
WHERE MemberID = '2008-0001')
AND AccountNumber = 120
);

i need help on this so that there would only be one (WHERE MemberID = ?) to use. thanks so much! i hope to read your replies as soon as you can!

 

[ByteMyzer]

Try:

SELECT SUM(IIF(T1.AccountAction='Debit',T1.Amount,0))
-SUM(IIF(T1.AccountAction='Credit',T1.Amount,0)) AS [TotalAccountsReceivables]
FROM tblJournal T1
INNER JOIN tblAccountsReceivableFromMembers T2 ON T1.JournalEntryNumber=T2.JournalEntryNumber
WHERE T2.MemberID = '2008-0001'
AND T1.AccountNumber = 120;


See if this works for you.

 

[markramos]

Try:

SELECT SUM(IIF(T1.AccountAction='Debit',T1.Amount,0))
-SUM(IIF(T1.AccountAction='Credit',T1.Amount,0)) AS [TotalAccountsReceivables]
FROM tblJournal T1
INNER JOIN tblAccountsReceivableFromMembers T2 ON T1.JournalEntryNumber=T2.JournalEntryNumber
WHERE T2.MemberID = '2008-0001'
AND T1.AccountNumber = 120;


See if this works for you.

it worked well. Thanks for the reply. Can i ask one more favor?
Can you explain what you did? especially on the IIFs.. thanks

 

[ByteMyzer]

I simply created a single join instance between tblJournal and tblAccountsReceivableFromMembers via the JournalEntryNumber field, and used the Sum and Iif functions to generate the two subtotals for Debit and Credit. Since Microsoft Access has no SUMIF function, as does Excel, I used the Sum function in conjunction with the Iif (Immediate-If) function to create the two subtotal figures.

If you have a table with the following entries:

Type Amount
D     1
D     2
D     3
C     5
C     7

Sum(Iif(Type='C',Amount,0)) = 12
Sum(Iif(Type='D',Amount,0)) = 6

 

[markramos]

I simply created a single join instance between tblJournal and tblAccountsReceivableFromMembers via the JournalEntryNumber field, and used the Sum and Iif functions to generate the two subtotals for Debit and Credit. Since Microsoft Access has no SUMIF function, as does Excel, I used the Sum function in conjunction with the Iif (Immediate-If) function to create the two subtotal figures.

If you have a table with the following entries:

Type Amount
D     1
D     2
D     3
C     5
C     7

Sum(Iif(Type='C',Amount,0)) = 12
Sum(Iif(Type='D',Amount,0)) = 6

Thank you very much for the information. I learned alot from that.
Our instructors did not teach us about those two functions. Such a great big help sir!
Thank you very much! Problem solved!

 

[ByteMyzer]

Problem solved, as in a homework problem? Are you saying, then, that this was for a homework assignment?

 

[chris davies]

I would have thort that more than a little complex for mere homework wouldn't you? I've just completed a computer studies degree and have not covered this topic, still not sure if I understand it completely either

 

[ByteMyzer]

I would have thort that more than a little complex for mere homework wouldn't you?

Not if the course were specifically about Microsoft Access and if the assignment had to do with queries.

I've just completed a computer studies degree and have not covered this topic, still not sure if I understand it completely either

A generic computer studies course does not extensively cover any one specific area, such as databases or Visual Basic for Applications.

 

[markramos]

Problem solved, as in a homework problem? Are you saying, then, that this was for a homework assignment?

this is not a homework problem. i just created that query i needed help with. that was for our system. an accounting system. i never ask help on other developers for assignments.