[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Adding Record Count Based on Multiple Criteria in MS Access

Posted on 2015-02-01
4
Medium Priority
?
467 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 2000 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

612 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