INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...This is a very good site. Please keep it running. Thanks and wishing a great health and success for the site and its owners..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL Forum

Increment Number Problem

Read More Threads Like This One

Debbie37 (TechnicalUser)

15 Jun 12 16:31

Hi, I am trying to get a row number for each record in a group using MD_ID and CreateDate (date/time). For some MD_ID groups it works and others it does not. Can anyone tell me what I am doing wrong? Thanks in advance!

SELECT A.MD_ID, A.CreateDate, Count(*) AS [DefCt]
FROM tblAll AS A INNER JOIN tblAll AS B ON A.MD_ID = B.MD_ID and A.CreateDate > B.CreateDate
GROUP BY A.MD_ID, A.CreateDate;

Results
The last colimn is the DefCt that increments...
MD_ID CreateDate DefCt
2161 06/01/2012 15:13:00 1
2161 06/04/2012 10:55:00 2
2161 06/04/2012 13:25:00 3
2161 06/04/2012 13:43:00 4
2161 06/04/2012 13:45:00 10
2161 06/06/2012 07:47:00 7
2161 06/06/2012 08:47:00 8
2161 06/06/2012 14:28:00 9
2161 06/06/2012 14:34:00 10
2161 06/06/2012 15:08:00 11
2179 02/02/2011 15:04:00 1
2179 02/02/2011 15:05:00 2
2179 02/09/2011 08:21:00 3
2179 02/09/2011 08:27:00 4
2179 07/01/2011 12:57:00 5
2179 07/06/2011 08:12:00 6

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Queries and JET SQL