Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: How to search sub form records from Main form?

    The situation is like this:

    I am designing a db for a dental clinic. I created 3 tables as follows:

    1. T_Patients (P_Code(PK) and other fields)
    2. T_Diagnosis (P_Code(Fk), ToothRecId (PK) and 32 text fields for 32 tooth named (UR1 to UR8, UL1- UL8 for Upper left / right Arch and LR1 to LR8, LL1 to LL8 for Lower left / right Arch)
    3. T_Pmts (P_Code (FK), PmtRecId(PK) and other fields

    I created one main form based on T_Patients tbl and similarly another 2 forms from other 2 tables. In the main form I placed 1 sub-form and 2nd sub-form. Bothe the sub-forms are Master / Child relationship on form over P_Code field in the main form.

    Single character length has used for all the 32 text fields in table T_Diagnosis. So only one single B, C, D, F, M could be entered.

    Normally we search patient’s profile by patient’s code or by name that displays in the main form. But the client needs to search patient’s record by tooth criteria.

    There may many tooth fields in the first sub-form that are null and only some of them with any one of the B, C, D, F, M character. Bcz there is very rare chances of damaging all 32 tooth of the patient.

    Now my clients want to search based on the data which are B, C, D, F, M characters. Means once he types C (stands for Cavity) in a search text box then it should bring all the sub-form records that hold value C in any of the 32 fields.

    1. How can I do this when the search based on SUB-FORM and not over the main form?
    2. The entered criteria value might by in other field also. Like say if entered C then this value may be in any of the 32 fields. So how it would bring all records?

    I am confused how to do it. I attached a pic of my form for your ready ref.

    I would really appreciate if somebody could help me.

    With kind regards,
    Ashfaque
    Attached Thumbnails Attached Thumbnails MainWith 2 SubFomrs.JPG  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...then I suspect you have a problem with your existing table design.
    You may need to replace/redesign T_Diagnosis so that each tooth has a separate record associated with a specific patient. Otherwise it will be a pig (it can be done) trying to query the db based on 32 teeth with possibly 4 parameters. Failing that you are going to have a longwinded where clause eg:-

    where UR1="C" or UR2="C".....etc....

    a dirty fix might be to store a summary field/s whihc indicate if a patient has any of the 5 requested settings in any of the 32 teeth eg:-
    add 5 boolean columns to T_Diagnosis representing your 5 states
    HasCaries
    HasFillings
    Has......
    so you store the vlaue at individual tooth level AND the summary level. That way round your query is one one column, if the dentists requires records which exhibit both "C" & "M" its a breeze. With you current design it would be where UR1 in ("C","M") or....... as opposed to where HasCaries=TRUE or Has....= TRUE

    mind you I suspect you may have a problem already, unless the dentist is only interested in the current state as at the last examination, and not worried about the history of the patient.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks heldem,

    Sorry I did not understand.

    Do you mean that I have to creat each single table for each teeth?

    Here I attached my db for your reference. It is only trouble some parts from the whole db.

    Please try running F_PatientMaster and then F_PatientDiagMainReg.

    I created some of search facility with diffrent criteria which you will find on F_PatientMaster form and stuck up when it comes to the tooth searching requirement.

    There is dummy intial data that can be modified or update. If you or someone beleive it really needs to change the design then what it should be? Please correct me if I am wrong.

    I will appreate the help.

    Regards,
    Ashfaque
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Sorry,

    I forgot to include the pictures there that might give trouble in opening 2 forms.

    healdem, if I you think that I need to design a new table...what would be the columns included?

    I will really appreciate If you please do it for me n load it here. I am stuck up because of it.

    With kind regards,
    Ashfaque
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    Arrow

    Quote Originally Posted by Ashfaque
    ....Do you mean that I have to creat each single table for each teeth?....

    No I think you have 2 options
    either a new table below diagnosis which contains the overview of the diagnosis (eg date, time, dentists etc..), then a sub table toring all the results for each tooth which uses the diagnosisID from tblDiagnosis. So the design would look something like:-

    tblDiagnosis
    P_Code(Fk)
    add the following boolean (yes/no)....
    hasCaries default no
    hasFillings default no
    has......

    tblToothDiagnosis
    DiagnosisID (composite FK with ToothID)
    ToothID text 3 EG UR1
    Result text 1 EG "C"
    ...that way round in tblToothDiagnosis you would only have the teeth whihc had a problem, if the tooth was clear it woulndt appear in the table

    ToothID should probably be a foreign key in say tblToothType to limit the toothID in tblDoagnosis to approved types
    Result should probably also be a foreign key is say tblResultype to limit the diagnosis to "C"....... whatever

    OR

    add 4 columns to your existing tblDiagnosis
    hasCaries, hasFilling....whatever

    these hasxxxxx columns would be set if anyone of the teeth had caries or fillings or whatever detailed in the other 32 columns. Personally I dont like this design but it may well meet your requirements. This way round when you dentisit needs to find patients with caries and fillings then where hasCaries=True and hasFillings=true.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •