Diary 1

[Lewy]

I am now moving on to the next stage of my optician's application. I would like to set up a diary for appointments. The ideal set up would be for 15 minute appt slots with the facility to book double or triple times. ie a child would require a 15 minute appt and an adult 30 mins. The form needs to indicate the times, with spaces to fill in the names and addresses and appt type ie. sight test, contact lenses. Some pointers on table structure and automation of filling the times would be apreciated as I cannot quite get my head arround the concept yet!

Many thanks,

Lewy

 

[hcts]

OK:

You would need a patient or customer table that would include all the relevant data for the patient, including a status or age field. This would be keyed to a unique customer number and might have a secondary index for status or age.

I would then setup a child table that would give the user the ability to enter/modify the list of ages or status codes and how much time to allot for each one. A lookup would be setup between each table.

Another child table would be setup for appointment type, thus giving the user the ability to manage their own classifications of appointments.

Then you would setup an appointment table. This would include fields for customer, appointment reason, date, time, doctor, appointment type. You would probably key this field by date and then time slot. The time slot field would be a A2 field with 12:00 a.m as 00, 12:15 a.m. as 01 and so forth. I would probably setup a routine to create the records for the entire year or month in one shot so that the user did not have to worry about inserting or deleteing records from that table.

Then a transactional form would be created to view the appointments by day. To make that easier to navigate you would setup another table that was just keyed by day, put that in the form and data model as a table frame on a tab. Then link the appointment MRO onto another tab that was broken up by the appointments.

If you are interested, I could pobably setup an example of what I just described and email it to you.

Hope that helps

James D. Howard

 

[Lewy]

James,

Pheew! Not so simple after all! I already have a customer table keyed on a unique number. I also have a lookup table for "test type", "seen by" and I calculate the age on the form and report. The present system prints out a record card for each patient on their visit. this is an A4 sheet of paper with their name, address and personal details printed out. It also has lots of nice boxes for the practitioner to write in. I am clear on the lookup aspects of your description but get a bit lost on transaction tables and forms...I guess my brain isn't very logical!


Many thanks for your help, I shall now spend the holiday weekend mulling over your comments.

Regards,

Lewy

 

[Lewy]

James,

An example would be wonderful, however I cannot post my
e mail address on this forum, believe me I have tried. (this is to stop us all getting spam and I am grateful to the moderator for pointing this out) This leaves me with the task of getting my information from you. I have been told
"Files should be placed on the Internet and then they can just post a URL where members can download them."
Is it at all possible for you to do this?

Hope to hear from you soon,

regards,

Lewy

 

[hcts]

I don't have a lot of time right now to setup a sample, let me try to explain the concepts with what we have developed.

Go to our web site

http://www.construct-jobcosting.com/HCTS/AccountsReceivable/AR_CustomerMasterList.htm

The first two tabs contain information about the customer and the third tab shows the transactions for that customer. You can navigate from tab to tab by clicking on the tab text, (IE: clicking on History will take you to the history tab.) The customer would be equivilant to your patient database. The third tab is a transactional database that in our system includes all historical contracts, change orders, invoices, and payments. Now in our system we have seperate entry screens for each type of transaction, then we have a posting screen that puts them all into a history table. This history table is actually keyed by a incremented document number and has a secondary index for customer number. Your appointment table would be similar to this, however you would want to structure it by date and then time slot.

The important concept to understand is the difference between master tables and transactional tables. The master table contains information that you will generally put in once and seldom change. (IE the customers name, address, eye color, ect.) The transactional table will contain information that occurs on a regular basis. (IE: appointments, billings, reminders, ect..) The master table will be keyed by customer number or whatever is the unique identifier for that data set. The transactional table MUST be keyed differently. As I stated in my prior post I would probably key your appointment table by date/time slot. Other programers might work out a different, but perfectly logical system that would order the appointments in the desired order. By creating a routine that automatically inserts records into this table, you eliminate the need for the user to have to insert records, show any blank time on the screen as open. However you will also have to trap for when the user tries to delete a record, block the default behavior from taking place, and then blank out the customer information from that record.

I am guessing from your questions that you do not have any/much experience with creating databases and forms. It is going to be very difficult to create a working application without a strong knowledge of relational databases and graphical user interfaces. As you create your app, remember that Murphy's law is allways in effect with computer programing.

I will try to work on some better examples when I have some time. I'm not sure how to get them to you though.

James D. Howard

 

[Lewy]

Thanks James,

I do have a little experience in database and form design but nowhere near the level that many of you have. However I am a willing learner!! Constructing this application is, I suppose, a bit of a hobby, doing little bits now and again when I have the time. I have had no formal training whatsover but over the years have read books etc. The best help has been from this forum whereby I have learnt an awful lot. So MANY thanks to everyone that has helped me.
I am now off to look at your web link.

Thanks again,

Lewy