Needing Help with Forms and Queries.

[ChrisBush]

I have not used access much in the past and I have just been given a task to provide a solution to a problem. The problem is that a University has a lot of members of staff in one department. Each one of those members of staff teach quite a few Modules (Subjects) and each Module is taught by a few members of staff. So far I have three tables in my access database. I have a Staff table, with the fields as Staff ID, Staff Name, Email Address, Phone Number, Room Number. I also have a Module table. The fields in this table are Module ID, Module Name, Description, Skills Taught, Module Code(The official University Code). I also found out that I needed a third table to link these two tables together so I named this StaffModule. Inside this StaffModule table I have the StaffModule ID, the Staff ID (which is linked to the Staff table) and I also have a Module ID (Which is linked to the Module table). All of my tables now have been filled with the correct information and data and now all I need to do is the search/filter part using a Form. I have a form with Three Labels. These three labels are "Module Name:", "Staff Name:" and "Keyword:". Each of these labels have a text field next to them. What query would I need to solve this problem:

"The university would like an access form to be able to filter through the database and produce the data based on a keyword search. So for instance, if the university types in the name of a member of staff then all of the information related to them will be shown as well as the modules they teach. Another example would be if a module name is searched, then all of the information for that module would be shown as well as the names and information of the staff members that teach it."

Please remember I have not used access much before

 

[alansidman]

Here is a tutorial on search forms. Build your query then build the search form. You will need to join your tables on common IDs and bring in the fields that you want in the queries. Depending on your needs you may need more than one query.

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

 

[ChrisBush]

Here is a tutorial on search forms. Build your query then build the search form. You will need to join your tables on common IDs and bring in the fields that you want in the queries. Depending on your needs you may need more than one query.

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

Thanks for your help. My problem is that the query in that video only shows information from one table. I'd like the information that is shown to be from two tables. So say I type in a Module Name into the text field and click the "Run Query" button, how do show I the members of Staff that teach that Module if they are in a separate table?

At the moment I can get all of the information from that module to show up but not the names of the members of staff that teach it.

I have linked the two tables using a third joining table as mentioned in the original post. I just don't know what to do now.

 

[alansidman]

You need to bring all three tables into the QBE and drag the fields you want onto the grid.

Watch here Create a Multi-table Query with Sorts - YouTube

 

[ChrisBush]

Thanks for your help guys! Finally managed to get it done So happy at the moment.