How to connect info from two tables

[BADebbie]

I need a table of employees and I need a table of equipment. I need to somehow connect the two so I know which employees need to be signed off on a piece of equipment. How do I make this connection? Each EE needs to run a piece of equipment by a certain date. I have the EE name, in one table and I have the equipment name in another. Should I maybe combine the two in just one table? Seems like a lot of work when there should be an easier way with two tables. I have a lot of equipment and about 95 employees.



EE Name Equipment Name Sign off by:
John Doe Backhoe 08/30/15

[orange]

I suggest you read and work through this tutorial from RogersAccess Library.
You will learn the concepts of database and table design.
There is a process identified in the tutorial which you can use with any database.
Good luck.

[BADebbie]

I read it, and I tried to implement it. It didn't work. It didn't pull up any of the Employees to start putting in dates of sign off.

[June7]

Exactly what did you do that "didn't work"? What happened - error message, wrong results, nothing?

Did you build a third 'junction' table?

tblEmployeeEquipment
EmpID
EquipID
DateDue
DateVerify

Options for data entry/edit:

1. single form bound to tblEmployeeEquipment with comboboxes to select employee and equipment

2. main form bound to tblEmployees and subform bound to tblEmployeeEquipment with combobox to select equipment

3. main form bound to tblEquipment and subform bound to tblEmployeeEquipment with combobox to select employee

[orange]

Did you see the "and work through" comment in post #2?

[BADebbie]

What didn't work was that the query didn't pull up any information, nothing. It should have brought in my employee names at least. I did create 3 tables. Employees, Equipment and Sign off Date. I am confused about how to make the relationships. Do I have to add Employee ID (PK) to my other tables so I can make the relationship? All the examples show the PK from one table in the rest of the tables when making a relationship. I have no idea what a "Junction" table is.

[June7]

A junction table is the third table needed for a many-to-many relationship. That is the tblEmployeeEquipment I used or the SignOffDate table you used.

Post the query SQL statement you attempted.

What is not clear about the info in post 4?

Did you really complete the tutorials recommended by orange in this short time?

[BADebbie]

I did really complete the tutorials. I have attached a view of where I'm at. I can't make the relationships work. I want to paste a copy of my doc, but I can't find anyway to do this.How do I get the PK from the Tables into the other

tables? Like they did here? Job History has 3 PK's. I can't make mine do that.

[orange]

Nothing attached??? Ah ha! Now the jpg is there.

There are NOT 3 PKs. It is 1 PK that is composed of 3 fields. This is known as a composite Primary Key.
The three fields are required to make the record unique within the table.

[BADebbie]

Can you see the picture I put in my message? See how the second table JobHistory has 3 pk's? I can't get mine to do that. How do I do that?

[orange]

Please see my post #9 and the link within.

[June7]

Do not set those 3 fields as primary key, set them as a compound index if you want to assure that the combinations cannot be duplicated.

[BADebbie]

Thank you all for your help, but I give up.

[orange]

Did you try to write a clear description of the issue you are trying to solve? Perhaps you could share your description of your "business" with readers, who can then ask questions or offer advice.
Did you really work through the tutorials?
Have you used Google for your research?

There is more info at this link.
Good luck.

[ssanfu]

BADebbie, it takes a long time to learn how to use Access. I would think that with a name like Bad Axe Debbie you wouldn't give up so easily.

I threw together an example based on your picture of the table structure.

How do I get the PK from the Tables into the other

Normally, you use a main form/sub form arrangement.

Open a table and add employees, then open the Job type table and add some job types. Using the main form, select an employee, then in the subform add some jobs, a date and salary.

See if this helps....... and have fun!