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....
Michael said:
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.