Adding Payments Due for family members in query.

[eckert1961]

Hello,

I have a query that lists members who have outstanding payments. The results of this query are being used to populate a winword mail merge document.

The problem with this query is that if there are multiple members from the same family who owe then I will get an entry for each of them in my query results. What I would like to do is add the amounts due so that I only have 1 entry for that family. Additionally, since it is possible that more than 1 person can have the same last name and not be related I would also like to have the query check the address field and if it's the same than add the amounts due. The following is the sql statement for the current query.

SELECT Members.FirstName, Members.LastName, Members.ParentsNames, Members.HomePhone, Members.PaymentDue, Members.Active, Members.MemberDues, Members.FamilyAmount
FROM Members INNER JOIN MemberTypes ON Members.MemberTypeID = MemberTypes.MemberTypeID
WHERE (((Members.PaymentDue)=Yes) AND ((Members.Active)=Yes))
ORDER BY Members.LastName;

Any assistance would be greatly appreciated.

Regards,
Chris

 

[ScottGem]

The only way to do this is by having a family table with the FamilyID as a foreign key that's used to group the outstanding amounts.

 

[Jon K]

... since it is possible that more than 1 person can have the same last name and not be related I would also like to have the query check the address field and if it's the same than add the amounts due.

Since the same LastName + Address denotes a family, if you can add the Address field in your query:-

SELECT Members.Address, Members.FirstName, Members.LastName, Members.ParentsNames, Members.HomePhone, Members.PaymentDue, Members.Active, Members.MemberDues, Members.FamilyAmount
FROM Members INNER JOIN MemberTypes ON Members.MemberTypeID = MemberTypes.MemberTypeID
WHERE (((Members.PaymentDue)=Yes) AND ((Members.Active)=Yes))
ORDER BY Members.LastName, Members.Address;


then, from that query, you can build a Totals Query (GROUP BY LastName, Address) to SUM the amounts due.
.

 

[eckert1961]

Additional Assistance Please.

Thanks Jon,

I tried your suggestion and made the following changes to the query.

SELECT Members.HomeAddress, Members.FirstName, Members.LastName, Members.ParentsNames, Members.HomePhone, Members.PaymentDue, Members.Active, Sum(Members.MemberDues) AS SumOfMemberDues, Sum(Members.FamilyAmount) AS SumOfFamilyAmount
FROM Members INNER JOIN MemberTypes ON Members.MemberTypeID = MemberTypes.MemberTypeID
GROUP BY Members.HomeAddress, Members.FirstName, Members.LastName, Members.ParentsNames, Members.HomePhone, Members.PaymentDue, Members.Active, Members.HomeAddress
HAVING (((Members.PaymentDue)=Yes) AND ((Members.Active)=Yes))
ORDER BY Members.LastName, Members.HomeAddress;

Unfortunately I still get an entry for each family member. Did I miss something?

Regards,
Chris

 

[eckert1961]

Success

Hi Jon,

I read your post a second time, after stepping away from my PC for a couple of hours, and I finally understood your recommendation.

I created the first query as follows.

SELECT Members.HomeAddress, Members.FirstName, Members.LastName, Members.ParentsNames, Members.HomePhone, Members.PaymentDue, Members.Active, Members.MemberDues, Members.FamilyAmount
FROM Members INNER JOIN MemberTypes ON Members.MemberTypeID = MemberTypes.MemberTypeID
WHERE (((Members.PaymentDue)=Yes) AND ((Members.Active)=Yes))
ORDER BY Members.LastName, Members.HomeAddress;

I used this query to create a 2nd query.

SELECT [New Outstanding Duesqry].LastName, [New Outstanding Duesqry].HomeAddress, Sum([New Outstanding Duesqry].MemberDues) AS SumOfMemberDues, Sum([New Outstanding Duesqry].FamilyAmount) AS SumOfFamilyAmount, NZ([SumofMemberDues])+NZ([SumOfFamilyAmount]) AS [Payment Due]
FROM [New Outstanding Duesqry]
GROUP BY [New Outstanding Duesqry].LastName, [New Outstanding Duesqry].HomeAddress;

It works great. Thanks very much for your recommendation.

Take care,
Chris