Form that also updates the intermediary table?

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?

Thanks.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stranger" wrote in message
...
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?

Thanks.

On Sun, 5 Sep 2004 20:02:04 -0400, "Stranger"
wrote:

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?

Typically you would use two forms for this purpose. Whichever table is
the most common "view" of the data would be used on a main form, and
tblemployeeshardware as a subform. For instance, if you're usually
choosing an employee and assigning that employee hardware, you'ld base
a mainform on Employees and have a subform on it, with a combo box
based on Hardware to select the desired hardware.

You'ld need a separate form to enter new Hardware; this form could be
opened from the NotInList event of the combo box.

If you're doing it the other way around - selecting an item of
hardware, and assigning it to an employee - you can reverse the roles,
of course.

John W. Vinson[MVP]
(no longer chatting for now)

Thanks to both of you for the reply. I will play with this. Sounds like I
don't need a query. This project has been a good learning of Access.

"John Vinson" wrote in message
...
On Sun, 5 Sep 2004 20:02:04 -0400, "Stranger"
wrote:

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?

Typically you would use two forms for this purpose. Whichever table is
the most common "view" of the data would be used on a main form, and
tblemployeeshardware as a subform. For instance, if you're usually
choosing an employee and assigning that employee hardware, you'ld base
a mainform on Employees and have a subform on it, with a combo box
based on Hardware to select the desired hardware.

You'ld need a separate form to enter new Hardware; this form could be
opened from the NotInList event of the combo box.

If you're doing it the other way around - selecting an item of
hardware, and assigning it to an employee - you can reverse the roles,
of course.

John W. Vinson[MVP]
(no longer chatting for now)

Another question. I also have an intermediary table that links the site
table and location table. Would those be additional subforms?

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stranger" wrote in message
...
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?

Thanks.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stranger" wrote in message
...
Another question. I also have an intermediary table that links the site
table and location table. Would those be additional subforms?

"Allen Browne" wrote in message
...
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.


"Stranger" wrote in message
...
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?

Thanks.

I got it. thanks!!!

"John Vinson" wrote in message
...
On Sun, 5 Sep 2004 20:02:04 -0400, "Stranger"
wrote:

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?

Typically you would use two forms for this purpose. Whichever table is
the most common "view" of the data would be used on a main form, and
tblemployeeshardware as a subform. For instance, if you're usually
choosing an employee and assigning that employee hardware, you'ld base
a mainform on Employees and have a subform on it, with a combo box
based on Hardware to select the desired hardware.

You'ld need a separate form to enter new Hardware; this form could be
opened from the NotInList event of the combo box.

If you're doing it the other way around - selecting an item of
hardware, and assigning it to an employee - you can reverse the roles,
of course.

John W. Vinson[MVP]
(no longer chatting for now)

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?

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stranger" wrote in message
...
Another question. I also have an intermediary table that links the site
table and location table. Would those be additional subforms?

"Allen Browne" wrote in message
...
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.


"Stranger" wrote in message
...
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?

Thanks.

Yes: you will have another subform bound to tblSiteLocation, and it will
contain combo(s) that lookup the Site/Location data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stranger" wrote in message
...
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?

"Allen Browne" wrote in message
...
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.


"Stranger" wrote in message
...
Another question. I also have an intermediary table that links the site
table and location table. Would those be additional subforms?

"Allen Browne" wrote in message
...
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.


"Stranger" wrote in message
...
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?

Thanks.

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.

"Allen Browne" wrote in message
...
Yes: you will have another subform bound to tblSiteLocation, and it will
contain combo(s) that lookup the Site/Location data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stranger" wrote in message
...
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?

"Allen Browne" wrote in message
...
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.


"Stranger" wrote in message
...
Another question. I also have an intermediary table that links the
site table and location table. Would those be additional subforms?

"Allen Browne" wrote in message
...
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.


"Stranger" wrote in message
...
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?

Thanks.