Need help with a "roster" database -> date ranges

[RWX]

I'm trying to implement a Sign-In/Sign-Out database and I have a few questions about how to approach the project. I work for a company that provides services, job training, etc. to the homeless. My goal is to create a database that stores client information and one that stores a client visit history. Clients need to sign in every morning and sign out when they leave. Right now this is done on paper. I'd like to make a database so that we have the ability to easily track how often a client uses services, how often they visit us, etc.

My intial thought was to create two related files in a one-to-many relationship. Clients would store the client information and Visits would store the sign-in/sign-out information. So far so good. My question is, how should I handle days when a client does NOT visit the shelter? A client might visit on 10/1, 10/2, and 10/5. If I print a visit history, I'll see those three dates. I'd like to also see that this client did NOT sign in on 10/3 or 10/4.

One thought was to have three databases: Clients, Visits, and Dates. Dates would have one record for each day of the year. I could then relate both Clients and Visits to Dates and view every client that visited on a certain date or select a date range and print a report showing which days a particular client visited. Is this the way to go? Or is having a record for each day a bad idea?

It seems to me like you should only need to have records for the actual dates visited (in order to log sign in/out times, etc), not for every single date, as suggested in my two database idea (Clients and Visits). If the Visits file included Date, Time In, and Time Out fields, one could search for a particular date and see who signed in on that day. However, if I enter a range of dates, I'm only going to see the dates someone actually signed in. I'd like to also see blank lines or "Did Not Attend", or something to that effect printed on the dates the client did not sign in. In other words, every date in the range should appear on the report, not just the records in the Visits database.

Whew. I hope this makes sense. -) Any help would be much appreciated!

-Rob

[zincb]

Your approach seems solid, but I would minimize the number of files in any solution, as long as it doesn't "cost" too much.

You could create a looping script that marches down all the days that a client DID visit, creating a temporary "dummy row" for each date they did NOT visit.

In a Shutdown script, delete these empty rows, which will have the client & date field filled, but not the time (or some hidden field that you can set to "X" for the dummy rows).