Start with a simple select query based on the employee table. Show the Employee table again and it will probably appear with "_1" as a suffix.
You can then join the tables on SupervisorID and EmpID. The join should include all records from tblEmployees and from tblEmployees_1 where they match.
This is called a "self-join". Search for more information.
Duane Hookom MVP
MS Access
----------------------------------------
> To: MS_Access_Professionals@yahoogroups.com
> From: zjubias@gmail.com
> Date: Wed, 5 Jun 2013 05:00:16 +0300
> Subject: RE: [MS_AccessPros] Form with a Tab-Form - How to link each Tab Record to the Record on MainForm
>
> Hello Bill,
>
> Very kind of you.
>
> I will try with your suggestion, but I am confused how to built the said
> query.
>
> Thank you for your time.
>
> zjubias
>
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
>
> zjubias
>
> The usual way to put the supervisor data in the Employee table is to add a
> field that is the same data type as the Employee ID. Say Fred is a
> supervisor and his employee ID is 123. And Fred supervises Sam. Sam's record
> would have 123 in the supervisor field.
>
> Then you build a query that has the employee table in it 2 times. Join one
> table's Supervisor field to the other table's Employee ID. The first table
> would be used to display the employee info and the second table would
> display only the first and last name. That would be the supervisor.
>
> If none of this makes sense I can upload a little sample for you.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "ZJUbias" <zjubias@...>
> wrote:
>>
>> Hello Sir Duane,
>>
>>
>>
>> Thank you for your time.
>>
>>
>>
>> Ok for tblAddress, I may not include this table anymore, since they want
> to
>> input only the employee's nationality and emergency contact(s). I may just
>> include these info into tblEmployee.
>>
>>
>>
>> For tblSupervisor, how will I then link the supervisors to each employees.
>>
>>
>>
>> For the documents, no, each document(s) were privately own by each
>> employee..
>>
>>
>>
>> So assuming that my table structure is ok to go, how will I proceed to put
>> details on separate tab form and to link to each employee in the main
> form,
>> i.e.;
>>
>>
>>
>> Is it something likely to use the empid as the link control to each tab
>> form?
>>
>>
>>
>> Tab0 - employee info
>>
>> Tab1 - employee work history
>>
>> Tab2 - employee documents
>>
>> Tab3 - employee emergency contacts
>>
>>
>>
>> And may main form will be holding the general of employee, like: empid,
>> fname, lname, department & reportto.
>>
>>
>>
>> Thank you in advance for your help,
>>
>>
>>
>> zjubias
>>
>>
>>
>>
>>
>>
>>
>> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Duane
> Hookom
>> Sent: Tuesday, June 04, 2013 8:11 AM
>> To: Access Professionals Yahoo Group
>> Subject: RE: [MS_AccessPros] Form with a Tab-Form - How to link each Tab
>> Record to the Record on MainForm
>>
>>
>>
>>
>>
>> I'm confused by the purpose of the tblAddress since I don't see any fields
> I
>> would expect in the table.
>>
>> Also, I would not create tblSupervisor but use tblEmployee for employees
> and
>> supervisors.
>>
>> Can documents belong to more than one employee?
>>
>> Duane Hookom MVP
>> MS Access
>>
>>> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>> <mailto:MS_Access_Professionals%40yahoogroups.com>
>>> From: zjubias@... <mailto:zjubias%40gmail.com>
>>> Date: Tue, 4 Jun 2013 05:45:43 +0300
>>> Subject: RE: [MS_AccessPros] Form with a Tab-Form - How to link each Tab
>> Record to the Record on MainForm
>>>
>>> Hello Duane,
>>>
>>>
>>>
>>> Thank you for the feedback.
>>>
>>>
>>>
>>> So, how do I breakdown my table, should I breakdown something like;
>>>
>>>
>>>
>>> tblEmployee:
>>>
>>> EmpRecNo (A/N)
>>>
>>> EmpID
>>>
>>> DeptID
>>>
>>> SupervisorID
>>>
>>> FName
>>>
>>> LName
>>>
>>> BOD
>>>
>>> DateHired
>>>
>>> Position
>>>
>>> EmpNotes
>>>
>>>
>>>
>>> tblAddress:
>>>
>>> AddrRecNo (A/N)
>>>
>>> EmpID (fk)
>>>
>>> Country
>>>
>>> Nationality
>>>
>>> CountryPhone
>>>
>>> EmergencyPhone
>>>
>>> EmergencyContact
>>>
>>> AddrNotes
>>>
>>>
>>>
>>> tblWorkHistory:
>>>
>>> WHRecNo (A/N)
>>>
>>> EmpID (fk)
>>>
>>> Company
>>>
>>> Position
>>>
>>> Year
>>>
>>> WHNotes
>>>
>>>
>>>
>>> tblDepartment:
>>>
>>> DepRecNo (A/N)
>>>
>>> DeptID
>>>
>>> DeptName
>>>
>>> DepNotes
>>>
>>>
>>>
>>> tblSupervisor:
>>>
>>> SprID (A/N)
>>>
>>> SprFName
>>>
>>> SprLName
>>>
>>> SprNotes
>>>
>>>
>>>
>>> tblDocuments:
>>>
>>> DocRecNo (A/N)
>>>
>>> DocID - (this will reset per employee)
>>>
>>> EmpID
>>>
>>> DocDescription
>>>
>>> DocFrom
>>>
>>> DocDate
>>>
>>> DocCopy (scanned copy)
>>>
>>> Thank you for feedback
>>>
>>> zjubias
>>>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (19) |
Tidak ada komentar:
Posting Komentar