How can i add multiple entries for a record? 2

[jmandiaz]

Hi guys,
Does anyone know how to add multiple entries for a record?

I'm trying to accomplish the following:

I've created a table called sick time
I want to be able to track how much sick a certain individaul take

I'm not to sure if it would be easier to allow multiple entries for a record and then sum the total hour of sick time which will not exceed 24 hours

or if i should write and update statement to update each an entry is made any suggestions and examples would be greatly appreciated.

Regards

Jaime

 

[psemianonymous]

I'd recommend you store each sick time occurrence as a separate line. You could simply store the table as:
Employee | Sick_Date | Sick_Hours | Comments


Then you make a query, call it "total sick time", and make a query that groups by the Employee, sums the Sick_Hours, and voila, you end up with your proposed setup anyway. With this format, you can do sick time by year, so that you don't have to wipe the sick time table completely. Also this makes your system less vulnerable to errant entries, and it's easier to update and even set up, in my opinion.

If there's no other factor involved here, I'd recommend you just throw this in an Excel spreadsheet, even in the same format as I listed above. It all depends on what you need.

 

[jmandiaz]

foolio2,
how do i store each occurence in a separate line? Your response is exactly want i want do i'm just not sure how to store the values on a separate line


-Jaime

 

[psemianonymous]

This is too...vague. You use a form, bind the form to the table, and fill in the Employee, the sick date, and fill in the # of hours for that date. For additional entries, you fill in the employee, the sick date, and the # hours for that date. If you erroneously enter something, you can delete the entire entry, or change the # of sick hours or the sick date, or even the employee if you got that wrong.

This is too basic to answer properly. Use a form, or even enter the information directly into the table via table view.

 

[jmandiaz]

Foolio12,
As you can tell i hardly use access. I've created the table called sicktime wich contains the following fields
L_Name, F_Name, Total_Sick_Hours, Hours_Taken, Date

i created a form using the sicktime fields i can enter the first values with out a problem for example

Doe John

hours_taken 8 date 4/26/2004

when i go and enter another value the previous value gets over written.

i guess my question is How do i keep the previous values from getting over written?

 

[jmandiaz]

Foolio12,
After reading your posting i guess i can do what your suggesting if i was inputing the data directly into new table.

I've actually created the table already and pre-populated the all the fields with the exception of hours taken and date

the sick time table is also linked to an employee table via SSN. SSN being the relationship field. Are you telling me that i would still be able to store multiple entries for the same record

 

[lespaul]

Well, you are not going to want to store 'Total_Sick_hours' in the table (it's a calculated field and breaks 3NF).

Where do you get the employee name from? Do you look it up or allow someone to type it in? If it's a type in field, what happens when the user enters 'Deo John'?

So, there should be a table of employees to choose from so you only get records for employees that exist.

Does each person get a certain number of sick hours each month or year?

So, I would have:

tblEmployee
EmployeeID
EmployeeFName
EmployeeLName
etc(what ever other information you are storing that is about the EMPLOYEE - if people get a certain number of hours per month or year, I would store this information here: ACCRUAL_AMT unless EVERYBODY gets the same amount)

tblLeaveActivity
LeaveID
EmployeeID
LeaveDate
LeaveHours (positive if accrual, negative if deduction)

To see total available leave you would just sum the leavehours field and group by employee.



Leslie

 

[jmandiaz]

Leslie,
Thanks for your response i have no problem summing the hours via a query. My problem is storing multiple values for the same employee. I'm pulling the employee name off the sick time table the the name fields also live in another table. What i'm really after is storing multiple values for the same record i.e john doe the user is not allowed to type and employee name the name is already pre-defined the user using the db is only allowed to enter hours taken and date do i make sense here?

 

[psemianonymous]

You're saying that your users can only enter records for themsselves. That's fine. Just set the default value for the "employee" field to be ... whatever their employeeID or name or SSN is. Then lock the field. So if they try and add a record, it defaults to their name. You can similarly filter the recordset so that they can only edit records where the employee id is that of their own.


Pete

 

[lespaul]

So you are creating a table to track sick leave entries. The employee information comes from a different table (do you actually store the person's name in your table or the PK of the employee table? It should be the PK.)

What is the PK for your table?

Did you use the form wizard to create the Sick Leave Entry form?

Leslie

 

[jmandiaz]

Leslie,
SSN is the primary key. I've have all the information i need. I keep on tripping over how to store multiple values for the same record. Once i figure that out i can write a query that can sum the hours each employee has taken.

 

[jmandiaz]

Pete,
Let me give this another shot here.
I created 3 tables Empcontact, sicktime, monetaryinfo
i made the SSN the PK on all three tables.

The sick time table has the following fields

SSN, EMPNUMB, FNAME,LNAME,TOTALSICKHRS,HOURSTAKEN,DATE

All of the fields listed above with the exception of hourstaken and date are already populated

I want the user who is going to be tracking the sick time to enter the amount of hours taken and the date John Doe was out sick

I'm successfull in entering the sick time for the first time on John Doe's record

so for example John Doe took a sick day

3/25/2004


My table displays
SSN, EMPNUMB, FNAME,LNAME,TOTALSICKHRS,HOURSTAKEN,DATE
123-45-6789 1 John Doe 24 8 3/25/2004

Let say John takes another 4 hrs of sick time today if go to my form and enter 4 today date 4/26/2004

the 8 hours and 3/25/2004 values disappear I want to be able to keep the previous values meaning the 8 3/25/2004

 

[psemianonymous]

You're mixing what you want to SEE with what you want to STORE in data tables. You want to be able to SEE the employee's information and SEE that employee's list of sick dates. That's fine, but--but you don't STORE it in that manner.

You make both the SSN and the Sick_Date the (what is called composite) primary key for the sicktime table. You don't store the extra information, like employee name, or whatever else, in the sicktime table, you link to that information via the SSN field.

This is too fundamental to explain in any small manner. Here is a thread where they link to a document that will get you going on the right track:

thread181-828550

 

[lespaul]

OK, if the SSN is the PK for your field, then you will only be allowed to enter each SSN ONE TIME!! That's the point of the PK, you can only have ONE of these in the table.

If you are getting the employee information from another table you DO NOT need to include the person's name in YOUR table (that breaks 3NF too - duplication of data).

The sick time table should have:

SSN or EMPID (FK to the employee information)
SickDate
HoursTaken


The COMBINATION of EmpID or SSN AND the DATE make a composite PK in the sick table. To do this, open your table in design mode, highlight both rows of SSN and SickDate and press the key button. You now have a composite PK

ID SickDate HoursTaken
1 2/28/04 6
1 3/31/04 2
2 2/16/04 8
3 4/1/04 1.5
1 4/18/04 8

but you would not be able to add ANOTHER record for person 1 on 2/28/04 since the combination of person1 and date 2/28/04 is not unique!

HTH

Leslie

 

[jmandiaz]

Lisa, Pete,
Thanks for your help and patience. I was able to acomplish what i was looking for.

Thanks again

 

[lespaul]

Glad to help!

leslie