Forum Discussion
Return all results matching the search criterion
Hi everyone,
I have 3 tables: tblGeneralInfo, tblCap_Airflow, and tblCap_Yes/NoQuestions and a form with 2 buttons: Airflow and DER Repairs.
Here are the relationships:
- The Airflow column from the tblCap_Airflow is a multi-valued field where a Company can have many Airflow capabilities which allows the "Company Name" column to have repeated company names
I'm looking to create a form using tblCap_Airflow that is able to return all Company Names and their corresponding Details (if available) with respect to the selected Airflow value and when the "Airflow" button is clicked, the form is prompted.
Desired result:
Selected Airflow Value = CCDI massflow machine
Number of Companies = 4
List of Company Names:
1. A
2. CHR
3. ETH
4. REV
- The "Performing DER Repairs" column from tblCap_Yes/NoQuestions is also a multi-valued field with returns only Yes or No, therefore, the company names in the "Company Name" column only occur once
For this table, I'm looking to do the same things as mentioned above, however, only count and return company names that have "Yes" in the "Performing DER Repairs" column.
I tried to create a combo box for Airflow to select Airflow option but it doesn't seem to work. The drop down list contains repeated values, not unique ones and it either only returns one company name at a time not all companies or doesn't return anything at all.
I don't have any experience with Access and this is my first time dealing with it. Please can anyone show me how to go about this? Thank you so much!
- You should make three forms: one for each table. You can use the form wizard to do this. In the navigation pane on the left, locate and select one of the tables, On the ribbon select Create->Form Wizard. Repeat for the other two. Then, in the navigation pane, right click on the form you created for the tblGeneralInfo and select 'Design view'. This should display and activate the Design table tab on the ribbon. Make some more space on the form by hovering over the bottom edge and dragging it down. In the Controls panel on the ribbon, locate the Subform/Subreport control (you can click once on each control icon..leave the mouse pointer hovering over it...and the description will be displayed). With the Subform/Subreport control selected, move the mouse pointer to the Detail section of your form, press and hold the left mouse button to create a rectangle to hold one of the two remaining forms and When you release the left mouse button, a popup window appears allowing you to specify the name of the existing subform that you want to include in the form, which fields should be listed on the subform and the name of the column used to link the data to the main form. Add a second subform control and repeat the process.
- In your combobox group the data so you only have one of each type. Depending on how you plan on displaying the results, you can have the subform listing company names. Then set the filter to the "Ccdi..."
Same with the yes/no filter to "Yes" and if the results are group by you can get a count of the answers.
You can use the table as-is. Just group the data in the combobox.
