I didn't know Elena wanted to bring up more than the current job. If so, a subform would be called for.
You're right, I misspoke. With a one-to-many relationship (employee-to-jobs), one would be storing the EmployeeID in the Jobs table, not the other way around. One would HAVE to join on EmployeeID. My bad. Elena still needs the code that is created by the Combo Box wizard in order to use a drop-down list to populate the Employee part of the form (jump to the desired record). Thanks, Toby ----- Original Message ----- From: "Jones, Glenn P MSG (Ret) FL-ARNG" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Tuesday, November 22, 2005 12:31 PM Subject: RE: [AccessDevelopers] Re: Two Tables > ??? > Why would you not join the two forms (form and subform) with the EmpID. If > you have a table of job history and want to view joining by Job# would only > bring up the current job? > I would join by EmployeeID (what ever that may be) and use a datasheet view > in the subform which would show all job history. > > Glenn P. Jones > > > > > -----Original Message----- > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of Toby Bierly > Sent: Tuesday, 22 November, 2005 13:36 > To: [email protected] > Subject: Re: [AccessDevelopers] Re: Two Tables > > > Well then you definitely need two tables. > > What you need to do is to create a query that pulls in the fields you want > from each table, and with the tables joined on JobNbr. Base your form on > this. > > The combo box should then be added to the form as an unbound control. The > choices that appear in the combo box will be based on a query from your > personal info table. But the combo box is still "unbound" because what you > select in the combo box should not change anything in a table, but only > designate which record to pull up. > > To create the combo box, Select the Control Wizards button at the top of the > Design Form Toolbox before selecting the ComboBox Control on the Toolbox and > then adding it to your form. When you add the combo box to the form, the > Combo Box Wizard window should appear. Choose the "Find a record on my form > based on the value I selected in my combo box" option. > > HTH, > Toby > > ----- Original Message ----- > From: "Elena" <[EMAIL PROTECTED]> > To: <[email protected]> > Sent: Tuesday, November 22, 2005 10:24 AM > Subject: [AccessDevelopers] Re: Two Tables > > > > Basically what I want to do is show the information. Say I want to see > > Jane Doe's personal info (address, phone etc) and where she works, I > > go to this form, select her name from a drop down menu and her info > > populates. I set up the two tables so that I could keep a history of > > job changes and terminations so I have a 1 to many (employees to jobs) > > relationship set up. > > The trouble I'm having is getting the form to populate. I thought > > having the two tables was the problem. My original set up was a form > > with the combo box based on the query then a subform based on a > > query as well. I think my mistake is in there somewhere. I'm just > > brain dead over it. :) > > Thanks for all of the responses. > > > > > > --- In [email protected], "Toby Bierly" <[EMAIL PROTECTED]> > > wrote: > > > > > > It all depends on how detailed records you want to keep. > > Certainly if you wanted history of employee-job instances, or if > > simultaneously both a single employee could have multiple jobs and a > > single job could be done by multiple employees, you would have a > > Many-to-Many relationship and need a "joining" (a.k.a. "resolver" or > > "linking") table. > > > > > > Thanks, > > > Toby > > > ----- Original Message ----- > > > From: Trent Johnsey > > > To: [email protected] > > > Sent: Tuesday, November 22, 2005 6:44 AM > > > Subject: RE: [AccessDevelopers] Two Tables > > > > > > > > > Technically speaking, one job can be associated with one or more > > employees. One employee can be associated with one or more jobs > > during their employment. I think a joining table (jobnbr, > > employeenbr) is the order of the day in this situation. In this same > > table you could add a date hired to this job, a date terminated or > > date left this job, current wage, and beginning wages etc. for > > historical reference. If the employee would change jobs, then you > > simply add a new record to this table. > > > > > > Having a one to many from job to employee or employee to job > > would eliminate the possibility of keeping historical info related to > > each job to employee instance. Just a thought. > > > > > > TJ > > > > > > "Jones, Glenn P MSG (Ret) FL-ARNG" <[EMAIL PROTECTED]> wrote: > > > Addi! ng to Toby's reply; > > > If you have more than one person with the same job description > > then you > > > would want to place the Job# with the personnel information. > > If it is one > > > job one person then it really does not matter thought I prefer > > keeping Job# > > > with personnel information as personnel information is a more > > of a primary > > > table. > > > The combo box you want would be created from the query which > > is the data > > > source for your form. > > > > > > > > > Glenn P. Jones > > > Comm.: 904 823-0653 > > > > > > > > > > > > -----Original Message----- > > > From: [email protected] > > > [mailto:[EMAIL PROTECTED] On Behalf Of Toby > > Bierly > > > Sent: Monday, 21 November, 2005 16:05 > > > To: [email protected] > > > Subject: Re: [AccessDevelopers] Two Tables > > > > > > > > > Whether or not to have two tables depends. My guess is that > > it should be in > > > two tables. For instance, if one person leaves a job and > > anot! her person > > > takes the job, all you have to change is the personal info and > > link it to > > > the job table. But then if personal and job info is a one-to- > > one > > > relationship, you could always have it all in one table and > > just edit > > > certain fields. > > > > > > In any case, it should be fairly straightforward to create a > > query that > > > brings the two tables together to base the form on. > > > > > > Create a new query, and in Design view, add both the Personal > > and Job > > > tables. You will have to store either the Job# in the > > Personal table or > > > vice versa and then set the two tables to join on these keys > > in the query. > > > > > > HTH, > > > Toby > > > > > > ----- Original Message ----- > > > From: "Elena" <[EMAIL PROTECTED]> > > > To: <[email protected]> > > > Sent: Monday, November 21, 2005 7:40 AM > > > Subject: [AccessDevelopers] Two Tables > > > > > > > > > > Hello all, > > > > I have two tables: one contains personal i! nfo the other > > contains job > > > > info. I want a form where I can select a person from a combo > > box and > > > > have their info, from BOTH tables, be displayed. I have > > tried using a > > > > query using certain criteria but I still can't get it to > > work. Any > > > > suggestions? Would it be better just to have one table > > containing all > > > > of the info? > > > > > > > > Thanks. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > Yahoo! > > > > Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > SPONSORED LINKS Microsoft access developer Microsoft access > > help Microsoft access database > > > Microsoft access training Microsoft access training > > course Microsoft access programming > > > > > > > > > > > > > > > > > > ------------------------------------------------------------------- > > ----------- > > > Yahoo! FareChase - Search multiple travel sites in one click. > > > > > > > > > Please zip all files prior to uploading to Files section. > > > > > > > > > > > > SPONSORED LINKS Microsoft access developer Microsoft access > > help Microsoft access database > > > Microsoft access training Microsoft access training > > course Microsoft access programming > > > > > > > > > ------------------------------------------------------------------- > > ----------- > > > YAHOO! GROUPS LINKS > > > > > > a.. Visit your group "AccessDevelopers" on the web. > > > > > > b.. To unsubscribe from this group, send an email to: > > > [EMAIL PROTECTED] > > > > > > c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms > > of Service. > > > > > > > > > ------------------------------------------------------------------- > > ----------- > > > > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. Yahoo! > > Groups Links > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > Yahoo! Groups Links > > > > > > > > > > > Please zip all files prior to uploading to Files section. > Yahoo! Groups Links > > > > > > > ------------------------ Yahoo! Groups Sponsor --------------------~--> Get fast access to your favorite Yahoo! Groups. Make Yahoo! your home page http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/q7folB/TM --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
