Overcoming another author's failure to normalize.

[ch5150]

I am trying to develop a db originally written by someone else that has a series of fields in tblOperatorReports labeled RawPartNumber1, RawPartNumber2, etc... to RawPartNumber 14. In the form they present themselves as a single column though in truth they are 14 separate columns. Now however, the client wants to be able to search/report based on the contents of that "field/column". I have tried several things to solve the problem but in each case the solution was not workable and I have had no success... I have some experience w/ the object oriented aspects but not great at sql or vba and have come to a brick wall in terms of options to overcome this problem. Any assistance would be GREATLY appreciated.
Respects and Regards,
Chris

[burrina]

What do you mean by single column? A ComboBox ? You want to be able to search a Report or on a form? I have no clear idea of what you really have.

[June7]

Do you mean a single UNBOUND combobox used as input for filter criteria?

The only approach I can think of is a parameterized query where the combobox is referenced under each of the 14 fields, each on a separate Criteria row to invoke the OR operator (looks like stair steps).

[ch5150]

I'm uploading a screen shot of the form. The column on the left labelled Supplier Part # is a column of fields labelled RawPartNumber1-14. The end user wants to be able to search on the entries in those fields. In this instance only the top field is populated, but often there are up to five fields populated.

[ch5150]

One of the solutions I tried was to use an append query to move the data into a separate table with only workorder number and rawpartnumber fields but couldn't resolve the name issue between SELECT RawPartNumber1 (-14 )
INSERT (RawPartNumber)

[ch5150]

Could you please expound further on this resolution please?

[ch5150]

Further, the end user is not concerned with historic data... only with developing the capability moving forward. So a structural change to correct the failure of the previous author would not be out of the question. However, I created a subtable/continuous subform but that has not worked due to the workorder record is not created in tblOperatorReport until the form is closed so there is nothing for the subform to attach to...

[June7]

The description "Supplier Part # is a column of fields labelled RawPartNumber1-14" makes no sense.

I strongly suggest you change the structure.

Properly related tables and properly designed form/subform should facilitate data entry/edit. Enter new record (or edit existing) on main form then move to subform to enter related records. It works. The failure you experienced possibly due to improperly structured query used as RecordSource on main form.

The earlier suggestion was offered as a means to filter a report, not aid data entry/edit.

[ch5150]

You are absolutely right, it makes no sense. Somebody else authored the original program and didn't believe in normalization apparently. On the form it appears as a column of "Supplier Part #"... But in the underlying table it is actually 14 separate fields named RawPartNumber1, RawPartNumber2, etc. You suggested a means of filtering or reporting, I would be happy to pursue that, you are also recommending a change in structure. That is okay with me. The end necessary is to be able to search and call up records based on the contents of the "RawPartNumber" series of fields. Any means of getting to that result is acceptable. What I am hoping for is specific recommendations to achieve that end.

[ch5150]

Screen shot with the actual field names inset. I just looking for suggestions on where to go from here to either search the existing data or to restructure to capture the same data in a form easier to mine.

[June7]

To search the current table structure, a parameterized query as described earlier should work. What do you not understand about the described query and filter method? Did you open the query designer and attempt?

Really should normalize. What do you not understand about normalization?

[ch5150]

To address the first issue, parameterized query. I include a screen shot of what I believe you are recommending.
Second issue, I attempted to normalize by creating a sub table and attaching it via a subform. However, my subform doesn't work because the underlying table doesn't create a record to attach to until the form is closed... At least that is my belief of why the subform won't allow me to add records to the subtable.

In retrospect, I think you are suggesting to setup the query to ask for the parameter and then sift the fields for a matching case?

[ch5150]

Or is this the direction I should be going? I'm sorry that I'm not more adept and experienced at this and just am a couple of inches or so out of my depth...

Attachment 18576

[June7]

The query parameter can be a popup input prompt but I never do that. My suggestion is to use a control on a form for criteria input and reference that control as the query parameter, like: Forms!formname!controlname

Your attachment errors as 'invalid'.

[ch5150]

I am not proficient at parametrized queries. I would like to construct one that can return the contents of the RawPartNumber fields combined with the work order number that would allow me to set a doubleclick command to open the reports were the part number is present. I just don't know how to construct the query.