Record numbers generated as part of a Query

[James Frater]

Hello Everyone,

Is there anyway I can get a column in a Query to show the record number for
each of the records?

So say for example I have 8 appointments for 22nd Jan I would like to see
the query return

No Time Apt
1 0800 Mr A
2 0830 Mrs B
3 0900 Mr C
4 0930 Mrs D
5 1000 Mr E
6 1030 Mrs F
7 1100 Mr G
8 1130 Mrs H

Any advice as always is mostly appreciated.

Many thanks

JAMES

 

[Jeff Boyce]

James

Define "record number"... no, really!

Access doesn't have an inherent record number ... the order in which you see
the records depends on sorting, filtering, etc.

If you want to have a "sequence number" generated in a query, in which you
(apparently) are sorting by time, you need to create that. Try searching
on-line for Access-related, query-related "ranking" for approaches.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Ken Sheridan]

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