Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 7th, 2006, 04:58 AM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default Selecting top record of a group by clause

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
 
Old June 7th, 2006, 06:46 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old June 7th, 2006, 10:43 AM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 7th, 2006, 10:49 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old June 7th, 2006, 11:20 AM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

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

Thanks

Lindsey
 
Old June 7th, 2006, 11:22 AM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"));

 
Old June 7th, 2006, 11:23 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old June 7th, 2006, 11:25 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with clause Top in store procedure Javierera SQL Server 2005 3 October 24th, 2008 10:17 AM
Select the top 3 record of each employee phungleon Access 1 June 16th, 2007 01:16 AM
Group by Clause for 3 tables jfergy Classic ASP Professional 2 April 15th, 2006 06:21 PM
selecting only one primary record in a subform Richard Lally Access 2 April 1st, 2005 10:20 AM
SELECT TOP n NOT SELECTING TOP n! ibi SQL Language 8 March 30th, 2005 08:08 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.