Do I need a Union for this?

[Guest]

Parent table is Member, child is Transaction which stores dues payments, and
dues refunds when a member leaves. I need a report telling how much dues we
have, number of members, and, the average dues payment (they're not all the
same). I know how to do these in two separate queries (count members, sum
payments) but how do I "join" (excuse the pun) the two.

tia

 

[John Spencer (MVP)]

One method:
Use the DCount function in your query.

SELECT DCount("*","Member") as MemberCount,
Sum(Dues) as TotalDues,
Avg(Dues) as AverageDues
FROM Transaction

 

[Guest]

Thanks, but I got the answer in another thread:

SELECT Count(*) AS CountOfMemberNumber, (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")));