UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Row Number By Grouping, Access 2016    
 
   
WildBird
post Jun 10 2019, 04:17 PM
Post#1


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


Hi,

Having a seniors moment, again, and cant think straight.

I have a table with an EmployeeID. The table may have multiple records for each employee, or just 1. I just need to have a row number for each record e.g

EmployeeID, RowNumber
ABC123,1
ABC123,2
ABC234,1
ABC235,1
ABC236,1
ABC236,2
ABC236,3

I can get a row number for the entire table, but need it for each EmployeeID.

Ideally just in SQL (not a large table, and one off, so not too concerned about time it takes to run)

Any ideas, much appreciated!

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
RJD
post Jun 10 2019, 04:47 PM
Post#2


UtterAccess VIP
Posts: 9,911
Joined: 25-October 10
From: Gulf South USA


Hi: Do you have another field in the table that will differentiate the records - such as an autonumber (unique record ID) rather than just the EmployeeID, which repeats and does not uniquely identify the record? If so, that can be used along with the EmployeeID in a DCount or subquery method to give you a sequence within EmployeeID.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
WildBird
post Jun 10 2019, 04:52 PM
Post#3


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


Hi Joe,

Yes, have a unique ID for the table

EmployeeID, ID
ABC123,1
ABC123,2
ABC234,3
ABC235,4
ABC236,5
ABC236,6
ABC236,7

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
RJD
post Jun 10 2019, 05:02 PM
Post#4


UtterAccess VIP
Posts: 9,911
Joined: 25-October 10
From: Gulf South USA


Okay, something like this ...

SELECT ID, EmployeeID,
DCount("*","[tblEmployees]","[EmployeeID]='" & [EmployeeID] & "' And [ID]<=" & [ID]) AS RowNumber
FROM tblEmployees
ORDER BY EmployeeID, ID;

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
WildBird
post Jun 10 2019, 05:30 PM
Post#5


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


I currently have 329 tables, 186 queries, (and 1 form and 1 splash screen)

I tried your code, and it wasnt working. Looking at it again, I had put the wrong table in. Little things like that make a bit of a difference! :-)

Anyway, this looks like a goer! Will gladly get you a beer if we are ever in same vicinity!

I had seen code like this, but I never use DCount() and all examples I had seen had been an overall row number, not grouped. This should get me working.

Many thanks!

Cheers



--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
RJD
post Jun 10 2019, 05:36 PM
Post#6


UtterAccess VIP
Posts: 9,911
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that did what you needed. thumbup.gif

If this is really too slow, there are other ways to do this that do not use a domain function (DCount). There may be one that runs faster. But if this is good enough for now ...

And, wow!, that's a lot of tables! Can any of them be combined?

Continued success ... and looking forward to the beer if you are ever on the US Gulf Coast - or I ever get in the down under area!

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
WildBird
post Jun 11 2019, 09:58 PM
Post#7


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


Its a somewhat unique project. One company was bought out by another, larger multi national. So while we are also moving to a new company structure, the parent company is migrating to new a HR system and a new payroll system. So we have 2 source systems migrating into 2 outputs. Currently 439 tables, and 239 queries. Consistent naming conventions makes it easier to deal with.

Should have been SQL Server, as it is personal data, but its staying in Access. They didnt want to host a server offsite or something. If the project doesnt work, the whole country wont be working as its HR and payroll :-) So lots of visibility and its all stored in a standalone Access DB, written by a foreign contractor (me). Data is volatile, so I can delete it from my database, and then reimport it. I have to deal with a different domain, SSRS, encryption and decryption, and everything else, all with MS Office.

Anyway, will be keen for a beer and travel at the end of this project, so could end up back in The States. Got friends in Florida, so could actually end up there! The row numbering works fine by the way.





--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
RJD
post Jun 12 2019, 09:19 PM
Post#8


UtterAccess VIP
Posts: 9,911
Joined: 25-October 10
From: Gulf South USA


Sounds like you have a lot of work cut out for yourself. Best of luck.

I live on the Mississippi coast, so if you get to the Florida panhandle, maybe we can get that beer. I get to Orange Beach, AL, several times a year. The Flora-Bama Lounge (a popular dive, in Perdido Key, Florida) is just down the road.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th August 2019 - 12:05 PM