Re: Form that also updates the intermediary table?
From: Stranger (stranger_at_raex.com)
Date: 09/08/04
- Next message: Duane Hookom: "Re: Why is my field shortened when queried?"
- Previous message: jontvr: "Re: Removing carriage returns"
- In reply to: Allen Browne: "Re: Form that also updates the intermediary table?"
- Next in thread: Allen Browne: "Re: Form that also updates the intermediary table?"
- Reply: Allen Browne: "Re: Form that also updates the intermediary table?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 7 Sep 2004 21:24:47 -0400
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.
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:%23sSKwnOlEHA.208@TK2MSFTNGP12.phx.gbl...
> 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.
>
> --
> 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" <stranger@raex.com> wrote in message
> news:413d9917$0$94938$45beb828@newscene.com...
>> What do I need to change in the structure?
>>
>> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
>> news:O90umoHlEHA.3536@TK2MSFTNGP12.phx.gbl...
>>> 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.
>>>
>>>
>>> "Stranger" <stranger@raex.com> wrote in message
>>> news:10jp7ib84edgj2e@corp.supernews.com...
>>>> 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" <AllenBrowne@SeeSig.Invalid> wrote in message
>>>> news:etH06pClEHA.1376@TK2MSFTNGP12.phx.gbl...
>>>>> Yes: you will have another subform bound to tblSiteLocation, and it
>>>>> will contain combo(s) that lookup the Site/Location data.
>>>>>
>>>>>
>>>>> "Stranger" <stranger@raex.com> wrote in message
>>>>> news:10jovfb70lgo598@corp.supernews.com...
>>>>>> 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" <AllenBrowne@SeeSig.Invalid> wrote in message
>>>>>> news:%23%23mmy08kEHA.3756@TK2MSFTNGP11.phx.gbl...
>>>>>>> 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" <stranger@raex.com> wrote in message
>>>>>>> news:413be48c$0$94957$45beb828@newscene.com...
>>>>>>>> Another question. I also have an intermediary table that links the
>>>>>>>> site table and location table. Would those be additional subforms?
>>>>>>>>
>>>>>>>> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
>>>>>>>> news:%23loARI7kEHA.3712@TK2MSFTNGP15.phx.gbl...
>>>>>>>>> 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" <stranger@raex.com> wrote in message
>>>>>>>>> news:10jn8sggrs7jm75@corp.supernews.com...
>>>>>>>>> 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?
>>>
>>>
>>
>>
>
>
- Next message: Duane Hookom: "Re: Why is my field shortened when queried?"
- Previous message: jontvr: "Re: Removing carriage returns"
- In reply to: Allen Browne: "Re: Form that also updates the intermediary table?"
- Next in thread: Allen Browne: "Re: Form that also updates the intermediary table?"
- Reply: Allen Browne: "Re: Form that also updates the intermediary table?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
