Employee database with attendance

[Guest]

I am trying to make an employee database that I can use to track attendance
through points on a rolling calander year, but I am running into the problem
of making any calculations that will add the number of days missed during the
year due to sick leave and/or disability leave and track when the points
should fall off the following year. Also I am having trouble with
calculations such as trying to determine the number of vacation due to an
employee despending on his/her years of service.

 

[Guest]

There is no need to make any calculations for the database itself. You can
keep all data from day one. Where the calculations need to occur is in the
queries and reports.

 

[Guest]

Well I am not as familiar to quieres, each time I run a query for some reason
it doesn't not run right, do you have an suggestions? Another thing I am
having trouble with is setting the relationships, I want to put an employee
form and have it link to the attendance form updating all info and it doesn't
work, it gives me an error code. I have the primary key set as the employees
SSN and I wan the rest of the info to update in the other form, but for some
reason it doesn't work, do you have any suggestions? Thank you for your help!

 

[Guest]

Are you using subforms?

It would be helpful to see your database design. For example, an
EmployeeMaster table might look like:

SSN
LastName
FirstName
MI
Addr1
Addr2
City
ST
ZIP
PH
HireDate
DischargeDate
DischReason

Now, an attendance table might look like:

PrimaryKey (autonumber)
SSN
DateMissed
DateTardy
Excused (Yes/No)
ReasonCode

(Where ReasonCode would be arbitrarily defined by you, like: 1 = Sick, 2 =
accident, 3 = ...)

Then you simply link the two tables by SSN, using SSN as the Primary Key in
the Employee table, but not in the Attendance table.

Once you have the database setup properly, the queries become much more
self-evident, based on the relationship of the tables.

 

[Guest]

Yes, I am using subforms, I have two tables, an employee table which has all
the employees personal information on it and I have an attendance table which
has vacation and in the attendance form I have a subform which calculates all
the points based on a rolling calander and any missed days due to disability
or absences, what I am having difficulty is telling the database to add these
days up without adding uneeded days. If I were to make quieres to do this,
how would I go about that? Thank you so much for you help, I greatly
appreciate it!

 

[Guest]

Queries will "do stuff" based on parameters you provide. For example, if
"Vacation" is ReasonCode = 1, then your query should ignore that one. If you
want to have one record for EVERY work day for every employee, then your
ReasonCode for a normal day worked might = 0. SickDays might be ReasonCode =
2 for example.

So in your query, you'd use:

ReasonCode > 1 (for example)

or perhaps specific queries for specific codes, in which case you'd specify
the actual code in the query itself.

In actual fact, you can design a form that allows you to choose certain
values (for whatever) and pass those values to a query. So if you have a form
named "frmQueryForm", and a field on that form named "fldReasonCode", in the
query you can specify:

ReasonCode = Forms![frmQueryForm]![fldReasonCode]

which is the method used to pass values from a form to a query.

 

[Guest]

Note that you should NOT be using a form to generate review information. That
is the job of actual queries and reports. Forms are for data-entry and
modification only (in the normal scheme of things).

 

[Guest]

Okay, I got it, thank you, but can I get the query to look at days such as
only a 5 day work week rather than saying 1 year plus anytime the team member
missed? What I want to know is when the team members points would fall off
without getting more than a certain amount of points which would lead to
their termination. Another quick question, we have a database on filemaker
pro and it has the team members picture on the database of each individual
employee, can Access do the same? Every time I try to add a picture it adds
the picture to every record, where can I put the picture so that it would
only add it to the record I want? Thank you again! You have been a great
help!

 

[Guest]

I'm not sure I understand you last question. Normally, attendance is checked
over a calendar-year. If you intend having an employee record for EVERY DAY
of the year (including weekends when they might be off), then you could use
ReasonCode = 99 (for example) to note a non-work-day. Normally however,
attendance records are only kept for work days. You can add the DayofWeek to
the attendance table, which is a numeric value representing what day that
date is. For example, 1 might be Sunday, 2 = Monday, etc. I'm not sure of the
values, but you can look them up in the VBA help facility.

Adding an image would only be done to a specific EmployeeMaster record, so I
don't see why it would duplicate for all records. I'm not sure of the method
you're attempting for the add, so I really can't advise on that.

Hope this helps...

Okay, I got it, thank you, but can I get the query to look at days such as
only a 5 day work week rather than saying 1 year plus anytime the team member
missed? What I want to know is when the team members points would fall off
without getting more than a certain amount of points which would lead to
their termination. Another quick question, we have a database on filemaker
pro and it has the team members picture on the database of each individual
employee, can Access do the same? Every time I try to add a picture it adds
the picture to every record, where can I put the picture so that it would
only add it to the record I want? Thank you again! You have been a great
help!

Queries will "do stuff" based on parameters you provide. For example, if
"Vacation" is ReasonCode = 1, then your query should ignore that one. If you
want to have one record for EVERY work day for every employee, then your
ReasonCode for a normal day worked might = 0. SickDays might be ReasonCode =
2 for example.

So in your query, you'd use:

ReasonCode > 1 (for example)

or perhaps specific queries for specific codes, in which case you'd specify
the actual code in the query itself.

In actual fact, you can design a form that allows you to choose certain
values (for whatever) and pass those values to a query. So if you have a form
named "frmQueryForm", and a field on that form named "fldReasonCode", in the
query you can specify:

ReasonCode = Forms![frmQueryForm]![fldReasonCode]

which is the method used to pass values from a form to a query.

 

[Guest]

We were on a calander year, but we switched to a rolling calander year
meaning if you received an attendance point that point would not come off
until the following year on that date, minus the number of days missed
because of absences. How do you make an employee master Record? Sorry I am
new to Access as you can see. What I am wanting to know is can you have a
calculation or a query where they remove the weekends or any days the
employee might be off of the calander year? i.e calander year = 365, year for
an an employee with weekends off = 260. Thank you!

I'm not sure I understand you last question. Normally, attendance is checked
over a calendar-year. If you intend having an employee record for EVERY DAY
of the year (including weekends when they might be off), then you could use
ReasonCode = 99 (for example) to note a non-work-day. Normally however,
attendance records are only kept for work days. You can add the DayofWeek to
the attendance table, which is a numeric value representing what day that
date is. For example, 1 might be Sunday, 2 = Monday, etc. I'm not sure of the
values, but you can look them up in the VBA help facility.

Adding an image would only be done to a specific EmployeeMaster record, so I
don't see why it would duplicate for all records. I'm not sure of the method
you're attempting for the add, so I really can't advise on that.

Hope this helps...

Okay, I got it, thank you, but can I get the query to look at days such as
only a 5 day work week rather than saying 1 year plus anytime the team member
missed? What I want to know is when the team members points would fall off
without getting more than a certain amount of points which would lead to
their termination. Another quick question, we have a database on filemaker
pro and it has the team members picture on the database of each individual
employee, can Access do the same? Every time I try to add a picture it adds
the picture to every record, where can I put the picture so that it would
only add it to the record I want? Thank you again! You have been a great
help!

Queries will "do stuff" based on parameters you provide. For example, if
"Vacation" is ReasonCode = 1, then your query should ignore that one. If you
want to have one record for EVERY work day for every employee, then your
ReasonCode for a normal day worked might = 0. SickDays might be ReasonCode =
2 for example.

So in your query, you'd use:

ReasonCode > 1 (for example)

or perhaps specific queries for specific codes, in which case you'd specify
the actual code in the query itself.

In actual fact, you can design a form that allows you to choose certain
values (for whatever) and pass those values to a query. So if you have a form
named "frmQueryForm", and a field on that form named "fldReasonCode", in the
query you can specify:

ReasonCode = Forms![frmQueryForm]![fldReasonCode]

which is the method used to pass values from a form to a query.

 

[Guest]

Teaching database design is kind-of beyond the scope of these forums. I
provided an example in one of my earlier posts to you. Database design can
take a long time to master. You need to understand data relationships, as
well as relational concepts. One of the first things you need to understand
is the "one-to-many" relationship.

For each employee that you have (one record), you will have MANY attendance
records. Using SSN, you "relate" the EmployeeMaster table with the
EmployeeAttendance table. Also, you DO NOT institute a "rolling" ANYTHING in
the data itself. That is reserved for queries. In the tables, you simply
collect data. Later, you can create a query that will do whatever pruning and
adjusting you want. But tables are for RAW DATA only.

As to whether or not you can adjust for work days - that depends on how you
accumulate attendance data. If you have 365 attendance records per year per
employee for example, you can add the DayOfWeek to each record, then exclude
those records from queries. I've been in the software devlopment field for
over 30 years, and to me it sounds like you are making this WAY more
complicated than it really needs to be.

Mentally, you need to separate DATA from REPORTING. Once you've done that,
the rest becomes easier....

We were on a calander year, but we switched to a rolling calander year
meaning if you received an attendance point that point would not come off
until the following year on that date, minus the number of days missed
because of absences. How do you make an employee master Record? Sorry I am
new to Access as you can see. What I am wanting to know is can you have a
calculation or a query where they remove the weekends or any days the
employee might be off of the calander year? i.e calander year = 365, year for
an an employee with weekends off = 260. Thank you!

I'm not sure I understand you last question. Normally, attendance is checked
over a calendar-year. If you intend having an employee record for EVERY DAY
of the year (including weekends when they might be off), then you could use
ReasonCode = 99 (for example) to note a non-work-day. Normally however,
attendance records are only kept for work days. You can add the DayofWeek to
the attendance table, which is a numeric value representing what day that
date is. For example, 1 might be Sunday, 2 = Monday, etc. I'm not sure of the
values, but you can look them up in the VBA help facility.

Adding an image would only be done to a specific EmployeeMaster record, so I
don't see why it would duplicate for all records. I'm not sure of the method
you're attempting for the add, so I really can't advise on that.

Hope this helps...

Okay, I got it, thank you, but can I get the query to look at days such as
only a 5 day work week rather than saying 1 year plus anytime the team member
missed? What I want to know is when the team members points would fall off
without getting more than a certain amount of points which would lead to
their termination. Another quick question, we have a database on filemaker
pro and it has the team members picture on the database of each individual
employee, can Access do the same? Every time I try to add a picture it adds
the picture to every record, where can I put the picture so that it would
only add it to the record I want? Thank you again! You have been a great
help!

:

Queries will "do stuff" based on parameters you provide. For example, if
"Vacation" is ReasonCode = 1, then your query should ignore that one. If you
want to have one record for EVERY work day for every employee, then your
ReasonCode for a normal day worked might = 0. SickDays might be ReasonCode =
2 for example.

So in your query, you'd use:

ReasonCode > 1 (for example)

or perhaps specific queries for specific codes, in which case you'd specify
the actual code in the query itself.

In actual fact, you can design a form that allows you to choose certain
values (for whatever) and pass those values to a query. So if you have a form
named "frmQueryForm", and a field on that form named "fldReasonCode", in the
query you can specify:

ReasonCode = Forms![frmQueryForm]![fldReasonCode]

which is the method used to pass values from a form to a query.

 

[Guest]

Could it be because I am using Access 2002? I used the one-to-many
relationship and for some reason it is not updating the information into the
attendance from the employee table. What I did was join the SSN from
employee to SSN in the attendance and it is not updating the SSN nor the
first, last name or any related info? Have you ever had this problem? Maybe
I could show you the database I have so you can see it?

Teaching database design is kind-of beyond the scope of these forums. I
provided an example in one of my earlier posts to you. Database design can
take a long time to master. You need to understand data relationships, as
well as relational concepts. One of the first things you need to understand
is the "one-to-many" relationship.

For each employee that you have (one record), you will have MANY attendance
records. Using SSN, you "relate" the EmployeeMaster table with the
EmployeeAttendance table. Also, you DO NOT institute a "rolling" ANYTHING in
the data itself. That is reserved for queries. In the tables, you simply
collect data. Later, you can create a query that will do whatever pruning and
adjusting you want. But tables are for RAW DATA only.

As to whether or not you can adjust for work days - that depends on how you
accumulate attendance data. If you have 365 attendance records per year per
employee for example, you can add the DayOfWeek to each record, then exclude
those records from queries. I've been in the software devlopment field for
over 30 years, and to me it sounds like you are making this WAY more
complicated than it really needs to be.

Mentally, you need to separate DATA from REPORTING. Once you've done that,
the rest becomes easier....

We were on a calander year, but we switched to a rolling calander year
meaning if you received an attendance point that point would not come off
until the following year on that date, minus the number of days missed
because of absences. How do you make an employee master Record? Sorry I am
new to Access as you can see. What I am wanting to know is can you have a
calculation or a query where they remove the weekends or any days the
employee might be off of the calander year? i.e calander year = 365, year for
an an employee with weekends off = 260. Thank you!

I'm not sure I understand you last question. Normally, attendance is checked
over a calendar-year. If you intend having an employee record for EVERY DAY
of the year (including weekends when they might be off), then you could use
ReasonCode = 99 (for example) to note a non-work-day. Normally however,
attendance records are only kept for work days. You can add the DayofWeek to
the attendance table, which is a numeric value representing what day that
date is. For example, 1 might be Sunday, 2 = Monday, etc. I'm not sure of the
values, but you can look them up in the VBA help facility.

Adding an image would only be done to a specific EmployeeMaster record, so I
don't see why it would duplicate for all records. I'm not sure of the method
you're attempting for the add, so I really can't advise on that.

Hope this helps...

:

Okay, I got it, thank you, but can I get the query to look at days such as
only a 5 day work week rather than saying 1 year plus anytime the team member
missed? What I want to know is when the team members points would fall off
without getting more than a certain amount of points which would lead to
their termination. Another quick question, we have a database on filemaker
pro and it has the team members picture on the database of each individual
employee, can Access do the same? Every time I try to add a picture it adds
the picture to every record, where can I put the picture so that it would
only add it to the record I want? Thank you again! You have been a great
help!

:

Queries will "do stuff" based on parameters you provide. For example, if
"Vacation" is ReasonCode = 1, then your query should ignore that one. If you
want to have one record for EVERY work day for every employee, then your
ReasonCode for a normal day worked might = 0. SickDays might be ReasonCode =
2 for example.

So in your query, you'd use:

ReasonCode > 1 (for example)

or perhaps specific queries for specific codes, in which case you'd specify
the actual code in the query itself.

In actual fact, you can design a form that allows you to choose certain
values (for whatever) and pass those values to a query. So if you have a form
named "frmQueryForm", and a field on that form named "fldReasonCode", in the
query you can specify:

ReasonCode = Forms![frmQueryForm]![fldReasonCode]

which is the method used to pass values from a form to a query.

 

[Guest]

*sigh*

Email it to: (e-mail address removed)

You should "compact and repair" the database first, then ZIP it, before
emailing, in order to conserve bandwidth.

Could it be because I am using Access 2002? I used the one-to-many
relationship and for some reason it is not updating the information into the
attendance from the employee table. What I did was join the SSN from
employee to SSN in the attendance and it is not updating the SSN nor the
first, last name or any related info? Have you ever had this problem? Maybe
I could show you the database I have so you can see it?

Teaching database design is kind-of beyond the scope of these forums. I
provided an example in one of my earlier posts to you. Database design can
take a long time to master. You need to understand data relationships, as
well as relational concepts. One of the first things you need to understand
is the "one-to-many" relationship.

For each employee that you have (one record), you will have MANY attendance
records. Using SSN, you "relate" the EmployeeMaster table with the
EmployeeAttendance table. Also, you DO NOT institute a "rolling" ANYTHING in
the data itself. That is reserved for queries. In the tables, you simply
collect data. Later, you can create a query that will do whatever pruning and
adjusting you want. But tables are for RAW DATA only.

As to whether or not you can adjust for work days - that depends on how you
accumulate attendance data. If you have 365 attendance records per year per
employee for example, you can add the DayOfWeek to each record, then exclude
those records from queries. I've been in the software devlopment field for
over 30 years, and to me it sounds like you are making this WAY more
complicated than it really needs to be.

Mentally, you need to separate DATA from REPORTING. Once you've done that,
the rest becomes easier....

We were on a calander year, but we switched to a rolling calander year
meaning if you received an attendance point that point would not come off
until the following year on that date, minus the number of days missed
because of absences. How do you make an employee master Record? Sorry I am
new to Access as you can see. What I am wanting to know is can you have a
calculation or a query where they remove the weekends or any days the
employee might be off of the calander year? i.e calander year = 365, year for
an an employee with weekends off = 260. Thank you!

:

I'm not sure I understand you last question. Normally, attendance is checked
over a calendar-year. If you intend having an employee record for EVERY DAY
of the year (including weekends when they might be off), then you could use
ReasonCode = 99 (for example) to note a non-work-day. Normally however,
attendance records are only kept for work days. You can add the DayofWeek to
the attendance table, which is a numeric value representing what day that
date is. For example, 1 might be Sunday, 2 = Monday, etc. I'm not sure of the
values, but you can look them up in the VBA help facility.

Adding an image would only be done to a specific EmployeeMaster record, so I
don't see why it would duplicate for all records. I'm not sure of the method
you're attempting for the add, so I really can't advise on that.

Hope this helps...

:

Okay, I got it, thank you, but can I get the query to look at days such as
only a 5 day work week rather than saying 1 year plus anytime the team member
missed? What I want to know is when the team members points would fall off
without getting more than a certain amount of points which would lead to
their termination. Another quick question, we have a database on filemaker
pro and it has the team members picture on the database of each individual
employee, can Access do the same? Every time I try to add a picture it adds
the picture to every record, where can I put the picture so that it would
only add it to the record I want? Thank you again! You have been a great
help!

:

Queries will "do stuff" based on parameters you provide. For example, if
"Vacation" is ReasonCode = 1, then your query should ignore that one. If you
want to have one record for EVERY work day for every employee, then your
ReasonCode for a normal day worked might = 0. SickDays might be ReasonCode =
2 for example.

So in your query, you'd use:

ReasonCode > 1 (for example)

or perhaps specific queries for specific codes, in which case you'd specify
the actual code in the query itself.

In actual fact, you can design a form that allows you to choose certain
values (for whatever) and pass those values to a query. So if you have a form
named "frmQueryForm", and a field on that form named "fldReasonCode", in the
query you can specify:

ReasonCode = Forms![frmQueryForm]![fldReasonCode]

which is the method used to pass values from a form to a query.

 

[Guest]

Thank you so much and sorry for all the questions and I really appreciate you
looking at this for me! Thank you!

*sigh*

Email it to: (e-mail address removed)

You should "compact and repair" the database first, then ZIP it, before
emailing, in order to conserve bandwidth.

Could it be because I am using Access 2002? I used the one-to-many
relationship and for some reason it is not updating the information into the
attendance from the employee table. What I did was join the SSN from
employee to SSN in the attendance and it is not updating the SSN nor the
first, last name or any related info? Have you ever had this problem? Maybe
I could show you the database I have so you can see it?

Teaching database design is kind-of beyond the scope of these forums. I
provided an example in one of my earlier posts to you. Database design can
take a long time to master. You need to understand data relationships, as
well as relational concepts. One of the first things you need to understand
is the "one-to-many" relationship.

For each employee that you have (one record), you will have MANY attendance
records. Using SSN, you "relate" the EmployeeMaster table with the
EmployeeAttendance table. Also, you DO NOT institute a "rolling" ANYTHING in
the data itself. That is reserved for queries. In the tables, you simply
collect data. Later, you can create a query that will do whatever pruning and
adjusting you want. But tables are for RAW DATA only.

As to whether or not you can adjust for work days - that depends on how you
accumulate attendance data. If you have 365 attendance records per year per
employee for example, you can add the DayOfWeek to each record, then exclude
those records from queries. I've been in the software devlopment field for
over 30 years, and to me it sounds like you are making this WAY more
complicated than it really needs to be.

Mentally, you need to separate DATA from REPORTING. Once you've done that,
the rest becomes easier....

:

We were on a calander year, but we switched to a rolling calander year
meaning if you received an attendance point that point would not come off
until the following year on that date, minus the number of days missed
because of absences. How do you make an employee master Record? Sorry I am
new to Access as you can see. What I am wanting to know is can you have a
calculation or a query where they remove the weekends or any days the
employee might be off of the calander year? i.e calander year = 365, year for
an an employee with weekends off = 260. Thank you!

:

I'm not sure I understand you last question. Normally, attendance is checked
over a calendar-year. If you intend having an employee record for EVERY DAY
of the year (including weekends when they might be off), then you could use
ReasonCode = 99 (for example) to note a non-work-day. Normally however,
attendance records are only kept for work days. You can add the DayofWeek to
the attendance table, which is a numeric value representing what day that
date is. For example, 1 might be Sunday, 2 = Monday, etc. I'm not sure of the
values, but you can look them up in the VBA help facility.

Adding an image would only be done to a specific EmployeeMaster record, so I
don't see why it would duplicate for all records. I'm not sure of the method
you're attempting for the add, so I really can't advise on that.

Hope this helps...

:

Okay, I got it, thank you, but can I get the query to look at days such as
only a 5 day work week rather than saying 1 year plus anytime the team member
missed? What I want to know is when the team members points would fall off
without getting more than a certain amount of points which would lead to
their termination. Another quick question, we have a database on filemaker
pro and it has the team members picture on the database of each individual
employee, can Access do the same? Every time I try to add a picture it adds
the picture to every record, where can I put the picture so that it would
only add it to the record I want? Thank you again! You have been a great
help!

:

Queries will "do stuff" based on parameters you provide. For example, if
"Vacation" is ReasonCode = 1, then your query should ignore that one. If you
want to have one record for EVERY work day for every employee, then your
ReasonCode for a normal day worked might = 0. SickDays might be ReasonCode =
2 for example.

So in your query, you'd use:

ReasonCode > 1 (for example)

or perhaps specific queries for specific codes, in which case you'd specify
the actual code in the query itself.

In actual fact, you can design a form that allows you to choose certain
values (for whatever) and pass those values to a query. So if you have a form
named "frmQueryForm", and a field on that form named "fldReasonCode", in the
query you can specify:

ReasonCode = Forms![frmQueryForm]![fldReasonCode]

which is the method used to pass values from a form to a query.