Multiple Dates on a Table

[Joan]

I need to create a database to schedule patient appointments. I get
one piece of paper with a years worth of appointments (can be every
week or every month) and I want to be able to plug in all the dates,
then just pull each day one at a time to get my list of patients. Any
suggestions how to set that up? I tried creating differetn fields for
the weeks of the year, but then when I use a query to look for the
date, I have to search in each field. I want to search the whole
database by date. Help!!

 

[Rick B]

Maybe I am missing something. I would think you would have a patient table
with all their data, then an Appointments table. The appointment table
would include the patient number (link to your patient table), the date,
time, comments, etc.

To get a list of all of today's appointments, you'd just build a query that
pulls all the records from your appointment table with the date field equal
to today's date (or tomorrow's if you plan to print this the night before).

If you want a nice report, just build a report based on that query. You
could include the related patient table in your query, and you'd be able to
print the patient's name (and phone number?) on the report as well.

If this does not answer your question, I'd need to know more about your data
structure.

 

[Duane Hookom]

I would keep the tables normalized. You might want to look at the sample
calendar reports at http://www.access.hookom.net/Samples.htm

 

[Guest]

In what form are the appointments given in the list which you receive for the
year? Do they include the actual dates of each appointment or are they in
the form of "Weekly for 10 weeks from 4 September 2006" or "Monthly for 6
months from 2 January 2006".

If you have a list of actual appointment dates its simply a case of entering
them into an Appointments table which has PatientID and AppointmentDate
columns. If its in the "Weekly for n…." or "Monthly for n…" formats the
you'd need a little bit of simple code to insert however many rows for each
patient into the table, each with the relevant PatientID and AppointmentDate
value. Basically the code would start from the first date and then compute
the other dates n times. Its not just a question of adding one month or one
week each time, however, as some dates might not fall on a working day. With
the above monthly example 2 April 2006 is a Sunday for example. Even with
weekly dates some days might be public holidays (whose dates you'd have in
another table), so these would also need to be adjusted. This is quite
simple to do in code, but before showing you exactly how we'd need to know
the format of your paper list.

Ken Sheridan
Stafford, England