Hi, yes, that is exactly it. For example, Administration is at both
sites.
If you have several locations and one site, then yes, it makes sense to
have a Site table that lists them. The Location table will have SiteID
as a foreign key, i.e. it indicates the site that the location is at.
I doubt that you need a junction table between Site and Location. That
would only make sense if one location could be at multiple sites, as
well as one site containing multiple locations.
So I should get rid of the junction table tblsitelocation and store
the site and location in one table? That is what I had originally
done but thought that I should split so I knew what locations were at
which site. although maybe it wouldn't matter if I had the site listed
multiple times????
Should I just go back to 3 tables including the junction table? No
need for the additional splits like I did?
What ever would make this easy. I want to try and follow the
standards.
I'm not really clear on what you are trying to store, but if the last
guess was accurate, the junction table would have these fields:
EmployeeID foreign key to tblEmployee.EmployeeID
HardwareID foreign key to tblHardware.HardwareID
LocationID foreign key to tblLocaiton.LocationID
That would allow you to make entries that said, "Employee 6 is using
hardware 4 at Location 2."
The interface would then contain a single subform that had 2 combos
side by side:
[Hardware v] [Location v]
and each row in the subform would match the employee in the main
form.
What do I need to change in the structure?
You can place two subforms on the one Employee form. If there is
not enough space on the screen to do that, you could use a tab
control, and place the subforms in different pages.
If you are actually trying to make assignments that say, "This
employee uses this hardware at this location", the data structure
needs changing.
If I have the main form from the employees table, can I use one
subform to assign hardware and site and location to an employee or
do I need to have another main form for employees and then a
subform to assign the location and site to the employee? This
sounds confusing.
What I believe I want to try and end up with is the main form
where I can look up an employee and then the subform where I can
assign the hardware, site and location.
Yes: you will have another subform bound to tblSiteLocation, and
it will contain combo(s) that lookup the Site/Location data.
Hi,
I have the tblemployeehardware junction table linking to
hardware and employees. Then the tblsitelocation linking
employees with the site table and the location table.
There is a many to many relationship between employees and
hardware and a many to many between employees and sites and
locations.
One employee can have multiple hardware and one hardware can be
assigned to multiple employees. Same goes for the sites and
locations.. Does this help?
Not sure of the data structure here. If you have a Site table,
a Location table, and a SiteLocation table, then yes you would
use a similar setup.
If your juncion table (tblEmployeeHardware) has a foreign key
field to your Location table (so you can say that Employee #2
uses Hardware item 6 at Location 9), then you would have a 2nd
combo box in the existing subform.
Another question. I also have an intermediary table that
links the site table and location table. Would those be
additional subforms?
If you want to see the hardware being used by employees:
1. Create a main form bound to the Employees table.
2. Create a subform bound to the link table.
3. In the subform, include a combo that has the Hardware
table as its RowSource.
For any employee (main form), you can now add rows to the
junction table by selecting a piece of hardware in the
subform, repeating for as many rows as needed.
Hello.,
How do I create a form that will update the middle table?
Two tables, Employees and hardware, intermediary table is
tblemployeeshardware.
Would I create a query using..... I'm not sure.I believe I
need to base the form on a query since a form can not pull
from two tables. What is the best way to do this?
