Need help with this complex query...

[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];

 

[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];



.