Help!!!! Problem with form in Access.

[IfButOnly]

Elhan,

I think we are going to need to clean up your tables a bit - so a few questions.

a) Can a Patient have more than one Insurance Agent - you have a direct link to Insurance Agencies table in your patient form (which implies one) but you also have a Patient/Insurance Agency link table (which implies many)

b) your links to Doctors is confusing - do you want a patient to have multiple doctors assigned to a single treatment? Is the doctor link to the patient or the treatment?

c) Why do we have Treatment1 and Treatment2 tables?

d) Could you explain the link between Patient-Treatment-Cost?

Peter

 

[IfButOnly]

Elhan, Is the file you sent me a full version or did you cut it down?

If full version you need to compact it as there is a lot of bloat in it. Do this by:

Tools/Database Utilities/Compact and Repair Database...

or preferably, each time you exit the application, by:

Tools/Options/General/Compact on Close..


Peter

 

[Elhan]

Okay I compressed it as you said and I am adding it as an attachment here. Thanks for the tip

a) A patient may only have one insurance agency at a time, but one agency may have many patients. The Patient/ Insurance Agency table is part of the normalisation process...perhaps the type of relationship specified is wrong though..it should be one agency to many patients.

b) Doctors can have many patients, but a patient only one main doctor.

c) The reason that there is a Treatment 1 and 2 table is to create two seperate queries to make the form/ subform. I'm not sure if thats the right thing to do though

d) A treatment will involve data on the patient (for the main form) and cost data (for the subform). Thus the treatment is made of data from the patients and cost tables. Makes sense?

I think perhaps what I will do is create a Treatment table containing all fields necessary for the main form and delete the queries. Apparently, forms based on queries containing multiple tables don't allow the user to arbitrate data (so I have been told). In any case, continue looking at the project and see what can be done. If need be I will try and create a table for it. I appreciate all your help so far

PS : I posted a more compact version of my full project. Take a look at this Peter, it should be more complete.

 

[IfButOnly]

Elhan,

I think we are nearly there. a couple more questions -

a) can a patient have multiple treatments?
b) Is the cost of a treatment always the same or is it based on the patient. Do some patients get discounts?

Peter

 

[Elhan]

Hmm yes a patient can have multiple treatments.

Generally the cost of the treatment is always the same. The patient only gets a 5% discount if their account is above £250 and a 10% if they are not insured.

Have you managed to determine yet what the problem is?

 

[IfButOnly]

Elhan,

The problem is with your database design and my last few posts have been to ascertain what it should look like to help you move along.

Now if costs for a treatment are always the same, why do you need a combo box to select cost. I suspect this might be a terminology thing, but I am assuming that the subform should show the treatments the patient has received and the costs involved - is this correct?

Couple of points so far:-
.. Don't have spaces, brackets etc in field names;
.. you don't want the Doctor & Insurance agency link tables;
.. DoctorID should be a lookup field in tblPatients

By the way, your form design is looking good - however I had trouble working out what your pic was - I thought it was some disgusting internal organ, not a flower.

Once I understand what you are trying to achieve with Treatment/Costs then it should all be pretty straight forward.

Peter

 

[Elhan]

Hmm the subform is meant to allow the user to select costs from a list (the combo box) and add them on. It is thus used for cost selection. You choose a cost (say medication) and add it to the subform. The subform is primarily made up from the Costs table. As such, you are correct. The subform is meant to show what treatments and other costs a patient must pay for. The costs subform will only add items from the Costs table (which includes various treatments). In the end all costs are added and a total is generated. So is it clearer now?

When you mean a lookup field do you mean it should allow the user, when using tblPatients, to select doctors via lookup? That would be a good idea I think.

As for the forms, thanks The pic is trivial right now. What I want to do is get the Treatment form to work. Make any changes you see fit.

PS: By tomorrow I need to start on the Design section of the project, meaning I will have to list the tables, forms etc in a write up and show how forms look approximately etc. So I will try and finish it as far as possible for now, but I'll need the project to go any further. I can't really use the much of the weekend as I have an exam on Monday, so today and tomorrow will be my main working days. Have you managed to get any further Peter?