need help...subselect needed?

[HB]

table is transactions and has types: admin fee, deposit, and their dates. I
need a {monthly) report of those that paid deposits, but not admin fees
(renewals). The query I have now shows renewals, but also new people who
also paid admin fees. What is the query to show only those who paid deposists
but not also admin fees.

I think a subselect because they're all on the same table, or maybe a not
having??? I don't know...thats why I'm here. Here is the current query....it
also includes getting some member info. and the sum of deposists

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


Thanks.

HB.

 

[Guest]

hi
if i understand correctly....
you have
((TRANSACTION.[Transaction Type])="Deposit") AND
((TRANSACTION_1.[Transaction Type])="Admin Fee"))
change to
((TRANSACTION.[Transaction Type])="Deposit") AND

 

[HB]

Trans = Deposit gives me the "renewals" for the month, which is what I want,
only renewals
Trans1 = Admin Fee gives me their join date (which I need to show on report)

Problem is when Date of Admin fee = Date Deposit = New member, not renewal.
How do I get rid of those people?

Is that any clearer.

Thanks for your help.

hi
if i understand correctly....
you have
((TRANSACTION.[Transaction Type])="Deposit") AND
((TRANSACTION_1.[Transaction Type])="Admin Fee"))
change to
((TRANSACTION.[Transaction Type])="Deposit") AND

-----Original Message-----
table is transactions and has types: admin fee, deposit, and their dates. I
need a {monthly) report of those that paid deposits, but not admin fees
(renewals). The query I have now shows renewals, but also new people who
also paid admin fees. What is the query to show only those who paid deposists
but not also admin fees.

I think a subselect because they're all on the same table, or maybe a not
having??? I don't know...thats why I'm here. Here is the current query....it
also includes getting some member info. and the sum of deposists

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


Thanks.

HB.
.