Simple questions

[canfish]

Hi all,

I am a relatively inexperienced Access user, and I have a few questions which, if answered, may help me see the light.

First, an example: I have a table about facilities, with details for a facility, and I have a field "Employees". In one record, under the "Employees" field, I want to tell the database that I have several employees working at the facility. How do I take advantage of database technology in order to do this? I know I can only have one value per field per record, so I can't just store 4 or 5 employee IDs in there (linked to an employees table). But I want the database to know that there are 5 employees working there, and when I query/report I want to be able to find out who they are quickly. Any help?

Second, how does having relationships really help when doing queries and reports? They seem logical, but how exactly are they useful? I can tell the difference between a one-to-many relationship and a one-to-one relationship, and I know somewhat how to establish such relationships, but I don't know what to do with them afterwards. This is probably related to my lack of knowledge on how queries/reports work. Any advice would be appreciated - in the books and tutorials I read, I haven't really been able to glean the understanding I need.

I have many more questions, but I'll let you guys go at these two. Thank you for your help!

[jzwp11]

Welcome to the forum!

With a relational database you can relate the employees to a particular facility in a couple of different ways. You say that one facility can have many employees, can an employee work at more than one facility?

If a person can work at only 1 facility, this is how it would be handled:

tblEmployees
-pkEmployeeID primary key, autonumber field
-txtFName
-txtLName
-txtSSN
-fkFacilityID foreign key to tblFacilities (long number data type field)

tblFacilities
-pkFacilityID primary key, autonumber field
-txtFacilityName
other field related to the facility

If a person can work at more than one facility then you would use a structure like this

tblEmployees
-pkEmployeeID primary key, autonumber field
-txtFName
-txtLName
-txtSSN


tblFacilities
-pkFacilityID primary key, autonumber field
-txtFacilityName
other field related to the facility

tblFacilityEmployees
-pkFacEmpID primary key, autonumber
-fkFacilityID foreign key to tblFacilities (long number data type field)
-fkEmployeeID foreign key to tblEmployees (long number data type field)

The tblFacilityEmployees is called a junction table. It is a way to handle a many-to-many relationship.


Now if an employee can only work at one facility at a time but can transfer to another facility over time and you want to track those migrations then we can adjust the junction table by adding a date field to say when the employee effectively joined each facility

tblFacilityEmployees
-pkFacEmpID primary key, autonumber
-fkFacilityID foreign key to tblFacilities (long number data type field)
-fkEmployeeID foreign key to tblEmployees (long number data type field)
-dteEffective (effective date)


The joins (relationships) between tables allow you to view the data in all the tables involved in the join. You would bring the tables together in a query and select the fields from any of the related tables you want to see together and then run the query to get the results.

Reports can be based on just the table or on a query, so if you want to see more than one's tables data in a report you would use a query that joins the tables first and then base a report on that query.

[canfish]

Thank you very much for your excellent answer, jzwp! It has helped me inordinately.

Now that I understand tables better (I got subdatasheets working, so I think I set them up right), I am wondering how to use forms to inform a junction table. I just can't wrap my head around it. I think it is straightforward enough when, for example, I make a "new employee" form and it puts data into the employees table. But what about when it is putting data into two tables, the facilityemployees table and the employees table? I honestly really don't understand forms that well, so any help with regard to that problem with a wider explanation of how exactly forms are used to put data into tables in a sound way would be extremely helpful, if anyone has the time.

Thanks so much again!

[jzwp11]

One thing I failed to mention. Access has the capability of having lookup fields (combo or list boxes) at the table level, but they generally are not recommended. This site has more detail on that aspect. The combo and list boxes are bests left for your forms.

First, I would go to the relationship window and formally set up your relationships there. (from the Tools menu). Once you place all of the tables into the window, you want to click on the primary key field of one table and drag it to the corresponding foreign key field of the related table. Check all 3 option boxes for the join. Close/save the relationship window changes.

Second, I would use the form wizard to create a simple form tied to your facilities table. Save the form. Go ahead an enter some of your facilities. There will be some record navigation buttons in the lower left corner of the form

Now, create another new form based on the employee table, you can use the form wizard here too. Save the form.

Now create another new form based on the junction table. I usually use the autoform: datasheet. Now in design view of the form, Access automatically creates a textbox control for each field of the underlying table. Look for the control called fkFacilitiesID and delete the control. Then using the combo box wizard, add a combo box to the form. Follow the prompts of the wizard. When it asks for the table/query that is to supply info to the combo box, choose you facilities table. You want your bound field to be the pkFacilitiesID and you want it bound to the fkFacilitesID of the junction table. Save the form.

Now open the employee form in design view, drag the lower control to open up the detail section of the form a little bit. Jump to the navigation pane/forms tab, click and drag the form based on the junction table (you just created) into the employee form (into the space you just opened up). Access should automatically link the form (employee) to the subform (form based on the junction table) via the primary key--foreign key relationship that you set up in the relationship window. If you do not set up the relationship window beforehand, you would have to link the forms manually. Save the form and then open it up in view mode. Enter an employee and then use the combo box to select the facility or facilities for that employee. When you begin to enter data in the subform, Access will automatically populate the primary key value of the record in the main form in the foreign key control of the subform (thus linking the employee record to the related facility in the junction table)


Post back with any questions.

[canfish]

Thanks again for all of your help, jzwp! I do have some further questions.

Take as an example that I have two tables, facilities and employees:

1. I am making a form to include every piece of information about an employee. I want my users to be able to enter, for example, the name of the facility they work at without knowing the FacilityID of that facility, and still have the form update their fkFacilityID even though they don't know what that is. Is there some way of including in the form anything that will do that?

2. Looking at your previous explanation of how to use forms with junction tables, I don't really understand what you mean by "subform". Is a subform still a part of the form? And when I use the form after doing the process you gave me, would I be correct in saying that the form updates TWO tables, the facilityemployee table and the employee table?

3. How do I use subdatasheets when a junction table is included in a relationship between, say, a facilities and an employees table? Do they become useless after the junction table is placed in between? If not, how do I again use subdatasheets to see employee information for each facility while in the facilities table?

4. Similarly, how do I use queries with junction tables? What fields do I include in the query design view if I, for example, want to see employees from a specific facility in the result of my query?

I have many more questions, but I'll leave it at those so as to make them manageable. Once again, thank you for all your help! This is making such a difference.

Cheers

[jzwp11]

1. I am making a form to include every piece of information about an employee. I want my users to be able to enter, for example, the name of the facility they work at without knowing the FacilityID of that facility, and still have the form update their fkFacilityID even though they don't know what that is. Is there some way of including in the form anything that will do that?

The combo box in the subform I described should allow the user to see the facility name. If it doesn't then it is just a matter of adjusting the column widths property of the combo box.

2. Looking at your previous explanation of how to use forms with junction tables, I don't really understand what you mean by "subform". Is a subform still a part of the form? And when I use the form after doing the process you gave me, would I be correct in saying that the form updates TWO tables, the facilityemployee table and the employee table?

A subform is just a form embedded within another form. The subform shows related records to the record shown in the main form. In terms of the relationships, the main form shows the one side of a one-to-many relationship while the subform shows the many side of the one-to-many relationship.

I've attached an example database that illustrates the use of a junction table and associated forms. Take a look at the form called frmPeople. A person can belong to many organizations (much like an employee can be associated with many facilities)

3. How do I use subdatasheets when a junction table is included in a relationship between, say, a facilities and an employees table? Do they become useless after the junction table is placed in between? If not, how do I again use subdatasheets to see employee information for each facility while in the facilities table?

Generally, all user interaction is through forms. If I want to view info while setting up the database, I just use a query. I do not use the subdatasheets.

4. Similarly, how do I use queries with junction tables? What fields do I include in the query design view if I, for example, want to see employees from a specific facility in the result of my query?

You simply bring in the tables that have the fields in which you are interested into the query design view. If you set up the relationships in the relationship window beforehand, Access will automatically carry those over into the query design grid. From there just select the fields you want in the lower part of the grid & run the query to see the results. A SELECT query only displays data, it does not alter the data so there is no harm if you mess it up.

[canfish]

Jzwp11,

Thank you once again for all of your help! The sample you sent me was disturbingly helpful. I still have some more questions, if you have the time.

I followed your instructions with the forms applying to the three tables, and I see now how the combo box can be used to apply a facility/organization to a person without using two separate forms. But what I really want in the combo box is the names of the organizations, not their ID's. And I can't figure out how to link the employee records through the form/subform while maintaining the organization names in the combo box. I am sure there is a way to do this - can you show me how?

Also, once I have inserted the junction-table form into the employees form (as a subform), would it then be ok to delete it? Or would it have to remain vestigially in my forms list?

If I then wanted to make another table for items associated with employees (hours, equipment, etc.), how would I link that to employees? Should I link it to the junction table or the employees table? With either one, how would I make a form similar to the one for entering new employees? I've tried doing that, and all the boxes got completely messed up.

Any and all help is greatly, greatly appreciated! Thanks

[jzwp11]

In the example I provided, you do not see the organizationID in the organization combo box, just the name of the organization, correct? The other 2 ID (pkOrgPeopleID and fkPeopleID) controls are displaying the primary key value of the record in the junction table and the foreign key relating back to the person displayed in the main form. These two other controls can be completely hidden. I left them visible, so that you could see how the main-subforms are linked. To hide a column for a form in datasheet view, see the attached document for a step by step process.

For combo boxes, you control what is seen when the user clicks on the dropdown as well as what is displayed after focus leaves the combo box. I've included a walk through of that as well in the attachment.

Also, once I have inserted the junction-table form into the employees form (as a subform), would it then be ok to delete it? Or would it have to remain vestigially in my forms list?

You cannot delete the subform. A subform can technically be used as a form by itself but then you would have to supply the value of the linking field (foreign key field) manually.

If I then wanted to make another table for items associated with employees (hours, equipment, etc.), how would I link that to employees? Should I link it to the junction table or the employees table? With either one, how would I make a form similar to the one for entering new employees? I've tried doing that, and all the boxes got completely messed up.

The short answer is: it depends--on your business model. Let's start with your example (employees and facilities). Let's say that you have employees that work at more than once facility during a pay period and you want to account for the time a person spends at each facility. Since you already have a junction table that relates the people to the facilities in which they can work, you would join your hours table to the employee-facilities junction table

tblEmpFacilityHours
-pkEmpFacilityHoursID primary key, autonumber
-fkFacEmpID
-dteWorked
-spHoursWorked

In terms of forms, you would have to change the subform from datasheet view to single record view since a datasheet view does not support a subform within it. Within the subform, add another subform based on the hours table. So you will end up with a form-subform-subsubform set up.


Let's look at another scenario. Let's say that your employees work at multiple facilities as before, but in terms of accounting, you don't care which facility they worked in as long as they were working. In that case, your hours table would be joined directly to the employee table.

In terms of forms, you would still have the same main form, but now with 2 individual subforms, one based on the junction table and the other based on the hours table. Both subforms can be in datasheet view.

[canfish]

Thank you again for your help, jzwp!

I am still, however, confused by your explanation of forms. I am trying to understand how to let the user choose a facilityID in the combo box even though the combo box is displaying the Facility Names that correspond to those facilityIDs. In other words, I want the actual names of the facilities to show up in the combo box, like you have them in the example you sent me, and I want the user at the same time to be linking the employee to the actual FacilityID, not just FacilityName. You have it this way in your example, but I can't seem to make it work myself in my own database. How do you do this?

Also, assume I choose to link the accommodations table to the FacEmployees junction table. I've linked it with a fkFacEmployeeID, the relationship is set up. How do I then create a form that lets me enter in what FacEmployee it belongs to and also the information about the accommodation? I know I need a subform for entering the FacEmployeeID, and I know I need to do it with a combo box. But what if the database user doesn't know the employee's ID? Does he/she have to look it up? Or is there a way of including in the form something that they can look the person up in, and then apply that person's id to the combo box?

Thank you so much for all of your help!

[jzwp11]

I want the actual names of the facilities to show up in the combo box, like you have them in the example you sent me, and I want the user at the same time to be linking the employee to the actual FacilityID, not just FacilityName.

If your main and subforms are linked properly as soon as you add a facility via the combo box, that facility is tied to the employee shown in the upper part of the form. Go ahead and enter some and then go back to the junction table and see if values are populated in the table. If they are then everything should be linked. If not, there there probably something wrong in the way the combo box is set up. If you post the database, I can take a look at it to see what is going on. Just make sure to remove any sensitive info (if you want, you can make a copy of the database, remove the sensitive info and post the copy).

How do I then create a form that lets me enter in what FacEmployee it belongs to and also the information about the accommodation?

There are ways through Visual Basic for Application (VBA) code that will open a form when a value is not found in a combo box where you can enter new records. This is a little more advanced. As an alternative, is it feasible to enter the accommodation information ahead of time (using a separate, simple form) and then you should be able to just use a combo box to select the one(s) needed? If that is not feasible, I can provide the code, but it will have to be tailored to your specific application.

I know I need a subform for entering the FacEmployeeID, and I know I need to do it with a combo box. But what if the database user doesn't know the employee's ID?

If the employee's information is shown in the main form, then why would the user even care about the employeeID, if your relationships and forms are properly set up, everything will be tied together.