Data Extraction

[faisal88]

HELLO everyone first of all Hi i am new here my name is Faisal and i have just start learning MS ACCESS ,Okay my question is



i have 2 tables

Employee Data
Employee Ticket

i have all the information in employee data and i need some of that information in employee ticket too .

For example field One is Id number and 2nd field is Name 3rd field is department & and 4th field is designation IN EMPLOYEE DATA TABLE

now in EMPLOYEE TICKET i need Multiple joining like i put only ID NUMBER the name department and designation should be extracted from Employee data automatically is it possible ??

Il be highly thankful for quick response

[Homegrownandy]

Yes its possible. Don't duplicate the data you should link them via the employee ID. I think that's what you are doing?

So every ticket relates to a specific employee via the employee ID. The just make a select query to show the information you want.

Ask more questions if you're still confused.

[faisal88]

Sir as you can see employee data i have department designation and salary highlighted ,and in another picture is EMPLOYEE TICKET table in which these 3 requirments are empty
this is the ID 2257071882
IF I JUST WRITE THIS ID IS IT POSSIBLE THE ANOTHER THREE (department designation and salary) can be pulled from employee data table so that i dont have to write one by one

Like in excel we use vlookup or index match

[Homegrownandy]

#If data is specific to the employee then it should only exist in the employee table. nowhere else.

each ticket should HAVE to have an employee ID. (assuming that 1 ticket relates to one employee.)

you need to make the data relate to each other. click on the database tools tab on the top > then relationships.

drag the ID from employees to the employee ID on tickets. This will create a one to may relationship by default.

One employee may have many tickets.

I'm assuming this is how you want it anyway.

[faisal88]

Sir the Id number is unique because every person has seperate id number id number is IQAMA NUMBER the first field in employee data WHAT I WANT IS THAT IF I AM JUST PUTTING ID NUMBER IN TICKET TABLE THE OTHER THINGS LIKE DESIGNATION SALARY JUST AUTOMATICALLY PULLED IN TICKET TABLE COLUMNS

[Homegrownandy]

I know. I've just told you how to relate that data.

[ssanfu]

Do not use "Name" as a field name. "Name" is a reserved word and should not be used as an object name.
I would have two fields: "FirstName" and "Surname".
If you must use 1 field, a better name would be "FullName".


Andy is correct.

Should not duplicate the data.
Table "EmployeeTicket" should not have fields "Department", "Designation" or "Salary".

Table "EmployeeData" (should not use spaces in object names) has field a PK field "IQAMA_NUMBER" (no spaces)
Table "EmployeeTicket" has FK field "IQAMA" .




In the relationship window, add both tables.
Drag EmployeeData.IQAMA_NUMBER to EmployeeTicket.IQAMA.
Check "Enforce Referential Integrity". Click OK.




Remember....
TYPING IN ALL CAPS IS THE SAME AS SHOUTING!.
Please don't shout.

A few words in capitals can be used for emphasis, but not every word should be capitalized..