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;

I am told that there is no way of doing this using a query. Is it possible to accomplish this via VBA? Any assistance would be greatly appreciated.

Regards,
Chris

 

[richary]

You should be able to do this in a query. Look at the SQL GROUP BY keyword and use that to group on address.id (I assume you have a seperate Address table, if the database is normalized?). You can then sum on MemberDues to give the total for the family. This assumes that all members of the family live at the same address, of course.

One question that you will have to consider is who you will send the payments to ie which Members.FirstName Member.LastName etc. in the household?

From the look of your query (and I am only going on this info, of course), the database is not completly normalised. For example, HomePhone should probably live with the address table, rather than the Member since this will (probably) repeat for multiple family members. Same goes for parents names (probably).

 

[eckert1961]

Hello,

You're correct. After I posted this I did some more work on the query and I came up with the following.

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 for taking the time to respond.

Take care,
Chris

 

[richary]

You're welcome!