Trouble with grouping records

[dawnd3]

I am trying to find out the last payment made by each member in a club database. I have a query that links the member table with the payments made and then I group the records to show just one member and their "first" payment. (I sorted the date paid field asending and then asked for the first record for each of the payment table fields i want to see. Problem is that it isn't consistently showing the last payment made, sometimes it is showing the first payment. And I tried using MAX on the payment date field but then I didn't know what to put in the group section of the other fields I want to see. Here is my SQL but be gentle, I am not very knowledgeable about SQL. I usually do everything in the query window. Please help!


SELECT tblMemberInfo.DateJoined, tblMemberInfo.LastName, tblMemberInfo.FirstName, First(tblPayment.DatePaid) AS FirstOfDatePaid1, tblMemberInfo.MemberID, tblMemberInfo.StatusID, tblMemberInfo.PreviousStatus, tblMemberInfo.WorkPhone, tblMemberInfo.HomePhone
FROM tblMemberInfo LEFT JOIN tblPayment ON tblMemberInfo.MemberID = tblPayment.MemberIDPay
GROUP BY tblMemberInfo.DateJoined, tblMemberInfo.LastName, tblMemberInfo.FirstName, tblMemberInfo.MemberID, tblMemberInfo.StatusID, tblMemberInfo.PreviousStatus, tblMemberInfo.WorkPhone, tblMemberInfo.HomePhone
HAVING (((tblMemberInfo.StatusID)=2 Or (tblMemberInfo.StatusID)=11 Or ((tblMemberInfo.StatusID)=2 Or (tblMemberInfo.StatusID)=11) Or ((tblMemberInfo.StatusID)=2 Or (tblMemberInfo.StatusID)=11) Or ((tblMemberInfo.StatusID)=2 Or (tblMemberInfo.StatusID)=11) Or ((tblMemberInfo.StatusID)=2 Or (tblMemberInfo.StatusID)=11)) AND ((First(tblPayment.PaymentType)) Is Null)) OR (((First(tblPayment.PaymentType)) Not Like "donation")) OR (((First(tblPayment.PaymentType)) Not Like "bond")) OR (((First(tblPayment.PaymentType)) Not Like "scholarship")) OR (((First(tblPayment.PaymentType)) Not Like "*other*"))
ORDER BY tblMemberInfo.LastName, tblMemberInfo.FirstName, First(tblPayment.DatePaid), First(tblPayment.DatePaid)
WITH OWNERACCESS OPTION;


Thank You!

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

Since I am too lazy to decipher your SQL... Assuming you have the Northwind sample MDB and want to find the most recent Order information for each Customer. The SQL would use a [blue]subquery[/blue] to find the max OrderDate

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.ContactTitle, Customers.Address, Orders.OrderDate, Orders.Freight
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate=[blue](SELECT Max(OrderDate) FROM Orders O WHERE O.CustomerID = Customers.CustomerID)[/blue];

Duane
Hook'D on Access
MS Access MVP

 

[dawnd3]

Sorry Duane, I should have shortened it. I will look at the Northwind database and see if that helps.

http://www.calliopeconsulting.net