A specific type of Database (1 Viewer)

[jimmykebab]

Hi guys! I'm new here so please be gentile.

I have a database which i have to build for a college project, most of it is done, I just have to build the a few forms and add them to the switchboard.

I'm stuck on creating a consultation/prescription page and a prescription/ medicine page, as i need these to be created from only 2 tables but need 3 tables worth of info for it to be readable.

Any ideas on how i can get round this?

Cheers

Jamie

 

[Rabbie]

Without knowing your business model It is not easy to help you. If you can give us more information I am sure you will receive some helpful advice.

 

[neileg]

I'm new here so please be gentile.

This forum is non-discriminatory. All are welcome regardless of race or religion.

 

[jimmykebab]

Thanks guys.

The tables I have are:

Consultation
Customer
Medicine
Pet
Prescription
Prescription Medicine
Vet

Here are the user requirements, I have nailed most of these just got problems with numbers 3,4 parts of 7 and most of 8.

I want to learn the process really, so general pointers would be great, much better than loads of coding. It's that, how do i work this out approach i need to develop.

1. Entry of customer details into the database and the subsequent maintenance of these details.
2. Recording of the outcome of consultations made by the vet for each customer and their pet
3. Entry of prescriptions written by the vet for the customer
4. Recording of medicine(s) prescribed in each prescription
5. Maintenance of the list of medicines available
6. Maintenance of vet details over time
7. Querying and printing of customer/pet history for a number of different scenarios
8. Printing of prescription invoices
9. A menu form to allow selection of the required process form

Thanks guys!

Jamie

 

[neileg]

If you are based in the UK, it is usual for the vet to dispense the medication at the consultation, so I would miss out the prescription stage and record the medications against the consultation. Up to you.

Bear in mind that
1) Folks are very helpful in here. Make life easy by providing as much detail as you can e.g. table structure, or the whole database helps.
2) As this is a college assignment, you'll get help and hints, but folks won't do your assignment for you.

Good luck!

 

[CraigDolphin]

Jimmy,

For parts 3 and 4 I think what you need to learn about is how forms and subforms interact using master/child key fields. You would likely need a mainform bound to the prescriptions table that has controls for all the attribute fields for that table.

Combobox or listbox controls store one piece of information but can SHOW related information from other tables. So, for example, it can store the pk from the Vet table as the foreign key value in the Prescriptions table, and it can display the vets name (from the vet table) if you use the vet table in the row source SQL of the combo. The same is true for CustomerID etc.

Now, if you also create a subform which is bound to the [Prescription Medicines] table (incidentally, avoid spaces or special characters in object or field names) in continuous form (or datasheet) view, you can add this onto the main form in a subform control. Link the form/subform using the pk of the Prescriptions table.

Now, the master/child link acts to filter whatever is shown in the subform to only those records with the same value fk as that shown in the main form's pk. And if you enter records into the subform, the db will automatically add the correct value in the PrescriptionMedicine tables fk to match the prescription pk from the mainform.

For part 8, you need to make a query that provides the information you want to print and base a report on that query. You may need to search for/learn about parameter queries to achieve your goal.

Hope this helps.

 

[Rabbie]

If you are based in the UK, it is usual for the vet to dispense the medication at the consultation, so I would miss out the prescription stage and record the medications against the consultation. Up to you.

Good luck!

Neil, Vets in this country(UK) now have a statutory obligation to provide Prescriptions if the client asks for it.

 

[jimmykebab]

Thanks very much guys! Also it states that you have to be able to make prescriptions without an appointment in some other part of the assigment.

 

[neileg]

Neil, Vets in this country(UK) now have a statutory obligation to provide Prescriptions if the client asks for it.

Quite true and I had overlooked that. However, I might consider the issue of a prescription as a different activity to the dispensing of the medication. A vet wouldn't issue a prescription if he/she was going to provide the medication (in my experience, having 50 cats and 2 dogs!).

Thanks very much guys! Also it states that you have to be able to make prescriptions without an appointment in some other part of the assigment.

I think that links with the point above. I would be thinking about a presciption as an entity that might or might not be linked with a consultation.

 

[CraigDolphin]

Another topic you may find helpful to research Jimmy, are junction tables. These allow you to build many to many relationships between entities. Remember that many means anything from 0 to zillions.

As an example, if you want to know which consultation a prescription was given, but not all prescriptions get given at consultations, you can either allow nulls in the Consultation fk field in your prescription table (often done but generally not a good practice), or you could set up a junction table that links the Consultations Pk as a FK to the Prescriptions PK as a FK.

If a prescription was given during a consultation there would be a related record in that junction table. If not, there would be no related record in that table. No nulls to have to trap for in queries or any code that you might write.

 

[MStCyr]

Thanks very much guys! Also it states that you have to be able to make prescriptions without an appointment in some other part of the assigment.

Hello

Maybe you could take a look at some of the database models that may apply to your situation ...


http://www.databaseanswers.org/data_models/catering_businesses/index.htm

Maurice

 

[Jurjen]

Another topic you may find helpful to research Jimmy, are junction tables. These allow you to build many to many relationships between entities. Remember that many means anything from 0 to zillions.

As an example, if you want to know which consultation a prescription was given, but not all prescriptions get given at consultations, you can either allow nulls in the Consultation fk field in your prescription table (often done but generally not a good practice), or you could set up a junction table that links the Consultations Pk as a FK to the Prescriptions PK as a FK.

If a prescription was given during a consultation there would be a related record in that junction table. If not, there would be no related record in that table. No nulls to have to trap for in queries or any code that you might write.

Avoiding optional foreign keys is not a good reason to work with junction tables. A junction table establishes a many to many (N:N) relationship, and as such should only be used if that is the case. If there is a zero to 1 (0:1) relationship, then an optional foreign key is actually best suited for that situation.