Need help please....

[HB]

Here is the query:

SELECT Count(MEMBER.MemberNumber) AS CountOfMemberNumber,
Sum(TRANSACTION.TransAmt) AS SumOfTransAmt
FROM MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID = TRANSACTION.MemberID
WHERE (((MEMBER.EquityStatus)="FULL" Or (MEMBER.EquityStatus)="CURRENT" Or
(MEMBER.EquityStatus)="NONCURRENT") AND ((TRANSACTION.[Transaction
Type])="Deposit"));


The problem is the count, it returns the number of "deposit" rows with
matching Member number instead of the count of member number. I tried using
Distinct but got errors. How do I get the count of members, and the sum of
the TransAmt.

tia.

 

[John Vinson]

Here is the query:

SELECT Count(MEMBER.MemberNumber) AS CountOfMemberNumber,
Sum(TRANSACTION.TransAmt) AS SumOfTransAmt
FROM MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID = TRANSACTION.MemberID
WHERE (((MEMBER.EquityStatus)="FULL" Or (MEMBER.EquityStatus)="CURRENT" Or
(MEMBER.EquityStatus)="NONCURRENT") AND ((TRANSACTION.[Transaction
Type])="Deposit"));


The problem is the count, it returns the number of "deposit" rows with
matching Member number instead of the count of member number. I tried using
Distinct but got errors. How do I get the count of members, and the sum of
the TransAmt.

tia.

You'll need some nested queries:

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt] FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

This will sum each member's deposits within the subquery, return that
sum as a subtotal, and then sum those subtotals.

John W. Vinson[MVP]

 

[HB]

I get it.....thanks a lot. However, when I save they query I get an error
that a ) or ] is missing. I've spent about 10 min. trying to find it...can
you help?

Thanks again.

Here is the query:

SELECT Count(MEMBER.MemberNumber) AS CountOfMemberNumber,
Sum(TRANSACTION.TransAmt) AS SumOfTransAmt
FROM MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID = TRANSACTION.MemberID
WHERE (((MEMBER.EquityStatus)="FULL" Or (MEMBER.EquityStatus)="CURRENT" Or
(MEMBER.EquityStatus)="NONCURRENT") AND ((TRANSACTION.[Transaction
Type])="Deposit"));


The problem is the count, it returns the number of "deposit" rows with
matching Member number instead of the count of member number. I tried using
Distinct but got errors. How do I get the count of members, and the sum of
the TransAmt.

tia.

You'll need some nested queries:

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt] FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

This will sum each member's deposits within the subquery, return that
sum as a subtotal, and then sum those subtotals.

John W. Vinson[MVP]

 

[HB]

And it's not the missing ( around "deposit"

Here is the query:

SELECT Count(MEMBER.MemberNumber) AS CountOfMemberNumber,
Sum(TRANSACTION.TransAmt) AS SumOfTransAmt
FROM MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID = TRANSACTION.MemberID
WHERE (((MEMBER.EquityStatus)="FULL" Or (MEMBER.EquityStatus)="CURRENT" Or
(MEMBER.EquityStatus)="NONCURRENT") AND ((TRANSACTION.[Transaction
Type])="Deposit"));


The problem is the count, it returns the number of "deposit" rows with
matching Member number instead of the count of member number. I tried using
Distinct but got errors. How do I get the count of members, and the sum of
the TransAmt.

tia.

You'll need some nested queries:

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt] FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

This will sum each member's deposits within the subquery, return that
sum as a subtotal, and then sum those subtotals.

John W. Vinson[MVP]

 

[Ken Snell [MVP]]

Try this (there was a missing ) for the SUM in the nested subquery):

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt]) FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

--

Ken Snell
<MS ACCESS MVP>

I get it.....thanks a lot. However, when I save they query I get an error
that a ) or ] is missing. I've spent about 10 min. trying to find it...can
you help?

Thanks again.

Here is the query:

SELECT Count(MEMBER.MemberNumber) AS CountOfMemberNumber,
Sum(TRANSACTION.TransAmt) AS SumOfTransAmt
FROM MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID =
TRANSACTION.MemberID
WHERE (((MEMBER.EquityStatus)="FULL" Or (MEMBER.EquityStatus)="CURRENT"
Or
(MEMBER.EquityStatus)="NONCURRENT") AND ((TRANSACTION.[Transaction
Type])="Deposit"));


The problem is the count, it returns the number of "deposit" rows with
matching Member number instead of the count of member number. I tried
using
Distinct but got errors. How do I get the count of members, and the sum
of
the TransAmt.

tia.

You'll need some nested queries:

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt] FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

This will sum each member's deposits within the subquery, return that
sum as a subtotal, and then sum those subtotals.

John W. Vinson[MVP]

 

[HB]

Thanks Ken. There were no errors, but only the correct number of members was
returned, the sum of amount was null.

Try this (there was a missing ) for the SUM in the nested subquery):

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt]) FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

--

Ken Snell
<MS ACCESS MVP>

I get it.....thanks a lot. However, when I save they query I get an error
that a ) or ] is missing. I've spent about 10 min. trying to find it...can
you help?

Thanks again.

Here is the query:

SELECT Count(MEMBER.MemberNumber) AS CountOfMemberNumber,
Sum(TRANSACTION.TransAmt) AS SumOfTransAmt
FROM MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID =
TRANSACTION.MemberID
WHERE (((MEMBER.EquityStatus)="FULL" Or (MEMBER.EquityStatus)="CURRENT"
Or
(MEMBER.EquityStatus)="NONCURRENT") AND ((TRANSACTION.[Transaction
Type])="Deposit"));


The problem is the count, it returns the number of "deposit" rows with
matching Member number instead of the count of member number. I tried
using
Distinct but got errors. How do I get the count of members, and the sum
of
the TransAmt.

tia.

You'll need some nested queries:

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt] FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

This will sum each member's deposits within the subquery, return that
sum as a subtotal, and then sum those subtotals.

John W. Vinson[MVP]

 

[John Vinson]

Try this (there was a missing ) for the SUM in the nested subquery):

Thanks Ken... needed some more coffee!

John W. Vinson[MVP]

 

[Ken Snell [MVP]]

I think a slight change in the query may solve this for you:

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt]) FROM [Transaction]
INNER JOIN [Member] ON [Transaction].[MemberID] = [Member].[MemberID]
WHERE [Transaction].[Transaction Type] = "Deposit" AND
[Member].[EquityStatus] IN ("FULL", "CURRENT", "NONCURRENT"))
AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN ("FULL", "CURRENT", "NONCURRENT");


--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. There were no errors, but only the correct number of members
was
returned, the sum of amount was null.

Try this (there was a missing ) for the SUM in the nested subquery):

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt]) FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

--

Ken Snell
<MS ACCESS MVP>

I get it.....thanks a lot. However, when I save they query I get an
error
that a ) or ] is missing. I've spent about 10 min. trying to find
it...can
you help?

Thanks again.

:

Here is the query:

SELECT Count(MEMBER.MemberNumber) AS CountOfMemberNumber,
Sum(TRANSACTION.TransAmt) AS SumOfTransAmt
FROM MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID =
TRANSACTION.MemberID
WHERE (((MEMBER.EquityStatus)="FULL" Or
(MEMBER.EquityStatus)="CURRENT"
Or
(MEMBER.EquityStatus)="NONCURRENT") AND ((TRANSACTION.[Transaction
Type])="Deposit"));


The problem is the count, it returns the number of "deposit" rows
with
matching Member number instead of the count of member number. I tried
using
Distinct but got errors. How do I get the count of members, and the
sum
of
the TransAmt.

tia.

You'll need some nested queries:

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt] FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

This will sum each member's deposits within the subquery, return that
sum as a subtotal, and then sum those subtotals.

John W. Vinson[MVP]

 

[HB]

Bingo! Thank you very much for your help.

HB

I think a slight change in the query may solve this for you:

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt]) FROM [Transaction]
INNER JOIN [Member] ON [Transaction].[MemberID] = [Member].[MemberID]
WHERE [Transaction].[Transaction Type] = "Deposit" AND
[Member].[EquityStatus] IN ("FULL", "CURRENT", "NONCURRENT"))
AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN ("FULL", "CURRENT", "NONCURRENT");


--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. There were no errors, but only the correct number of members
was
returned, the sum of amount was null.

Try this (there was a missing ) for the SUM in the nested subquery):

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt]) FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

--

Ken Snell
<MS ACCESS MVP>


I get it.....thanks a lot. However, when I save they query I get an
error
that a ) or ] is missing. I've spent about 10 min. trying to find
it...can
you help?

Thanks again.

:

Here is the query:

SELECT Count(MEMBER.MemberNumber) AS CountOfMemberNumber,
Sum(TRANSACTION.TransAmt) AS SumOfTransAmt
FROM MEMBER INNER JOIN [TRANSACTION] ON MEMBER.MemberID =
TRANSACTION.MemberID
WHERE (((MEMBER.EquityStatus)="FULL" Or
(MEMBER.EquityStatus)="CURRENT"
Or
(MEMBER.EquityStatus)="NONCURRENT") AND ((TRANSACTION.[Transaction
Type])="Deposit"));


The problem is the count, it returns the number of "deposit" rows
with
matching Member number instead of the count of member number. I tried
using
Distinct but got errors. How do I get the count of members, and the
sum
of
the TransAmt.

tia.

You'll need some nested queries:

SELECT Count(*) AS CountOfMemberNumber,
Sum(SELECT Sum([Transaction].[TransAmt] FROM [Transaction]
WHERE [Transaction].[MemberID] = [Member].[MemberID]
AND [Transaction].[Transaction Type] = "Deposit") AS SumOfTransAmt
FROM [Member]
WHERE [Member].[EquityStatus] IN("FULL", "CURRENT", "NONCURRENT");

This will sum each member's deposits within the subquery, return that
sum as a subtotal, and then sum those subtotals.

John W. Vinson[MVP]