create table that store monthly salary of various employee

[harry26]

hi,
i want to create a table that stores monthly salary of many employees.
its like :
employee code : jan 15 : feb 15 : mar15 : apr15 : .................: dec 15
1...................: 5000 : 5000 : 5000 : 6000 :..................:8000
2...................: 5000 : 5000 : 5000 : 6000 :..................:8000
3...................: 6000 : 6000 : 7000 : 7000 :..................:8000
4...................: 6000 : 8000 : 8000 : 8000 :..................:8000
5...................: 5000 : 5000 : 5000 : 6000 :..................:8000


6 ..................: 7000 : 8000 : 8000 : 9000 :................. :10000
7 ..................: 8000 : 8000 : 8000 : 12000:...................:18000

after saving these record i want to refer particular employee code salary for particular month in a query.like select employee code 1 and month mar15 then it should give result 5000.
how to make this table in access and and link salary field to month field in query???

[maximus]

Try some thing like this e.g.

table_employee_salary
tbl_employee_salary_trans_ID (Autonumber Primary Key)
tbl_employee_salary_employee_code (preferably text if your codes are alpha numerical)
tbl_employee_salary_month_code (Number e.g. 1=January;2=February)
tbl_employee_salary_year_code (Number e.g. 2015;2016)
tbl_employee_salary_amount (currency)

The above is a good table structure and will help you to filter records using queries. You cam create a simple Select Query using the Table table_employee_salary and in the criteria of tbl_employee_salary_employee_code you can put [Type:Employee_Code];tbl_employee_salary_month_code [Type:Month]; tbl_employee_salary_year_code [Type:Year]. Now when ever you open the query it will prompt you for the employee code, month code and year code. Type the employee code,month code and year code in the respective input box and you will see the query opens with the record for the particular employee code that you have typed for a particular month for a particular year. if you want to display records based on employee codes only don't put criteria to month and year field.

[NTC]

that is not a good design and will require a query per month plus the need to alter it each year. the design should be:

AutonumberKey - Employee - DatePeriod - Amount (note that 'Date' is a reserved word)

do not confuse presentation with table structure; one can present data side by side via union or cross tab queries if that is needed....