Solved

Adding Record Count Based on Multiple Criteria in MS Access

Posted on 2015-02-01
4
396 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:SimonAdept
Comment Utility
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
Comment Utility
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
Comment Utility
Note that your "date" field looks a though the dates are stored as 'Text" values...


JeffCoachman
0
 
LVL 1

Author Closing Comment

by:fb1990
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now