Match table Fields to Another table Column Field List - Form Filters

[misscrf]

Sorry for the confusing subject. I will try to explain the structure, as basically as possible. I have a main "project" table - tblProject. There are 12 int fields that are used as check boxes on the data entry form, to check off if that option is being used in the project. intone, inttwo, intthree. Their labels are way longer and more involved, eg. Option One: Team would like to have conveference room availability. This relationship of options in the project are one to one, so it didn't make sense to put the options in another table.

I now need to make a report menu, which actually runs the report as a spreadsheet view query in a subreport object on the report form. I have a listbox of the projects that the user can select one or more. If they select none, then there is no filter. If they select any, the report filters to those projects.

In order to allow me to make a listbox of the options on the report menu, I set up a table of these options, called tblProjectOptions. These are the fields:
ID - (int/identity field/primary key)
NumOrder - (int) an ordering column for the options
ProjField - (nvarchar(255)) the field names from the tblProject (intone, inttwo, intthree)
ProjDesc - (nvarchar(255)) the description of the option (matches the labels on the data entry form)

I can use the ProjDesc to populate a listbox on the report menu, but then I need to be able to use some vba to match the projfield with the field name in the tblProject, to see which records have those options checked.

Does this make sense? I am open to suggestions.

Thanks


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You need to read up on normalization to put data in your table and not in your field names.

However, you can create a normalizing union query to match up with your tblProjectOptions. The following might allow you to join the OptNumber field to NumOrder in order to find the matching projects.

SELECT ProjectID, 1 as OptNumber FROM tblProject WHERE intOne Is True
UNION ALL
SELECT ProjectID, 2 FROM tblProject WHERE intTwo Is True 
UNION ALL
SELECT ProjectID, 3 FROM tblProject WHERE intThree Is True
UNION ALL
--- etc ---
SELECT ProjectID, 12 FROM tblProject WHERE intTwelve Is True

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks, Duane. This is normalized. The options are one to one, and belong in the main table. The style of report menu I do allows for users to filter data based on multi-select list boxes. To present these check box options in a way that is easy for them to filter, I wanted to set up a multi-select listbox with their "nice" names, and have that allow me to match the options to each check box (int data type). I ended up solving it like this:

If Me.lstProjOptions.ListIndex <> -1 Then

    If Me.lstProjOptions.Selected(0) Then
        strCriteria = strCriteria & _
        " AND [Option II] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(1) Then
        strCriteria = strCriteria & _
        " AND [Option III] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(2) Then
        strCriteria = strCriteria & _
        " AND [Option IV] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(3) Then
        strCriteria = strCriteria & _
        " AND [Option VI] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(4) Then
        strCriteria = strCriteria & _
        " AND [Option VII] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(5) Then
        strCriteria = strCriteria & _
        " AND [Option VIII] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(6) Then
        strCriteria = strCriteria & _
        " AND [Option IX] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(7) Then
        strCriteria = strCriteria & _
        " AND [Option X] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(8) Then
        strCriteria = strCriteria & _
        " AND [Option XI] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(9) Then
        strCriteria = strCriteria & _
        " AND [Option XII] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(10) Then
        strCriteria = strCriteria & _
        " AND [Option XIII] = " & """" & "Yes" & """"
    End If

    If Me.lstProjOptions.Selected(11) Then
        strCriteria = strCriteria & _
        " AND [Option XIV] = " & """" & "Yes" & """"
    End If

There are other filters, but that is this direct solution.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

This is normalized" really? If "12 int fields that are used as check boxes on the data entry form" relate to 12 fields in a table then IMO this is not normalized. Apparently you are tied to 12 options and would need to modify tables, forms, queries, reports, and code if you need to vary the 12.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

How is that not normalized? If I am wrong, please explain. why does it matter that there are 12 int fields? I have 12 boolean options that are each 1 to 1 relationship with the primary table. What should I be doing differently?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Again, what happens if you change the options? Your application design is tied to a developer. data belongs in your tables, not in your code.

I don't think this is much difference from an orders and order details where your project is the same as the orders and your options are the same as the order details (products). You would never create a field for every different product.

I would create a junction table of project options. Nothing different would satisfy my requirements/specifications.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

How is that not normalized?
Violation of 1NF.
Have a look here:

http://www.r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

The options won't change. These aren't app options. They are set options that a project team can choose for a project.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

According to that link, PHV, it is not directly stated, but all of my project team options are one to one relationship with the project record. There is no underlying reason to put those set options into another table, unless I feel it is too many columns in the project table, which I don't.

One-to-One Relationships

Two tables are related in a one-to-one (1—1) relationship if, for every row in the first table, there is at most one row in the second table. True one-to-one relationships seldom occur in the real world. This type of relationship is often created to get around some limitation of the database management software rather than to model a real-world situation. In Microsoft Access, one-to-one relationships may be necessary in a database when you have to split a table into two or more tables because of security or performance concerns or because of the limit of 255 columns per table. For example, you might keep most patient information in tblPatient, but put especially sensitive information (e.g., patient name, social security number and address) in tblConfidential (see Figure 3). Access to the information in tblConfidential could be more restricted than for tblPatient. As a second example, perhaps you need to transfer only a portion of a large table to some other application on a regular basis. You can split the table into the transferred and the non-transferred pieces, and join them in a one-to-one relationship.

I don't see a violation of 1NF.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

The options won't change." I wouldn't rely on this. If your tables were normalized, you wouldn't be here asking a question. Normalized tables provide much greater functionality and don't require repeating sections of code to work around your un-normalized structure limitations.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

my tables are normalized, and I have a relational schema. You have always been helpful to me, so I don't want to seem ungrateful, but it seems you are attacking my database design when you don't know the design. I shared part of the design in my question, and I tried to be clear. I have a tblProject table. It has and ID identity field and other unique project information. There are 12 unique project options that the project team turns on or off for their project. Without you knowing what a project is, or what the options are, I don't understand how you can be so certain that there will be more or less options in the future.

I've already designed this app and it has been running for a while. They just got to the point that they wanted to run some reports. They way I set up reports is not through reporting, but with forms that allow for 100's of "reports" to be run. The report form has list boxes for filters, and a list box for the fields they want in their report. The vba builds a where criteria out of any list box, check box, and text box filters, and builds a field list for the select and group by, out of the fields list box. This is all displayed in datasheet view in a sub form object.

For this specific thing I was working on, I wanted to do, was represent the "nice" label name of the project option check boxes in a list box. Then refer those to the check box options for filtering. The 3rd post in this thread is the solution I came up with, so I am all set. If we had more time, and my employer didn't mind me disclosing our information (hahaha), I would be happy to show you the schema. I do think I have explained enough that you can stop accusing me of not normalizing my data. Some may like to split out one to one relationships, to keep tables small. I don't, if I can avoid it.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

It's your option to continue with the path you have chosen. PH and I don't think it is normalized but it's your database.

Hopefully your options will never change. I always assume they will when creating a solution for a customer (even though they insist they never will).

I think my original reply could have also been a good solution but if you are happy with yours, then use it.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

yeah, these aren't options that can change. It's just not that kind of a situation. I appreciate your looking out for the longevity of the database.

misscrf

It is never too late to become what you could have been ~ George Eliot