Solved

Adding Record Count Based on Multiple Criteria in MS Access

Posted on 2015-02-01
4
407 Views
Last Modified: 2016-02-11
I have data with dates and I need to add a field that shows the record number by date showing the oldest first by 1,2,3,4,5, etc

Here is an example:
RECOR DID      Month      Date      Step       Counter
1000123 3/1/2013      3/5/2013      4      1
1000123 3/1/2013      3/11/2013      4      2
1000123 3/1/2013      3/18/2013      4      3
1000123 3/1/2013      3/25/2013      4      4
1000123 4/1/2013      4/1/2013      4      1
1000123 4/1/2013      4/6/2013      4      2
1000123 4/1/2013      4/15/2013      4      3
1000123 4/1/2013      4/22/2013      4      4
1000123 4/1/2013      4/29/2013      4      5
1000123 5/1/2013      5/6/2013      4      1
1000123 5/1/2013      5/13/2013      4      2
1000123 5/1/2013      5/20/2013      4      3
1000123 5/1/2013      5/27/2013      4      4
1000123 6/1/2013      6/3/2013      4      1
1000123 6/1/2013      6/10/2013      4      2
1000123 6/1/2013      6/17/2013      4      3
1000123 6/1/2013      6/24/2013      4      4
1000123 7/1/2013      7/1/2013      4      1
1000123 7/1/2013      7/8/2013      4      2
1000123 7/1/2013      7/15/2013      4      3
1000123 7/1/2013      7/22/2013      4      4
1000123 7/1/2013      7/29/2013      4      5
1000123 8/1/2013      8/5/2013      4      1
1000123 8/1/2013      8/12/2013      4      2
1000123 8/1/2013      8/19/2013      4      3
1000123 8/1/2013      8/26/2013      4      4
 
Thanks in advance for your help
0
Comment
Question by:fb1990
4 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40582950
Is there any guarantee of uniquely identifying the rows in your data e.g. the combination of RecordID and Date?

If not, how would you want to deal with multiple records for the same date and recordID (both tied on the same rank, or one randomly ranked above the other)?

Would you be happy with a row-by-row VBA routine to do the numbering?

is this something you have to do repeatedly on large datasets?

The option that would perform best is to use a query (ordered by recordid and date) to append these records into a table that has an autonumber field. You would then get sequential numbering, but it wouldn't always start from one unless you re-created the destination table each time.
0
 
LVL 4

Accepted Solution

by:
scsyme earned 500 total points
ID: 40582966
Below is a code example that will give you what you want on the assumption that the dates are unique within each month.

SELECT 
	t1.RecordId, 
	t1.Month,
	t1.Date,
	t1.Step,
	(SELECT COUNT(*) 
	FROM YourTable t2 
	WHERE t2.RecordId=t1.RecordId
	AND t2.Month=t1.Month
	AND t2.Step=t1.Step
	AND t2.Date <= t1.Date) as Counter
FROM
	YourTable t1

Open in new window


If the dates are not unique then you need an additional unique field inequality for the subquery. (Is RecordId really non-unique as shown?)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40583172
Note that your "date" field looks a though the dates are stored as 'Text" values...


JeffCoachman
0
 
LVL 1

Author Closing Comment

by:fb1990
ID: 40583656
Thanks to everyone that contributed. scsyme solution gave me what i needed.  Somehow is it slow, but i think it is because i am using it on a large dataset.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question