How to Number Records Based on Another Record (1 Viewer)

veshand

New member
Local time
Yesterday, 22:15
Joined
Jun 10, 2018
Messages
9
Hello all!

Here is the question!

I have a table as below, named tblRollcall, with two fields (EmployeeName and DateOfRegistry). I need to number each record based on these two different fields.

EmployeeName................DateOfRegistry

A..............................................01/01/0001
A..............................................01/01/0001
A..............................................02/01/0001
B..............................................01/01/0001
B..............................................02/01/0001
B..............................................02/01/0001
C..............................................01/01/0001

What I need is this query:

EmployeeName................DateOfRegistry................................Number

A..............................................01/01/0001..............................................1
A..............................................01/01/0001..............................................2
A..............................................02/01/0001..............................................1
B..............................................01/01/0001..............................................1
B..............................................02/01/0001..............................................1
B..............................................02/01/0001..............................................2
B..............................................02/01/0001..............................................3
C..............................................01/01/0001..............................................1

What should I do!? I need a query here!! :confused:
 

isladogs

MVP / VIP
Local time
Today, 05:15
Joined
Jan 14, 2017
Messages
18,186
Either a query or a user defined function.
If you want specific help from someone you will need to explain the patter in the required output as it makes no sense (to me at least)
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:15
Joined
Sep 21, 2011
Messages
14,048
I *think* the o/p is looking for running total of matching employee and date.?
 

isladogs

MVP / VIP
Local time
Today, 05:15
Joined
Jan 14, 2017
Messages
18,186
That was my assumption as well but the numbers are incorrect if that's the case.
Perhaps every date change it reverts to 1 (giving duplicates) then a running sum?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:15
Joined
Sep 12, 2006
Messages
15,614
@OP

this is easy in a report - the "running value" within a sort group keeps count of the indexes. I think it's called running value, or something similar.

it's very hard in a form/query, because the order of the rows is not guaranteed, and is not really a "thing" with databases. Access does not define a previous or next row for that reason.
 

Micron

AWF VIP
Local time
Today, 01:15
Joined
Oct 20, 2018
Messages
3,476
I did something similar to this for someone else the other day. They wanted a query but I (and other responders) said it couldn't be done - because of the multiple forward (and in their case, backward) looks. The only chance of having a reliable order would be to sort by employee then by date and base any attempt on a query. I'd recommend that even if solving this by a report.
Thus, if a report is of no use, then a custom function might be the only solution. As far as the data being wrong for the proposed goal, I'm not seeing that.
This is a duo of date/employee pairs for A, so the count is 2 as shown by OP
01/01/0001
01/01/0001
This is a trio of date/employee pairs for B so the count is 3
02/01/0001
02/01/0001
02/01/0001
The rest are not pairs of employee and date. At least that's my observation, but this wouldn't be the first time I missed the ball. If this is handled by a function, I don't see it being one that is called by a query field either - unless all that is needed is the end count and not a running sum. That means a place would be needed to store the results.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:15
Joined
Sep 12, 2006
Messages
15,614
I did something similar to this for someone else the other day. They wanted a query but I (and other responders) said it couldn't be done - because of the multiple forward (and in their case, backward) looks. The only chance of having a reliable order would be to sort by employee then by date and base any attempt on a query. I'd recommend that even if solving this by a report.
Thus, if a report is of no use, then a custom function might be the only solution. As far as the data being wrong for the proposed goal, I'm not seeing that.
This is a duo of date/employee pairs for A, so the count is 2 as shown by OP
01/01/0001
01/01/0001
This is a trio of date/employee pairs for B so the count is 3
02/01/0001
02/01/0001
02/01/0001
The rest are not pairs of employee and date. At least that's my observation, but this wouldn't be the first time I missed the ball. If this is handled by a function, I don't see it being one that is called by a query field either - unless all that is needed is the end count and not a running sum. That means a place would be needed to store the results.


Yes. It's easy to do it by counting the records in each subset, to get domain counts and totals of each group of "employee + date", rather than trying to number the individual items in each subset. That's the way access works. It deals with the set of values, (and specifically ignores the ordering of items within the set)

A report is different. Because it is a final print, there is necessarily a pre-determined order of the records, and therefore it now becomes easy to add item numbering.
 

veshand

New member
Local time
Yesterday, 22:15
Joined
Jun 10, 2018
Messages
9
Either a query or a user defined function.
If you want specific help from someone you will need to explain the patter in the required output as it makes no sense (to me at least)

Many thanks for your time and guidance.
Well, such query shall number each record base on these two fields. each time the employee or date change, the numbering shall start from 1. for example if an employee registers an item in a new day, the numbering for this specific employee should start from 1. this action is easy in excel using a combination of count and if formula but in access, i'm not able to make it work! I agree, reports make it possible but I need it to work in a query!
Yours!
 

veshand

New member
Local time
Yesterday, 22:15
Joined
Jun 10, 2018
Messages
9
I did something similar to this for someone else the other day. They wanted a query but I (and other responders) said it couldn't be done - because of the multiple forward (and in their case, backward) looks. The only chance of having a reliable order would be to sort by employee then by date and base any attempt on a query. I'd recommend that even if solving this by a report.
Thus, if a report is of no use, then a custom function might be the only solution. As far as the data being wrong for the proposed goal, I'm not seeing that.
This is a duo of date/employee pairs for A, so the count is 2 as shown by OP
01/01/0001
01/01/0001
This is a trio of date/employee pairs for B so the count is 3
02/01/0001
02/01/0001
02/01/0001
The rest are not pairs of employee and date. At least that's my observation, but this wouldn't be the first time I missed the ball. If this is handled by a function, I don't see it being one that is called by a query field either - unless all that is needed is the end count and not a running sum. That means a place would be needed to store the results.



Hello there!
Thanks for your reply, I am appreciative.
Well, the query Im looking for should not count, but number them, starting from 1. in other words, if an specific employee register an item in a new day, this registration should be numbered from 1.
any idea?
yours
 

Micron

AWF VIP
Local time
Today, 01:15
Joined
Oct 20, 2018
Messages
3,476
There is a difference between count and sum. You seem to be thinking of sum, or else you are misinterpreting my explanation in response to post 4.
As for "any idea?" - what about the link in post 8? No comment?
 

isladogs

MVP / VIP
Local time
Today, 05:15
Joined
Jan 14, 2017
Messages
18,186
As well as the link in post #8, have a look at this example on my website Rank Order in Queries
Also look at the link I have provided to further examples on Allen Browne's website
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:15
Joined
Sep 12, 2006
Messages
15,614
Hello there!
Thanks for your reply, I am appreciative.
Well, the query Im looking for should not count, but number them, starting from 1. in other words, if an specific employee register an item in a new day, this registration should be numbered from 1.
any idea?
yours

Well, you could add a field to store the admission number for a day, and increment it each time. If you delete a record though, your numbers will not be in sequence. Why does it need to be numbered, though?

If you select the admissions for a date, or by date, it's easy to get a count of the number of admissions per day. If you store the date and time of the entry you can sort those admissions by the time entered. Isn't that enough. That's the way a database is designed to work. If you can work with that, it's easier than trying to "hammer" it into something different.


so instead of this

PHP:
EmployeeName................DateOfRegistry................................Number

A..............................................01/01/0001.............................................. 1
A..............................................01/01/0001.............................................. 2
A..............................................02/01/0001.............................................. 1
B..............................................01/01/0001.............................................. 1
B..............................................02/01/0001.............................................. 1
B..............................................02/01/0001.............................................. 2
B..............................................02/01/0001.............................................. 3
C..............................................01/01/0001.............................................. 1

you see this

PHP:
employee  date   count of records
A         01/01/0001  2
A         02/01/0001   1
B         01/01/0001  1
B         02/01/0001   3
C         01/01/0001  1

and if you need to see the details, you just expand one of the summaries.
 
Last edited:

veshand

New member
Local time
Yesterday, 22:15
Joined
Jun 10, 2018
Messages
9
There is a difference between count and sum. You seem to be thinking of sum, or else you are misinterpreting my explanation in response to post 4.
As for "any idea?" - what about the link in post 8? No comment?

Post #8 was great! it helped me a lot!!I did as it said!!
Thanks!!

I wish access guys would go around and ask users how to improve it before issuing the next version! :D
 

veshand

New member
Local time
Yesterday, 22:15
Joined
Jun 10, 2018
Messages
9
As well as the link in post #8, have a look at this example on my website Rank Order in Queries[/URL]
Also look at the link I have provided to further examples on Allen Browne's website

Thanks a lot!!! i will definitely read it thoroughly
Love!
 

Users who are viewing this thread

Top Bottom