Question Microsoft Access Database Help (1 Viewer)

ChrisKipper

New member
Local time
Today, 19:18
Joined
Jan 17, 2013
Messages
6
I am completely new to Microsoft access and I have a task at University to create a database that holds the information of members of staff, modules they teach and skills that the staff members might possess. So I have learned to create two tables in Microsoft access.

One table holds the information for a member of STAFF, such as Staff ID, Staff_Name, Staff_Email_Address, Staff_Telephone_Number, Staff_Room_Number.

Another table holds all the information for a MODULE. Such as Module_ID, Module_Name, Module_Skills, Module_Description, Module_Code.

As more then one member staff can teach more then one module and more than one module can be taught by one member of staff I'm not sure if I need a third table to link the two or how I go about doing this.

Our second task was to be able to use a Microsoft access form to input a word, (such as a staff members name or a module name) and on the click of a button a query would run and display the all of the fields of information about that module as well as the names of the members of staff who teach this module.

Any help on doing this would be greatly appreciated. Thank you.
 

Vassago

Former Staff Turned AWF Retiree
Local time
Today, 15:18
Joined
Dec 26, 2002
Messages
4,751
I would recommend in the future you post adequate titles to suggest the help you need. A lot of longtime members ignore titles that are not descriptive enough.

For starters, I would recommend a third table that treats the other two tables as reference tables as you suggested. This one would house the Staff ID and then the Module IDs that pertain to that member in separate rows.

For the form, you can have a form with a combo box is referenced by a list box to display the staffers that are applicable to that combo box based on this third table.
 

ChrisKipper

New member
Local time
Today, 19:18
Joined
Jan 17, 2013
Messages
6
I would recommend in the future you post adequate titles to suggest the help you need. A lot of longtime members ignore titles that are not descriptive enough.

For starters, I would recommend a third table that treats the other two tables as reference tables as you suggested. This one would house the Staff ID and then the Module IDs that pertain to that member in separate rows.

For the form, you can have a form with a combo box is referenced by a list box to display the staffers that are applicable to that combo box based on this third table.

I apologise for the thread name. So say I name my third table "Staff_Module" and have the fields set as StaffModule_ID, Module_ID and Staff_ID (Module_ID and Staff_ID are both looked up from their own tables?)

So for example my rows would look like:
StaffModule_ID Staff_ID Module_ID
1 1 1
2 1 2
3 2 2
4 3 1

If so, then I understand that bit so far. I'm not quite sure what you mean when you say "you can have a form with a combo box is referenced by a list box to display the staffers that are applicable to that combo box based on this third table." So far the only thing I have on my Search Form is Three Labels, "Staff Name:", "Module Name:", "Keywords:" and text input boxes next to each with property names of "NameSearch", "ModuleSearch" and "KeywordSearch"so on the click of a run query button I can search through the database using criteria query Like "*" & [Forms]![Search Form]![ModuleName] & "*", with obviously the last section changed for each text input box. I could drag a combo box onto the form but I wouldn't know where to start with referencing it to a list box to display the staffers as I have never done this before. Thanks for your help.
 

Vassago

Former Staff Turned AWF Retiree
Local time
Today, 15:18
Joined
Dec 26, 2002
Messages
4,751
I apologise for the thread name. So say I name my third table "Staff_Module" and have the fields set as StaffModule_ID, Module_ID and Staff_ID (Module_ID and Staff_ID are both looked up from their own tables?)

So for example my rows would look like:
StaffModule_ID Staff_ID Module_ID
1 1 1
2 1 2
3 2 2
4 3 1

If so, then I understand that bit so far. I'm not quite sure what you mean when you say "you can have a form with a combo box is referenced by a list box to display the staffers that are applicable to that combo box based on this third table." So far the only thing I have on my Search Form is Three Labels, "Staff Name:", "Module Name:", "Keywords:" and text input boxes next to each with property names of "NameSearch", "ModuleSearch" and "KeywordSearch"so on the click of a run query button I can search through the database using criteria query Like "*" & [Forms]![Search Form]![ModuleName] & "*", with obviously the last section changed for each text input box. I could drag a combo box onto the form but I wouldn't know where to start with referencing it to a list box to display the staffers as I have never done this before. Thanks for your help.

You can assign queries to a list box. Or you can create your own continuous form to display the data as if it were a list.
 

ChrisKipper

New member
Local time
Today, 19:18
Joined
Jan 17, 2013
Messages
6
What would this query be for the list box so that I could show the modules that are linked to a certain member of staff.

So for example I have a member of staff called john who teaches a module called website development and he also teaches a module called web services. If I type in the name John into my form, how do I get these two linked modules to show? That's my main problem as there are three tables :)
 

Users who are viewing this thread

Top Bottom