If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using AND or Something?
Easy one for the pros but it has me scratching my head in frustration. So
here goes. [TrainDocTbl] is indexed on autonumber field [TrainDocID]. The table includes a field [TrainDocName] that will contain 15 to 20 records. The records are for the different certifications that the employees have. Every employee has one common certification but there are employees that may have 2, 3, or more additional certification that must be tracked. The db is form based and my dilemma is constructing a query and listbox that would allow multiple selections of the certification types. I just cannot get the correct formula for choosing x and y and z. My efforts always seem to give zero results or all records. Any and all help would be greatly appreciated. |
#2
|
|||
|
|||
Using AND or Something?
"The table includes a field [TrainDocName] that will contain 15 to 20 records"
What does this mean? Fields cannot contain records. Do you mean it can have 15 to 20 different values? If a field (column) needs to contain multiple values you need to make the column a foreign key to another table which will contain the multiple records. This is called a one-to-many relationship. Your form could have a subform to set up these values. Look up Subforms in Access Help. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "gngsquared" wrote: Easy one for the pros but it has me scratching my head in frustration. So here goes. [TrainDocTbl] is indexed on autonumber field [TrainDocID]. The table includes a field [TrainDocName] that will contain 15 to 20 records. The records are for the different certifications that the employees have. Every employee has one common certification but there are employees that may have 2, 3, or more additional certification that must be tracked. The db is form based and my dilemma is constructing a query and listbox that would allow multiple selections of the certification types. I just cannot get the correct formula for choosing x and y and z. My efforts always seem to give zero results or all records. Any and all help would be greatly appreciated. |
#3
|
|||
|
|||
Using AND or Something?
Sorry I didn't parse the question correctly.
The main form has a subform. The subform displays the various certifications of the individual employee along with other data from various tables. This is done with a one to many relationship as are several other relationships in the eight tables that the db contains. The question was not intended to be a 'Form' question. What I want is a way to find which employee is certified in x and y and z and so on to include in a report or screen display. Ideally, by listbox with a checkbox to allow the user to select which certifications are required and get the result of which employees hold the desired certifications. What I need to figure out is how to choose multiple criteria from the selected table. It is a simple thing, but something that has me stumped. "Dorian" wrote: "The table includes a field [TrainDocName] that will contain 15 to 20 records" What does this mean? Fields cannot contain records. Do you mean it can have 15 to 20 different values? If a field (column) needs to contain multiple values you need to make the column a foreign key to another table which will contain the multiple records. This is called a one-to-many relationship. Your form could have a subform to set up these values. Look up Subforms in Access Help. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "gngsquared" wrote: Easy one for the pros but it has me scratching my head in frustration. So here goes. [TrainDocTbl] is indexed on autonumber field [TrainDocID]. The table includes a field [TrainDocName] that will contain 15 to 20 records. The records are for the different certifications that the employees have. Every employee has one common certification but there are employees that may have 2, 3, or more additional certification that must be tracked. The db is form based and my dilemma is constructing a query and listbox that would allow multiple selections of the certification types. I just cannot get the correct formula for choosing x and y and z. My efforts always seem to give zero results or all records. Any and all help would be greatly appreciated. |
#4
|
|||
|
|||
Using AND or Something?
I see several ways to do this. In one you would add a field to the
[TrainDocTbl] so as to pick requirements - a Yes/No field. On opening the form for this process have an update query run to un-pick all criteria. The form to have two subforms. The first to display the certifications and check box for your picks. The second to list employees meeting the certification criteria. After picking requirements click a command button to requery the query feeding the employee subform. That query would use the Yes/No field as criteria. You could get real fancy to display those that meet all criteria followed by those that meet some requirements by sorting on ABS sum of criteria met and then by name or other items. -- Build a little, test a little. "gngsquared" wrote: Sorry I didn't parse the question correctly. The main form has a subform. The subform displays the various certifications of the individual employee along with other data from various tables. This is done with a one to many relationship as are several other relationships in the eight tables that the db contains. The question was not intended to be a 'Form' question. What I want is a way to find which employee is certified in x and y and z and so on to include in a report or screen display. Ideally, by listbox with a checkbox to allow the user to select which certifications are required and get the result of which employees hold the desired certifications. What I need to figure out is how to choose multiple criteria from the selected table. It is a simple thing, but something that has me stumped. "Dorian" wrote: "The table includes a field [TrainDocName] that will contain 15 to 20 records" What does this mean? Fields cannot contain records. Do you mean it can have 15 to 20 different values? If a field (column) needs to contain multiple values you need to make the column a foreign key to another table which will contain the multiple records. This is called a one-to-many relationship. Your form could have a subform to set up these values. Look up Subforms in Access Help. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "gngsquared" wrote: Easy one for the pros but it has me scratching my head in frustration. So here goes. [TrainDocTbl] is indexed on autonumber field [TrainDocID]. The table includes a field [TrainDocName] that will contain 15 to 20 records. The records are for the different certifications that the employees have. Every employee has one common certification but there are employees that may have 2, 3, or more additional certification that must be tracked. The db is form based and my dilemma is constructing a query and listbox that would allow multiple selections of the certification types. I just cannot get the correct formula for choosing x and y and z. My efforts always seem to give zero results or all records. Any and all help would be greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|