Query only showing values that are not used by other tables

[William_Ch]

I'm creating a dental program for a project and I have a database to go with it. I have a query that is supposed to show all of the teeth of a patient so that they can be edited, however it doesn't actually show all of the teeth of a patient. If another patient is having something done to a tooth that the original patient isn't having done, then it won't show that tooth e.g if patient 5 is having something done to Ur4 and patient 1 isn't, it won't show Ur4 in the list of teeth for patient 1... I'm really new to access and queries so this could be a simple fix

Does anyone have any ideas on how this can be fixed?

I've attached the database I'm using, the password is..password...

WillCDatabase.zip


Thanks

[June7]

DOB should be in a date type field.

Which query?

If you want to show all teeth for a patient, then need records for all teeth in tblPatientTreatment.

[William_Ch]

Oh yes, sorry I forgot there were other queries. The query named treatment for patient.

[June7]

Doesn't change my advice.

Including tblTeeth with a LEFT JOIN will not show all teeth for a patient. This does not create a record in tblPatientTreatment for editing.

Why have both Treated and Date_treated fields? The yes/no Treated field is superfluous.

[William_Ch]

Okay, so how would I go about getting all of the teeth into the PatientTreatment table?

I'm interfacing this database with visual basic so having a boolean of Treated to tell whether a tooth had been treated or not is easier for me to code rather than checking whether the date of a treatment is before the current date. Some of the dates for treatment are also in the future so they obviously haven't been treated yet. I was supposed to change the Date_Treated field to Date_of_Treatment...

[June7]

Options:

1. Normally, records would only be created when there is data to document. If a tooth has no treatment data, why have a record? Create records in tblPatientTreatment with data entry. Selecting tooth from combobox will initiate record.

2. Run an INSERT SELECT sql action to add a complete set of teeth records for patient - additional automation can be programmed with VBA.

INSERT INTO tblPatientTreatment(Patient_ID, Tooth_Name, Date_treated) SELECT [Enter patient ID] AS Patient_ID, Tooth_Name, Date() AS Date_treated FROM tblTeeth


However, that doesn't include all surfaces for each tooth. In a normalized db structure, there would be another related table for documenting treatment for each surface which would be dependent on tblPatientTreatment. This is one situation where might be more convenient to stop normalization at the tooth level and have 7 fields in tblPatientTreatment for the surfaces.

[William_Ch]

Right...

Having all of the teeth is more of a visual thing. The user was supposed to be able to view all of the teeth for a specific patient, and then choose what they wanted to do to a particular tooth e.g choose the surface of the tooth being treated, the condition, the material etc

Hopefully that make sense as to why I want all of the teeth to show.

[June7]

Yes, I've seen the computer display at my dentist. It has a graphical display. Very sophisticated program.

Essentially, every tooth is examined and evaluated so each has a record in examination details. However, if there is no treatment (filling, crown, etc) then I am guessing there is no record in treatment details.

Is this db intended to be applied in real-world situation? Reinventing the wheel may be more costly than buying fully functional app.

[William_Ch]

No it doesn't have to be used as a real world program. It's supposed to be for a computing project however my teacher kind of backed me into a corner... He said it would be a good project to do however it's way over my head and I just don't have the coding or database knowledge to pull it off. My teacher doesn't know how to do it either.. This is the less complex version...

[June7]

Then make it as simple or as complex as you want. How tough is the instructor for grading - is full normalization a big deal? You will have to decide what needs to be done to achieve desired grade.

Simplest is option 1.