|
||||||
Access: Many-To-Many Relationships By Richard Rost 16 years ago I received an email from a student today asking how to track employees and the hours they bill to each customer. Their programmers work on projects for customers, and they need to bill them accordingly. You would need an Employees table and a Customers table, obviously. Now you'd need a BillableHours table so you can keep track of the hours each Employee spent working for each Customer. It would look like this:
Now you have a third table that is related to both of the other tables. You can now generate reports for each EMPLOYEE (how many hours has Joe billed this month?) or by CUSTOMER (what do we need to bill XYZ Corp?) You've created a many-to-many relationship between Customers and Employees using a JUNCTION or CROSS REFERENCE table. I cover this concept in detail in my ACCESS 2013 EXPERT 7 class and also my ACCESS RELATIONSHIP SEMINAR. There's also a free tutorial on it in my TIPS section: Access Many-To-Many Relationships. The same situation arises if you have something like CUSTOMERS and what SERVICES they have received. You would have one table with your customers, another table containing a list of services, and then a third JUNCTION table that links them (and even might have details on that particular instance of service). Customers:
Services:
Junction:
This junction table says:
|
||||||||||||||
| |||
Keywords: access many-to-many relationships junction cross-reference tips PermaLink Access: Many-To-Many Relationships |