Counting time????

[Guest]

Ok so I am creating a Database for a homeless shelter here in my town. What
they are trying to acheive is something that will track the guests that come
in and how long they stay there (along with some basic info). The guest's
are only allowed to stay for a certain number of nights per season.

So I need to know if there is a way that access has a way of tracking their
nightly stays and totaling that? (not sure if I am even saying that right)?

I am very new to this and have figured out the basics to creating the
database, but need a little more help. I think that I am using Access 2000

 

[Guest]

I would start with two tables: Guests and Stays

Guests would have info about the person. For example:

GuestID - An autonumber field used as the primary key.
GuestFirstName
GuestLastName
GuestDateOfBirth
GuestNextOfKin
etc.

Next would be the Stays table
StayID - An autonumber field used as the primary key.
GuestID - A Number field used as the foriegn key to the Guests table.
StayStart - A date field
StayEnd - A date field
ect.

Now in a totals query it would be very easy to count the number of stays for
each guest:

SELECT Guests.GuestID,
GuestFirstName,
GuestLastName,
Count(StayID) AS Stays
FROM Guests, Stays
GROUP BY Guests.GuestID, GuestFirstName, GuestLastName
HAVING Guests.GuestID = Stays.GuestID
AND StayStart > Now() - 91
ORDER BY Guests.GuestID;

However that would be the number of stays and not the number of days. Below
could show the number of days spent in the last quarter.

SELECT Guests.GuestID,
Guests.GuestFirstName,
Guests.GuestLastName,
Sum([StayEnd]-[ StayStart]+1) AS StayDays
FROM Guests LEFT JOIN Stays ON Guests.GuestID = Stays.GuestID
WHERE Stays.StayStart > Now() - 91
GROUP BY Guests.GuestID, Guests.GuestFirstName, Guests.GuestLastName
ORDER BY Guests.GuestID;

 

[Jamie Collins]

I am creating a Database for a homeless shelter here in my town. What
they are trying to acheive is something that will track the guests that come
in and how long they stay there (along with some basic info). The guest's
are only allowed to stay for a certain number of nights per season.

So I need to know if there is a way that access has a way of tracking their
nightly stays and totaling that?

"Why should I consider using an auxiliary calendar table?"
http://www.aspfaq.com/show.asp?id=2519

Jamie.

--