do i need a query or report? (1 Viewer)

[merlin777]

I'm building an appointments database for a friend. Her team of 10 therapists will see many patients in a week, often the patients will have multiple visits from several team members in a day or week so she produces a timetable for each patient. Appointments go into a shared outlook calendar then someone copies them by hand onto around 20 blank timetables which takes ages.

I originally did this in excel but it couldn't handle the need for multiple simultaneous sharing so i'm learning access (!) for this project.

What I'm trying to figure out is the best way to compile and print 20 weekly timetables.

I'll need to print individual timetables, sometimes all and sometimes a selection. When printing one i'll need to specify a w/c date and patient, just a date for printing all and select from a list for the selection. It has occurred to me that the selection approach can cover all 3.

because i need to make selecctions, does this make it a job for a query or a report? Also there is quite a strict format ffor the timetable and i need to make it look as similar as pobble.

Any advice would be most welcome!

 

[namliam]

You need both, the query to make the selection, the report to make it look the way you want.

Also you will need a form to enter your date(s) and options in

 

[merlin777]

so how might this work in practice?

do i give my users a query to ask for a patient name and week, then a report to calculate it and print it?

 

[Mihail]

Not sure that I fully understand your initial post (because my English).
I think that the best deal is to unify that "20 weekly timetables" in a single table (easy to do, with a bit of code, in Excel) then use this single table in order to develop the DB in Access.

 

[merlin777]

I'll see if I can be clearer - I suspect it's my English, not yours!

The group of 10 therapists will add appointments for the 25 patients throughout the day, every day of the week. (that's the bit excel doesn't like, simultaneous sharing by multiple users).

When they make an appointment they need to look at the existing appointments for the patients to avoid clashing appointments.

Once a week, their supervisor will print out 20 timetables for the patients and may occasionally print out single timetables or just a few.

So, in Access I'm intending to have one large table with an appointment per record row. I was only going to organise them into a timetable as required when someone requests a timetable (or all of them) for a particular week.

Although my excel skills are good I am new at access so I would like to avoid passing information between the two if possible.

does that help?

 

[themurph2000]

I wouldn't use one big table. You may want to split into three tables: list of patients, list of doctors, and the schedule itself. Give each patient and doctor some sort of ID number, and those IDs would be linked into the schedule table

PATIENT_TABLE
Patient ID
Last Name
First Name
(other fields that you need)

DOCTOR_TABLE
Doctor ID
Last Name
First Name
(other fields you might need)

SCHEDULE_TABLE
Appointment ID
Patient ID
Doctor ID
Date
Time
Room

Then what you can do is create a form where doctors can fill out the appointments and set up a button where it will check the date and time they enter against the schedule and say if there's a conflict. Also, not only can you set up a timeline for each patient, you can set one up for each doctor, and even each room reserved if you want.

 

[merlin777]

wow - it's like you're in my head!

I have almost exactly what you suggest: appointments, therapists and patients. I've recently added a fourth simple table containing the appointment slots so I can select them from a pull-down.

The therapist and patient tables are linked into the appointment table. I'm really impressed with how easily access automaticially turns these into pull-downs for data entry (although at the moment I can't work out how to select a whole name in one go when it's split in first name and last name fields).

My original question comes from my ignorance of access as a newbie (very steep learning curve) - I'm not sure whether to use a query or report to produce a timetable. I have a lot of fussy formatting to do the make the report look like the timetable they have now but I'm going to have to specify a person and date for the timetable and I don't have the knowledge yet to choose.

 

[Mihail]

While themurph2000 help you with this certain DB try also to help itself by reading about databases; what are and how it works.
Start by learning about normalization. After you will fully understand this concept will become a lot easier to design DBs.
Here is a very good article:
Database Normalization and Table Structures - Microsoft Access / VBA

Good luck !

 

[themurph2000]

You can make several reports tailored for the type of timetable that you want, and the majority of them can be based off of the same query. The grouping option will be a powerful tool for you if you want to devise a master schedule of patients or one for each therapist in question, and even what happens on a particular day should you desire.

If you want to make individualized schedules, you can either set up a parameter query and link the report to it (so the name of the patient will be asked for when the timetable is printed) or you can just set the report to start a new page with each patient's schedule (or therapist's or the date or whatever order you want)

 

[merlin777]

That's brilliant. I understand some of it but most importantly you've given me the keywords to follow up on for my learning research.