Duane, Hi. I now have 4 tables, tblEmployee with 2 fields "EmployeeId-(Pk)"
& "EmployeeName", tblSlips with 5 fields "SlipId-(Pk)", "EmployeeId-(Fk)",
"LeaveDate", "LeaveType" & "LeaveHrs", tblEmployeeLookup, 1 field
"EmployeeName-(Pk)" & tblLvTypeLookUp, 1 field "LeaveType-(Pk)"
:
You should not create fields with the names of leave types. Each "leave
event" should create a record in a new table with a structure like:
tblEmployeeLeave
==================
EmpLeaveID autonumber primary key
EmployeeID links to your table of employees
LeaveType either the value like "Sick", "PTO",... or
a link to the primary key value of a table of leave types
LeaveHours Numeric double for recording the number of hours
There are lots of great resources on the web regarding normalization. For
instance
http://www.datamodel.org/NormalizationRules.html and
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
--
Duane Hookom
Microsoft Access MVP
:
Ok, I understand changing "name", are you saying I should use a drop down
for the type of leave a person uses? There's only 9 types of leave a person
can take so I added a field named "other" just in case something changed. I
have field Named VacDate in the slips table. Sorry, this is such an exacting
science. I will do better. It should be 2 tables, correct??
Thank You
:
To be totally honest, I would not do anything with your application until:
1) you change the names of objects. "Name" is property of almost all objects
in Access and should never be used as the name given to anything.
2) "10 fields of the different types of leave" normalize your table
structure. What happens with your application when a new type of leave is
created? You shouldn't have to change tables, forms, queries, reports,...
when your business changes.
I don't understand the remainder of your reply. Can you create a query that
sums employee hours by month? You don't have any date fields so you can't get
monthly totals.
--
Duane Hookom
Microsoft Access MVP
:
Duane, Hi. I'm sorry but in looking at the the query, it does not work,
probably because I did not explain what I want correctly. I have 2 tables,
one named Name with "NameId" as the PK and a field called "Name", the other
table is named "Slips", it has 10 fields of the different types of leave a
person can take and it also has the "NameId" PK from the other table. When I
run the query it does not filter the name I type in. Can you help me, again??
Thank You Very Much
:
I would create a table of Years and Months with a column for the number of
work hours:
tblMthWorkHours
===============
WorkYear numeric
WorkMonth numeric
WorkHours numeric
You could then use this in your query to join your records to the
appropriate record to grab the WorkHours.
--
Duane Hookom
Microsoft Access MVP
:
Thank you Duane, that works and I understand it some what! With that working
I have another question. I want to divide the number of hours a employee is
at the job by the number of hour in the work month to determine the percent
of time they are present for duty. Should I establish a variable and an "if"
saying if the month = "Nov" the number of work hours = 152 and if so, would
you show me how to do that??
Thank You
:
SELECT [Employee], Format([WorkDate],"yyyymm") as YrMth, Sum([WorkHours]) As
TotalWorkHours
FROM [a]
GROUP BY [Employee], Format([WorkDate],"yyyymm");
If you can't get this to work, come back with some actual names.
--
Duane Hookom
Microsoft Access MVP
:
I have a query that has the names of employees and the dates and hrs. they
did a job, about 10,000 entries from Jan to present. How do I make a query
total the time by month for each employee?
Thanks!
