Counting time????

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Princess - 13 Jun 2007 18:32 GMT

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

Signature

Thanks :)
Princess

Reply to this Message

Jerry Whittle - 13 Jun 2007 19:35 GMT

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;

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> 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
[quoted text clipped - 6 lines]
> 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

Reply to this Message

Jamie Collins - 14 Jun 2007 10:34 GMT

On Jun 13, 6:32 pm, Princess <Princ...@discussions.microsoft.com>
wrote:

> 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
[quoted text clipped - 3 lines]
> 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.

--

Reply to this Message