Create query with field based on combobox

[Dale040205]

I have a table named tblCatergories. It has a primary key field and fields Month_Date, Squadron (which is a combobox that lookups the values from another table), and 21 other fields with names of issues where each month the users put the number of each issue they encountered for each squadron for that month. I have queries and reports that allow me to see the number for all issues for each unit for each month, but now I'm trying to create a query for a report that will show a trend report over a specified date range for a specific issue. I have a form with a combobox with a value list of the issues in tblCategories. I'm trying to make a query with a field that will change based on what is selected in the combobox of the form. I have not been able to figure out how to design the query or work anything else out that will achieve the desired results. I hope this makes sense and thanks for the help!

[Micron]

reference the form control in the criteria row of the query design grid. As long as the control is not on a subform,
Forms!cmbComboName
If the combo box values being passed/used are text, nulls can present a problem. You can work around this several ways:
- enforce a selection (use validation on the property sheet for the control) or code that runs on a button click (or whatever event you're using to run the query)
- use an IIF or NZ expression in the query criteria to supply a default value
- use a numeric id for the "issue" text, which means the combo box should be based on a SELECT statement or query that returns a row id (such as an autonumber) AND the problem text. The bound field of the control would "pass" the number to the query instead of the text, but the output would be the same as long as any joined table fields are compatible with the number.

[June7]

So you want to filter records?

Dynamic parameterized query is one method but I prefer http://www.allenbrowne.com/ser-62.html

[Dale040205]

Unfortunately using criteria doesn't work. All that does is search for records that match that criteria, not limit the fields used in the query to match that one selected in the combobox. I apologize for not making it clear that is what I wanted to do. The 21 issue categories, though, are field names in the underlying table and I am trying to find a way to force the query to only use the one field selected on the form. The end result of the query would include three fields (Month_Date, Squadron, "Issue"), not the 20something the original table has. The example from Allen Browne doesn't have a way to change what fields are included in the resulting filter either.

[June7]

Fields selected in query object cannot be dynamic - unless you want to write VBA code that modifies the query object - a field is in the query or it isn't.

Sounds like your data structure is not normalized.

[Micron]

...not limit the fields used in the query to match that one selected in the combobox.

Sounds to me like the tables are not normalized properly, or the form/report design isn't right. The idea usually is not to alter the number or selection of fields in a query depending on some selection, rather the number of rows where the results fit the criteria. You might have to post a copy of your db for us to help, else I'm thinking you'll need a crosstab query to get what should be in rows to display as columns. Sorry if we're not quite getting the drift, but it seems both June7 and I are not following 100%.

[Dale040205]

Sample.zip Here's my sample database. There are some other components I'm still working on that aren't finished and/or don't do anything yet.

[June7]

You have circular relationship. tblGrps and tblSqdn should not have link line. The tblCategories defines associations between tblGrps and tblSqdn and therefore luGroup should not be in tblSqdn.

However, tblCategories does not appear appropriate. I presume a squadron can be associated with only one group. Will squadron ever change to another group? Consider:

tblGrps
Grp_ID_PK
Group

tblSqdn
Sgn_ID_PK
Squadron
Grp_ID_FK

tblIssues
Data_ID_PK
Sqn_ID_FK
IssueDate
IssueDesc

This is normalized structure and allows any number of issues for each squadron for a date, from 1 to however many are needed. There would be no blank fields.

Recommend not building lookups with alias in tables. Build combobox on form. Use form/subform arrangement.

[Dale040205]

June7,
I really appreciate your suggestions, and some of them I intend to implement. For your suggestion for tblIssues, I don't see a field for inputting the quantity of each issue the unit had for that month. Also, the reason I chose the table structure I did is because currently my users open a form, enter the month for the data they're recording, select the unit, and then enter the quantity of each issue for that month. Some users have many squadrons they report on, and it would get cumbersome if they have to input 23 records for each unit for each month. They way I have it set up they only make one record for each unit for each month. I don't mind the number of records being created, but is there a way to design the form so that they only have to enter the month and unit only once and then have fields available for each issue for the user to tab through as they enter the quantity? That is the biggest hurdle that caused me to design my table the way I did. I will definitely re-examine my relationships and incorporate your suggestions.

[June7]

So your 'raw' data is already summary statistics. A field for each issue is not normalized structure. You are already seeing difficulty with output - hence the original reason for this thread. Fields selected in a query cannot be dynamic.

There is a standard set of issues that are reported on each month?

Modify suggestion:

tblIssues
Issue_ID_PK
IssueName

tblUnitIssues
Data_ID_PK
Sqn_ID_FK
Issue_ID_FK
ReportYear
ReportMonth
Quantity
Comment

Normalize structure as suggested and code can facilitate the input of 23 records so that users won't have to enter the unit and year/month for each record. http://access.mvps.org/access/forms/frm0012.htm

Or further normalize structure and split tblUnitIssues into two tables:

tblUnitReport
Report_ID_PK
Sqn_ID_FK
ReportYear
ReportMonth

tblUnitReportIssues
Report_ID_FK
Issue_ID_FK
Quantity
Comment

Use form/subform arrangement for data entry/edit.

IMO, it is a balancing act between normalization and ease of data entry/output. If you will want to do statistical analysis of your data, I think the non-normalized structure will be more cumbersome.