Need help with this complex query...


G

Guest

though I think the solution will be easy for you pros out there. Parent
table Member, child is Transaction. transaction has types, admin fee when
they sign up and deposit, when they sign up and for each dues payment. The
query I need shows renewals for a time period, and the columns I am
displaying in the report are: Join date (trans date where type = admin fee),
total deposits (sum deposits) and deposit date (expr2:trans date = deposit).
Problem is, new members, those that paid the admin fee and a deposit in the
month are also showing up (and I only want renewals; just deposits, no admin
fees). I need to somehow say: where transdate:admin fee <> transdate:
deposit to eliminate that row.

here is the query, created in desgin view. Thanks for help and/or suggestions

SELECT MEMBER.MemberNumber, MEMBER.ZipCode, TRANSACTION.TransDate AS
DepositDate, TRANSACTION.TransAmt, [FirstName1] & " " & [LastName1] & "
" & [FirstName2] & " " & [LastName2] AS Name, Sum(TRANSACTION_2.TransAmt) AS
SumOfTransAmt
FROM ((MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID =
TRANSACTION.MemberID) INNER JOIN [TRANSACTION] AS TRANSACTION_2 ON
MEMBER.MemberID = TRANSACTION_2.MemberID) INNER JOIN [TRANSACTION] AS
TRANSACTION_1 ON MEMBER.MemberID = TRANSACTION_1.MemberID
WHERE (((TRANSACTION_2.[Transaction Type])="Deposit"))
GROUP BY MEMBER.MemberNumber, MEMBER.ZipCode, TRANSACTION.TransDate,
TRANSACTION.TransAmt, [FirstName1] & " " & [LastName1] & " " &
[FirstName2] & " " & [LastName2], TRANSACTION.[Transaction Type],
TRANSACTION_1.TransDate, TRANSACTION_1.[Transaction Type]
HAVING (((TRANSACTION.TransDate) Between [Enter Date - First day of month
mm/dd/yyyy] And [Enter Last Day of Month - mm/dd/yyyy]) AND
((TRANSACTION.[Transaction Type])="Deposit") AND
((TRANSACTION_1.TransDate)<>"DepositDate") AND ((TRANSACTION_1.[Transaction
Type])="Admin Fee"))
ORDER BY TRANSACTION.TransDate, TRANSACTION.[Transaction Type];
 
Ad

Advertisements

G

Guest

see your other post
-----Original Message-----
though I think the solution will be easy for you pros out there. Parent
table Member, child is Transaction. transaction has types, admin fee when
they sign up and deposit, when they sign up and for each dues payment. The
query I need shows renewals for a time period, and the columns I am
displaying in the report are: Join date (trans date where type = admin fee),
total deposits (sum deposits) and deposit date (expr2:trans date = deposit).
Problem is, new members, those that paid the admin fee and a deposit in the
month are also showing up (and I only want renewals; just deposits, no admin
fees). I need to somehow say: where transdate:admin fee
deposit to eliminate that row.

here is the query, created in desgin view. Thanks for help and/or suggestions

SELECT MEMBER.MemberNumber, MEMBER.ZipCode, TRANSACTION.TransDate AS
DepositDate, TRANSACTION.TransAmt, [FirstName1] & " " & [LastName1] & "
" & [FirstName2] & " " & [LastName2] AS Name, Sum (TRANSACTION_2.TransAmt) AS
SumOfTransAmt
FROM ((MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID =
TRANSACTION.MemberID) INNER JOIN [TRANSACTION] AS TRANSACTION_2 ON
MEMBER.MemberID = TRANSACTION_2.MemberID) INNER JOIN [TRANSACTION] AS
TRANSACTION_1 ON MEMBER.MemberID = TRANSACTION_1.MemberID
WHERE (((TRANSACTION_2.[Transaction Type])="Deposit"))
GROUP BY MEMBER.MemberNumber, MEMBER.ZipCode, TRANSACTION.TransDate,
TRANSACTION.TransAmt, [FirstName1] & " " & [LastName1] & " " &
[FirstName2] & " " & [LastName2], TRANSACTION. [Transaction Type],
TRANSACTION_1.TransDate, TRANSACTION_1.[Transaction Type]
HAVING (((TRANSACTION.TransDate) Between [Enter Date - First day of month
mm/dd/yyyy] And [Enter Last Day of Month - mm/dd/yyyy]) AND
((TRANSACTION.[Transaction Type])="Deposit") AND
((TRANSACTION_1.TransDate)<>"DepositDate") AND ((TRANSACTION_1.[Transaction
Type])="Admin Fee"))
ORDER BY TRANSACTION.TransDate, TRANSACTION.[Transaction Type];



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top