Solved

Adding Record Count Based on Multiple Criteria in MS Access

Posted on 2015-02-01
4
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

738 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