Copie a value from a field to another based on the chosen value of the dataset

[adider]

Hello together

First I would like to say that I am new here and hope, that you are able to help me out. First you have to know, that I am not a specialist, which means i can't programm vba or SQL on my own (or better without exact advices ).
I am using Access 2013, german edition.

In the past I created a few access databases but now i have to use a unconventional thing for a database.

The scenario is as follows



Table 1 with (Personal Data of an employee)
ID (auto), Name, Prename, Salary, Jobfunction and a few other specific fields

Table 2 to fill (Kind of timesheet)
ID (auto), Name, Prename, Working date, Working hours, Salary, Jobfunction, and the rest of the other table.

The Table 2 should be filled via simpel form where you simply choose the name (combo box), fill out the date and enter the hours.
The rest of the information should be taken over in Tabel 2 based on the choosen name but has not to be visible.

Explenations:
It is for a school project where we (unfortunately) have to collect working hours data (respectively costs per working day) from the year 2015 for roughly 10 empoyees. The main project will be the comparison of overall costs (including costs for employees) to the sales revenues and rebates.
Therefore I would like to export one table from time to time to excel. The problem I struggle with my known methods is, that the employee data chances over time (eg. salary for employee 1 increases in the middle of the year). Out of this reason I cant use the 1:1 query type...
The easiest way for the further handling would really be an "autofilled" table from one combobox selection with two new fields; Date and Hours.

Thank you very much for your help in advance.

Adrian

[pbaldy]

How about:

BaldyWeb - Autofill

I would argue the only table 1 field other than ID that should be stored in the second table is salary.

[NoellaG]

Hi,
if the job info changes over time you'll need a different database structure. The employee info can be in 2 tables: tblEmployee with all info that doesn't change:
empID (PK), empName, empPrename, empBirthdate, ... . In the second table, for example tblEmployeeJob the changeable info and key fields: ejID (PK), ejEmployee(Foreign key to tblEmployee.empID), ejStartDate, ejEndDate, ejJobFunction, ejSalary, ect.
In your timesheet form you can than search for the stable info in table tblEmployee, linked to the variable info in tblEmployeeJob filtered to see the info that is valid on the date that is mentioned on the worksheet.

[adider]

Hello

Thank you both for your replies.
Noella If I get you right I would end up with three tabels: First one with static data, second one with variable data and third with Information employee ID, variable dataset ID, working date and hours. Right?

Field1 Table 3 is a Combobox linked to table ID from table 1. There I would still have the problem to automaticly fill the "variable data field" in table 3 with the ID from table 2 based of the selection in table 3 field1?

Or did I missunderstand anything?

Best regards
Adrian

[NoellaG]

The easiest way to show related data is to use a form with a subform. In the main form: employee info, the subform is linked to the main form on employee ID and there you can show the detailed timesheet data