Actually I will join them on EmpID, mainly because I do not have a "job number" but I'm not sure if that was just for example or not. However, I had not had the idea to display the history as well...now I just might do that.
Thanks! :) --- In AccessDevelopers@yahoogroups.com, "Jones, Glenn P MSG (Ret) FL-ARNG" <[EMAIL PROTECTED]> wrote: > > ??? > 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: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of Toby Bierly > Sent: Tuesday, 22 November, 2005 13:36 > To: AccessDevelopers@yahoogroups.com > 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: <AccessDevelopers@yahoogroups.com> > 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 AccessDevelopers@yahoogroups.com, "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: AccessDevelopers@yahoogroups.com > > > 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: AccessDevelopers@yahoogroups.com > > > [mailto:[EMAIL PROTECTED] On Behalf Of Toby > > Bierly > > > Sent: Monday, 21 November, 2005 16:05 > > > To: AccessDevelopers@yahoogroups.com > > > 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: <AccessDevelopers@yahoogroups.com> > > > 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 > ------------------------ Yahoo! Groups Sponsor --------------------~--> 1.2 million kids a year are victims of human trafficking. Stop slavery. http://us.click.yahoo.com/WpTY2A/izNLAA/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/
