newbie in ms access

[hinny]

Hello, I am Jennifer , 17years old and I am new to ms access.



I am stupid in ms access , I cant solve my assignment and I almost cry for it.
Please help me solve my problem.
Here's my question :

Table_1 named employee.
Entities are emp_no, emp_name and department.

Table_2 named emp_record.
Entities are ID, date, emp_name, emp_no and department.

Last I have to create a leave application form (5 boxes in the form , call text filed or combo box or column or whatever it call it only contain 5 spaces in the form).

1. Form ID is auto generated. (first box)
2. The date is auto recorded as today date. (second box)
3. A box called employee name, a dropdown box retrieve emp_name from table_1.
4. When I choose a name from dropdown box, the two more boxes in the form will auto filled with emp_no and department (retrieve from table 1,cannot key in by user or edit by user).
5. Created a save button, when I click on the save button, it will save all the datas into table_2.

Please help me on this, I need it before Friday morning. Sorry for disturbing.
Thank You for your kindness.

[ssanfu]

Welcome to the forum...

That is pretty harsh, calling yourself "Stupid". I would say you are inexperienced......which is why you are taking a class and doing homework.

To start off, "Date" is a reserved word in Access and shouldn't be used for object names. Plus, it is not very descriptive... "Date" of what?? Hire date? Pay date?
See http://allenbrowne.com/AppIssueBadWord.html for a list of reserved words.

Did you design on paper what the tables are to be? What are the field data types??

Consider:
tblEmployees
-----------
emp_no <<- What data type? Is this a PK field? A better name would be "EmpNum" or "EmpNum_PK".
emp_name <<- What data type?
department <<- What data type?

tblEmpRecords
-----------------
ID <<- What data type?
EmpDate <<- What data type?
emp_no <<- What data type? A better name would be "EmpNum". Do you know about/understand PK/FK relationships??
emp_name <-This should not be in this table. It is a duplicate of tblEmployees.emp_name. Plus if it is for full name, it should be 2 fields.
department <-This should not be in this table. It is a duplicate of tblEmployees.department.


Create a row source for the combo box with the necessary fields. The only field that should be visible is the emp_name field.
You can use a little code in the after update event of the combo box to push the emp name and department into 2 unbound controls on the form.


Have fun studying.....

[ArviLaanemets]

I'm somewhat confused with your Table_2.
Is it a table where you assign an employee at certain date to certain department? When this is the case, then field department in Table_1 is not needed.
Or it is a table where you enter some unidentified info about employee at certain date? When this is the case, then is the employee attached to department in Table_1 for lifetime?!!!

Generally the database structure must be something like:
tblEmployees: EmployeeID, Forename, LastName, ...
tblDepartments: DepartmentID, DepartmentName
tblEmplDep: EmplDepID, EmplDepDate, EmployeeID, DepartmentID

tblEmplEvent: EmplEventID, EventDate, EmployeeID, EventDescription

A description of form to register events:
Form's datasource is tblEmplEvent;
The form is either single or continuous;
On form you have a text box p.e. txtEventDate, linked to field EventDate. The default value for control =Date(). NB! Don't set default value for date field - otherwise you risk to get orphan entries! You can disable/lock this control permanently, when you want to restrict date edit for user;
The next control will be a combo box, p.e. cbbEmployee, linked to field EmployeeID (Control Source = EmployeeID). Set the RowSource for combo as "SELECT EmployeeID, LastName & " " & Forename AS FullName FROM tblEmployees", BoundColumn to 1, ColumnCount to 2 and ColumnWidths to "0;2.5";
The next control will be an unbound text box, p.e. txtDepartment;
The last control will be a text box linked to field EventDescription.

For cbbEmployee, you create an OnChange event, which calculates matching department name depending on employee and event date running a query, and writes it into unbound text box (txtDepartment). The query text will be something like (on fly):

strQry =
"SELECT a.DepartmentName FROM EmplDep a
WHERE a.EmployeeID = " & Me.ccbEmployee & " AND a.EmplDepDate = " &
(SELECT TOP 1 b.EmplDepDate FROM EmplDep b WHERE EmployeeID = " & Me.ccbEmployee & " AND b.EmployeeDate <= #" & Me.txtEventDate & "# ORDER BY EmplDepDate DESC"

And same code you use in OnCurrent event of form, so the right department is displayed, whenever you select another record.