James:
Sounds like you want the rows ordered by date and then by time and numbered
sequentially in time order per day. If you have separate 'date' and 'time'
columns then:
SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.DateField = T1.DateField
AND T2.TimeField <= T1.TimeField) AS No,
DateField, TimeField
FROM YourTable AS T1
ORDER BY DateField, TimeField;
If, as is better, you have a single column for date and time (there being no
such thing in Access as a date value or time value per se, only date/time
values), then:
SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE DATEVALUE(T2.DateTimeField)
= DATEVALUE(T1.DateTimeField)
AND TIMEVALUE(T2.DateDateTimeField)
<= TIMEVALUE(T1.DateDateTimeField)) AS No,
DATEVALUE(T1.DateTimeField) AS ApptDate,
TIMEVALUE(T1.DateTimeField) AS ApptTime
FROM YourTable AS T1
ORDER BY DateTimeField;
To return just the rows for a particular day add a WHERE clause to the outer
query with a parameter on, if using separate columns for date and time, the
date field, if using a single column on the DATEVALUE of the date/time field,
i.e.
PARAMETERS [Enter date:] DATETIME;
SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.DateField = T1.DateField
AND T2.TimeField <= T1.TimeField) AS No,
DateField, TimeField
FROM YourTable AS T1
WHERE DateField = [Enter date:]
ORDER BY DateField, TimeField;
or:
PARAMETERS [Enter date:] DATETIME;
SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE DATEVALUE(T2.DateTimeField)
= DATEVALUE(T1.DateTimeField)
AND TIMEVALUE(T2.DateDateTimeField)
<= TIMEVALUE(T1.DateDateTimeField)) AS No,
DATEVALUE(T1.DateTimeField) AS ApptDate,
TIMEVALUE(T1.DateTimeField) AS ApptTime
FROM YourTable AS T1
WHERE DATEVALUE(DateTimeField) = [Enter date:]
ORDER BY DateTimeField;
Note that date/time parameters should always be declared to avoid the
possibility of a parameter value entered in short date format being
misinterpreted as an arithmetical expression and giving the wrong results.
Ken Sheridan
Stafford, England