Solved

Adding Record Count Based on Multiple Criteria in MS Access

Posted on 2015-02-01
4
414 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

830 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