How to search sub form records from Main form?

[Ashfaque]

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

 

[gemma-the-husky]

sounds like you really need another table to hold attributes for the patients 32 teeth, not 32 columns of data as you have at present.

 

[Ashfaque]

Sorry,

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

gemma-the-husky, 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

 

[uplate]

I agree with gemma-the-husky that you should probably consider normalizing your design. However, I couldn't resist this quick-n-dirty solution:

If you really want to have 32 fields, then how about creating a calculated field by concatenating the 32 fields (e.g. All32: [UR1] & [UR2] & [UR3] ...). Then, when they type a search code, simply filter the calculated field (WHERE All32 LIKE '*' & Me.txtSearch & '*'). (yuk - but it might be an easy fix) (by the way, you might want to update all nulls to spaces to prevent errors)