Trying to label each date with same number from 1 to 5. Repeat until lis ends

[vianda]

Hi, I'm trying to make a query, that will put a number in field “Date #” for each date occurrence, from number 1 to 5
(same # to same dates). I’m not sure if I have explained this clear, so I added a picture of what I want to be returned in the “Date #” field.



Thanks

Query1
Provider'sName	Child'sName	Date	Date #
Jane Smith	HITZGES, AYLA	6/1/2016	1
Jane Smith	BREEN, FIONA	6/1/2016	1
Jane Smith	Grundner Jayden	6/1/2016	1
Jane Smith	WILDING, CONNOR	6/2/2016	2
Jane Smith	CLINTON, BROOKLYN	6/2/2016	2
Jane Smith	BREEN, FIONA	6/3/2016	3
Jane Smith	PLETO, LANDON	6/3/2016	3
Jane Smith	WILDING, CONNOR	6/3/2016	3
Jane Smith	Boos Ethan	6/6/2016	4
Jane Smith	BREEN, FIONA	6/6/2016	4
Jane Smith	HITZGES, AYLA	6/6/2016	4
Jane Smith	Grundner Jayden	6/6/2016	4
Jane Smith	RICE, GRADY	6/6/2016	4
Jane Smith	WILDING, CONNOR	6/6/2016	4
Jane Smith	Gross Oliver	6/6/2016	4
Jane Smith	Melton Garrett	6/7/2016	5
Jane Smith	DEARING, KAYLEE	6/7/2016	5
Jane Smith	DEARING, KAYLEE	6/7/2016	5
Jane Smith	CLINTON, BROOKLYN	6/7/2016	5
Jane Smith	HITZGES, AYLA	6/7/2016	5
Jane Smith	WILDING, CONNOR	6/7/2016	5
Jane Smith	Grundner Jayden	6/8/2016	1
Jane Smith	HITZGES, AYLA	6/8/2016	1
Jane Smith	RICE, GRADY	6/8/2016	1
Jane Smith	BREEN, FIONA	6/8/2016	1
Jane Smith	HITZGES, AYLA	6/8/2016	1
Jane Smith	DEARING, KAYLEE	6/9/2016	2
Jane Smith	Grundner Jayden	6/9/2016	2
Jane Smith	Boos Ethan	6/9/2016	2
Jane Smith	BREEN, FIONA	6/10/2016	3
Jane Smith	WILDING, CONNOR	6/10/2016	3

[Micron]

As far as I'm concerned, it is only possible with code. Before going down that route, are you absolutely certain that this is a requirement, or would you care to provide the reason? Maybe there is another way.
BTW, you should not use special characters (e.g. # or ') or reserved names (e.g. Date)

[ArviLaanemets]

On fly

SELECT yt.*, Int(((SELECT COUNT(dc.DateField) FROM YourTable dc WHERE dc.ProvidersName = yt.ProvidersName AND dc.DateField <= yt.DateField) -1)/5) AS DateNo FROM YourTable yt

NB! I changed field names in query, and I advice you do same with your tables/queries:
Date is reserved word in Access (and in most other databases too) - you never can be sure how Access reacts on it;
Don't use nothing except letters, numbers and "_" in field names. Access can interpret other characters (like "'" or "#") in his own way.

[June7]

Tested the query and getting count of 0, 1, 2, 4, 5, 6

[CJ_London]

think you need something like

SELECT yt.*, (((SELECT COUNT(DateField) FROM (SELECT DISTINCT Datefield FROM YourTable) dc WHERE dc.DateField <= yt.DateField)-1) MOD 5)+1 AS DateNo FROM YourTable yt

[June7]

Fantastic! That works.

[ArviLaanemets]

OK. I forgot that on same date are several entries at first.

Create a saved query p.e.

SELECT DISTINCT ProvidersName, DateField FROM YourTable;

Now the query will be:

SELECT yt.*, (((SELECT COUNT(pd.DateField) FROM qProviderDates pd WHERE pd.ProvidersName = yt.ProvidersName AND pd.DateField <= yt.DateField)-1)/5 - Int(((SELECT COUNT(pd.DateField) FROM qProviderDates pd WHERE pd.ProvidersName = yt.ProvidersName AND pd.DateField <= yt.DateField)-1)/5))*5 + 1 AS DateNo FROM YourTable yt

Edit. I see Ajax got simpler solution!

[vianda]

Thank you for the help. I did write your solution and I get an error 'Syntax Error in Join Operation' highlighting "MOD". Also I used

/5))*5 (ArviLaanemets)

Same error

highlighting "/". I'm Stuck

SELECT UNITSbyDATE.[Provider'sName], UNITSbyDATE.[Child'sName], UNITSbyDATE.DateField, UNITSbyDATE.Units, UNITSbyDATE.Rate, UNITSbyDATE.DateNumber
FROM UNITSbyDATE,
(((SELECT COUNT(DateField) FROM (SELECT DISTINCT Datefield FROM UNITSbyDATE) dc WHERE dc.DateField <= UNITSbyDATE.DateField)-1) MOD 5)+1 AS DateNo FROM UNITSbyDATE

[June7]

Can't have two FROM clauses in the outer query.

Ajax's suggestion works. Have to use the yt table alias.

SELECT yt.*, (((SELECT COUNT(DateField) FROM (SELECT DISTINCT Datefield FROM UNITSbyDATE) dc WHERE dc.DateField <= yt.DateField)-1) MOD 5)+1 AS DateNo FROM UNITSbyDATE yt;

[vianda]

Thank You to everyone who help, It works great!