Hi

I've got a problem with a database I'm checking. I need to group a selection of records using 'group by', but then select the top record of that group, eg. select a person and payment date, then select last payment made by that person based upon year and display in a query, so it look like: name: Joe Bloggs last payment date: 01/01/2005.

The information is based on two tables - one = main details which include name/personal information, the other = payment/subscription information. The relationship is a one-to-many as one individual can subscribe each year and records kept to track previous years payments. The results will be fed into a report in order to print out who have lapsed membership.

Select query that I have is:

SELECT DISTINCTROW FirstName, LastName, ID, Payments.DatePaid
FROM Details LEFT JOIN Payments ON Details.ID = Payments.ID
WHERE (((Details.LastName) Is Not Null) AND ((DatePart("yyyy",[DatePaid]))<2006) AND ((Payments.PaymentDescription)="membership"));

Problem is this brings up all payment records for one person.

Sorry, probably easy to do.

Any info gratefully received.

Lindsey

Try sorting the field so that the most recent payment date comes to the top, and then select top 1. You will have to design the report so that the most current date is first, then right click, properties, then Top = 1.

Did that help?



mmcdonal

Hi

No it didn't. Sorry should have been more specific, the query brings up all records for all members. What I want to do is select just the first record for each individual member so that you have a list such as

Name: Jo Bloggs Last payment date: 2003
       Fred Smith 2002

Query I gave gives the following

Name: Jo Bloggs Last payment date: 2003
       Jo Bloggs 2002
       Jo Bloggs 2001
       Fred Smith 2001
       Fred Smith 2002
       etc, etc, etc,

Any ideas

Lindsey

Sure. I have done this with code. Where is the data going once you have it?

What I should do is to go research this in Henderson's Guru's Guide to Transact SQL, which covers this very issue in SELECT statements: getting the top record with groups of records.

What you want then is:

Jo Bloggs 2003
Fred SMith 2002
etc.

Correct? If you can wait till tomorrow, I can research this tonight. Otherwise we can do this pretty easily with code.

Another issue: SQL server in this book (2000) uses ANSI 92, and Access Jet uses ANSI 89, so even if I get the statement, it may not be supported in Access, but we will try.

Lemme know.


mmcdonal

Hi

This would be great. I've just had another go and still can't do it.

Thanks

Lindsey

What you're looking for is actually a very easy query, no need to go overboard with code:

"SELECT FirstName, LastName, Details.ID, MAX(DatePart("yyyy",[DatePaid])) AS LastPayment
FROM Details LEFT JOIN Payments ON Details.ID = Payments.ID
GROUP BY FirstName, LastName, Details.ID, Payments.PaymentDescription
HAVING (((Details.LastName) Is Not Null) AND (LastPayment<2006) AND ((Payments.PaymentDescription)="membership"));

Okay, so to be sure, I will research this tonight and get back tonight or tomorrow with the SQL? Or do you want to do code now?

BTW, where is the data going after? Sometimes I do this by making a table in the database to hold temporary values. Then when I run a process I run a delete query first to empty the table, then do my code processing to append a record at a time, and then run a query and/or report off the table, all on the same button.

mmcdonal

Yep, that is what I was after. I needed to look up the exact syntax though since I don't ever do this sort of thing, just remember where I have it written.

mmcdonal