Re: Form that also updates the intermediary table?
From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 09/08/04
- Next message: Paul: "Query and table problems."
- Previous message: John Vinson: "Re: Updating Null Child Fields"
- In reply to: Stranger: "Re: Form that also updates the intermediary table?"
- Next in thread: Stranger: "Re: Form that also updates the intermediary table?"
- Reply: Stranger: "Re: Form that also updates the intermediary table?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 8 Sep 2004 13:12:02 +0800
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.
-- 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:10jsmfjpahfdb41@corp.supernews.com... > 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. >> >> >> "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: Paul: "Query and table problems."
- Previous message: John Vinson: "Re: Updating Null Child Fields"
- In reply to: Stranger: "Re: Form that also updates the intermediary table?"
- Next in thread: Stranger: "Re: Form that also updates the intermediary table?"
- Reply: Stranger: "Re: Form that also updates the intermediary table?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
