Open form based on TableA, add info, save reults to TableB

[Shag84]

Hello,
I'll try to be clear. Sometimes I'm not .
We have Cardiac Rehab patients that come 3 times a week (M-W-F). Once they are on program they stay there for 3 months. There are classes given hourly from 8am thru 6pm. There are 12 available slots per class. Right now this is being handled in Excel, but I thought I might improve things by putting it in Access.
The Excel "form" I built has fields for PatientName, MedicalNotes, DoctorName, and Notes. Because the names rarely move (a patient gets a slot and stays there) the user opens Excel, reviews the form to make sure no one cancelled-is out sick-etc, then prints the schedule. When the day is complete I have a macro that takes this information and saves it in a tablelike manner on another sheet. A dynamic pivot table references this info to give the user quick answers as to daily volume, etc.



The thing I can't get my head around is how to make this as easy in Access. My thought was I could have a PatientT, listing all of the fields mentioned above. I could have DailyF open, bound to PatientT, with additional unbound textboxes to add "today's date", cancellations or no shows, and then Save all of this in another table and print out the daily schedule.

Am I on the wrong track completely? If not can you point me in the right direction please? Your help would be appreciated.

Thanks,
Don

[Rod]

Hi Don,

Hm! Access is a very different animal from Excel. What you describe may be done in Access and done better than Excel but ...

1. You have to be prepared to think differently and be prepared to spend some time learning the basics of a relational database and normalisation.
2. The M-W-F, 8am - 6pm hourly classes, 12 slots per class may be a little awkward to set up requiring a small sub system all of its own.
3. If you want to transfer archive data then this may require some one-time effort.

Am I on the wrong track completely? If not can you point me in the right direction please?

Yes and no - that's not very helpful, is it? The patient table should contain information about the patient and only information about the patient. You may need a doctor/moderator table. You will certainly need a table for each class, yes 10 records for every M-W-F. You will probably need a small subsystem to populate this table - see 2 above - otherwise you will have to create each and every class manually, not a task I would appreciate.

The first step is to get your data design correct - that is design your database. Don't worry too much at this stage about text boxes, etc.

I'm more than willing to guide you through the initial learning curve if you go ahead. Let me know what you think.

[Shag84]

I thank you for your reply.

I'm working out the table structure now. Patient, dr, and other tables are noot the problem. I still don't know how to address the class times, but I'm wotking on it.

Thanks,

Don

[Rod]

Hi Don,

You will need two tables: one for the class itself and one for the participants.

Class table will look something like this:

ClassID (PK Autonumber) - indexed
ClassDate (Date/Time) - indexed
DoctorID (FK of doctor supervising/moderating) - indexed
Other data ...

In Access the date/time type is a large decimal number: the integer portion is a relative day number and the decimal portion is the time. Hence you may extract both the date and the time from a single data attribute. (As well as day of week, etc.)

The participant table will be either a true cross reference/join table consisting only of two foreign keys or it may attract some data such as 'Notes.' You may even want to use it for recording show/no show data.

ParticipantID (PK Autonumber) - indexed (A meaningless primary key.)
ClassID (FK of class) - indexed
PatientID (FK of patient) - indexed
Notes (Memo)
Attended (Yes/No)
...

[Shag84]

Rod,
Your class/paticipant table structure is MOST helpful. I am going to be working on this today and will get back to you soon.

Again, thank you very much for your help.

Don

[alansidman]

Don;
Rod is leading you in the right direction. It may be helpful to have some background information on database design. Here is an excellent white paper on designing databases and will help you down the road. It is not a long read, but does contain a lot of valuable information.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

Alan

[Shag84]

Thank you for the link Alan. While I have read several like it I much prefer your "condensed" version. A nice reference.

Rod, one thing I didn't mention is that we have 2 types of patients--monitored and unmonitored. Their differences are minor and mean nothing in this discussion. I had to bring it up so you will understand what follows. I would use a table like this:
ClassTypeID (PK Autonumber)
ClassType (monitored, unmonitored)

I want to understand what your are referring to. Are you suggesting I have a many-to-many relationship here? That I have a relationship like:

ClassT ClassXPatientT (your participant table) PatientT
ClassID(PK Autonumber) ClassXPatientID (PK Autonumber) PatientID (PK Autonumber)
ClassDate ClassID PatientName
ClassTypeID (FK ClassType) PatientID OtherInfo
Additional Data

Am I understanding you correctly? If so, do I have it right?

Thanks again for your help. I appreciate the nudge in the right direction.

Don

[Rod]

Hi Don,

That's a good link Alan proposed; any reading you do will help, especially as you have a real project to work on as an example. Your lists got a little jumbled since the web site dropped tabulation but I was able to sort it out. Yes, that's exactly what I am suggesting. Here it is in graphical form including your new class type table. Click on it to enlarge it.



If you are not familiar with the symbology then the end of the connection that looks like a cross means 'one and only one' whereas the end that look like a trident with a circle means 'zero to many.'

Now about this new table: it is formally correct according to data modelling and normalisation rules to separate class type out into a new table/entity. However, if you are more than 100% certain that the new table will never attract more data than class type description, it is acceptable to denormalise and simply hold the class type description in the class table. Which is better? Well it's six of one and half a dozen of the other: with a separate table you always have the bother of joining the class type table to any query to get the description whereas a separate table gives slightly easier data integrity.

The many-to-many relationship to which I referred is between patients and classes: one patient attends many classes, each class is attended by many patients. This relationship is resolved by the ClassXPatient table.

A final word about naming standards. Most of us - but not Microsoft seemingly - follow the Reddick naming standards. This would require your tables to be named tblClass rather than ClassT. It really does not matter, the important thing is to be consistent. What I would encourage however is that you avoid special characters - even undescores - in names. Use capitalisation to break up long names for easy reading. Here's a readily printable guide to Reddick standards.

http://www.dhdurso.org/articles/acce...onventions.pdf

You are certainly going in the right direction. Get back to us if you have any further concerns.