Count Sequential Records (by date)

[RussCRM]

I work for a small homeless shelter. I have a table called "Services"
that contains records with a ServiceID, GuestID, ServiceDate, and
ServiceType. Such as:

ServiceID (key) GuestID ServicesDate ServicesType
1875 4572 02/01/08 Men's Dorm
1877 9762 02/02/08 Men's Dorm
1892 9762 02/03/08 Men's Dorm


We also have a table called "Guest" with each guest's name and other
information.

For various reasons, we keep an ongoing count of how many nights in a
row a guest has stayed.

To count the total number of nights for a guest, we usually do a
dcount type of thing on ServiceID.

Anyone have any ideas how I could get a count of the number of days in
a row a guest has stayed?

 

[Michel Walsh]

Make a first query which ranks, per guestID, the dates:


SELECT a.guestID, a.servicedDate, COUNT(*) AS rank
FROM myTable AS a INNER JOIN myTable AS b
ON a.guestID = b.guestID
AND a.servicedDate >= b.servicedDate
GROUP BY a.guestID, a.servicedDate


Say it is saved as qRank. Then, make the query:


SELECT a.guestID,
MIN(a.servicedDate) AS starting,
MAX(a.servicedDate) AS lastDate,
MAX(a.servicedDate)-MIN(a.servicedDate) + 1 AS numberOfDays
FROM qRank AS a
GROUP BY a.questID, a.servicedDate-a.rank


should give what you want.


Note that I assumed there is just one record, for a given date, for a given
guestID, in your initial table.




Hoping it may help,
Vanderghast, Access MVP

 

[RussCRM]

Thanks! Is there any way to group them by or isolate by ServicesType
also? For instance, we have several service types for the same day
(breakfast, dinner, Men's Dorm, etc.) For this case, I want a report
for Men's Dorm only.

 

[RussCRM]

Nevermind that last one, I figured it out.