Newby-Help with relationships

[ozstar]

Hi,

Seems like the more i get into reading the more confused I get in this basic
fundamental. I just need it explained really clear and basic, then hopefully it will enter the subconcious and I will be able to proceed

I am trying to build with Access XP in 2000 version a small program for a small medical office.

Patients are offerd various services and they have vists to take advnatage of them. They are charged for the services and run an account which is then paid, leaving a balance.

I want it all to be entered via forms for older non computer literate staff.

I have done the main table okay with all the info we will need and I have messed around with the other 3 tables I think I need.

1. To enter the Services offered a description and fee.
2. Enter when they visit and what services they have and fees with total
3. When they make a payment and balance owing.

They are one to many relationships, correct?

For a start am I on the right track ?

My confusion is setting up the relationships especially what field/s has to be linked to fields in the other tables to make it work.

Is there a rock solid rule where a certain field has to be in each table and has to be linked ?

These are the first fileds in my MAIN TABLE..

Patient ID Auto Number Primary
Medicare Number Can be blank
Last Name Text
First Name Text
Address Text

SERVICE TABLE (Primary?)

Service Type /Combo- Accuncture-Massage-Herbs-Vitamins-Other
Description of Service Text
Charge for Service Text

VISIT (Primary?)

Date and Time of Visit: In case 1+ visits in one day
Reason for Visit

ACCOUNT

Date
Service Charges
Payments
Balance

I know it is basic but I need to get over this block..

Thanks in anticipation of help

Oz

 

[Malcy]

Hi Oz
This might not work through but I will have a go. The only rock solid rule about linking tables with relationships is that they have to work!!
Looking at your tables I think some more thinking perhaps needed.
A patient can only avail themselves of a service through a visit. A visit might entail one or more services?

So you need to have a Patient ID field in the visits table and then you link the Patient ID fields in the two tables - so patient 126 in visits table has got a name when you go to patient 126 in the Patients table. That seems to be step one.
Similarly if you linking accounts to patients then you do the same thing by linking on a patient id field.
Now for services. You need to have a service id field in the services table and also a matching field in the visits table. It might get tricky if more than one service offered per visit and brain too near sleep to work that one at the moment. This means that using the links you go from visits to patients to find out who it is, and then on to services to find what services.
You need to do some code to link services to accounts. If there is going to be a standard fee for a service then you can have a linking id field in services and accounts.
So sort of in summary (or at least enough to move you on a bit
tblPatients - lngPatientId links to tblVisits - lngPatientId
tblServices - lngServiceId links to tblVisits - lngServiceId
tblPatients - lngPatientId links to tblAccounts - lngPatientId
tblServices - lngServiceId links to tblAccounts - lngServiceId
I think that gets you a bit further but brain going dead from too long a day. Presumably you are fresh at the start of your one!!!
HTH
Good luck

 

[Pat Hartman]

Some advice:
1. Do not use special characters or embedded spaces in field/table names.
2. Do not use function or property names as field names ("Date" in particular will cause strange problems)
3. Do not store a running balance, calculate it as necessary.

I would change the Accounts table to:
TranID (autonumber, primary key)
PatientID (foreign key to tblPatient)
TranTypeID (foreign key to tblTranType - list of valid transaction types such as payment, service charge, adjustment, interest, etc.)
TranDate
TranAmt

This type of table structure will allow you to handle ANY type of financial activity for a patient's account without special programming. It will also provide an audit trail that won't be available if you try to store balance information. You can use a DSum() to show the account balance for a patient.