Create A Database For Tracking Time Off From Work And Print A Weekly Roster?
Dec 11, 2013
I 'm trying to create a database for tracking time off from work and print a weekly roster. I have built several databases in Access 2003 and have now transitioned to 2010 and it is seems to be going well. My past databases have been rather simple data in data out not really that big of a deal. However on this database they need a report that will show them a week view that shows them all the employees regular days off and any additional time off they have schedule in that week.
I have an employee info table that has employee basic info, Emp name, Emp number, Work Week code (which identifies which days off the employee has) Hire Dates (for seniority sorting) and then I have 7 fields listed as D1, D2, D3 and so on until D7 (I will try to explain these fields later).
I have another table (Time Requests) that has 3 fields, Date, Emp number, and Time code (which identifies why they are off work or unavailable to be assigned).
The last table I will refer to has 7 fields, they are Date1, Date2, Date3, Date4, Date,5 Date6, Date7
I now have a form. This form has the 7, fields Date1, Date2.... Date7.
On my form Date1 field is updated by a manager with a date that is a Sunday then Date2 = [Date1]+1,Date3 = [Date1]+2 and so on until I have all 7 fields showing dates from Sunday - Saturday.These fields are lined across the top in a vertical direction positioned above a subform of the employee info table. So directly below the 7 Date fields are the D1, D2, D3, D4, D5, D6, D7 fields then to the right is the Emp name and Emp number, This gives me a Week view of who is working.
I have been able to get the D1, D2.. fields to show their respective Work Week codes by writing very simple If Then statements . So here is where I AM STUCK, When I am showing this week view how do I get D1 - D7 to identify what date they are supposed to be in reference to Date1 - Date7 then compare themselves to the table Time Requests to see if they have a match and then set the value of D1, D2 . to the Time code in that table. D1 - D7 need to auto populate and do this for about 50 employees.
View Replies
ADVERTISEMENT
Dec 10, 2013
I have been tasked at my work to create a database for tracking time off from work. I have built several databases in Access 2003 and have now transitioned to 2010 and it is seems to be going well. My past databases have been rather simple data in data out not really that big of a deal. However on this database they need a report that will show them a week view that shows them all the employees regular days off and any additional time off they have schedule in that week.
I have an employee info table that has employee basic info, Emp name, Emp number, Work Week code (which identifies which days off the employee has) Hire Dates (for seniority sorting) and then I have 7 fields listed as D1, D2, D3 and so on until D7 (I will try to explain these fields later).
I have another table (Time Requests) that has 3 fields, Date, Emp number, and Time code (which identifies why they are off work or unavailable to be assigned).
The last table I will refer to has 7 fields, they are Date1, Date 2 – Date7
I now have a form. This form has the 7, fields Date1, Date2 – Date7.
On my form Date1 field is updated by a manager with a date that is a Sunday then Date2 = [Date1]+1,
Date3 = [Date1]+2 and so on until I have all 7 fields showing date from Sunday – Saturday.
These fields are lined across the top in a vertical direction positioned above a subform of the employee info table. So directly below the 7 Date fields are the D1, D2, D3, D4, D5, D6, D7 fields then to the right is the Emp name and Emp number, This gives me a Week view of who is working.
I have been able to get the D1, D2….. fields to show their respective Work Week codes by writing very simple “If Then” statements . So here is where I AM STUCK…When I am showing this week view how do I get D1 –D7 to identify what date they are supposed to be in reference to Date1 – Date7 then compare themselves to the table “Time Requests” to see if they have a match and then set the value of D1, D2 …. to the “Time code” in that table. D1 – D7 need to auto populate and do this for about 50 employees.
Here is a screen shot of what I have so far and where I am stuck: [URL] ....
View 2 Replies
View Related
Jul 3, 2013
I am building a database for employee training. In the FrmNewTrainingGroup I am trying to create a form that will allow selection of a single class, a completion date, one or more instructors (or none at all) and a list of students by LName, FName, MI, EMP#. in my latest attempt, I have tried using a query of the TblEmployee to act as a go between, and dropped a subform into the FrmNewTrainingGroup, and that is not working.
Trying to get the student roster to work has been frustrating. I have been attempting to make it a check box select list that is also searchable by LName and FName [which would perhaps display a reduced list of names, as the name was typed?. This list would update a class roster text box to reflect all the selected employees for training so far, prior to the class being committed (I am planning on a button on the form).
View 2 Replies
View Related
Oct 18, 2005
I'm undecided in what I should to to track what an employee does for the day. Currently employees have a 5X7 index card which is printed out fields and they fill it in by hand and turn it in.
Instead of the employees doing this I want to record all of this information in the database.
These are the fields that I will include:
EmployeeName, Book, WorkType, WorkEffort, StartPage, EndPage, StartTime, EndTime, TotalPgsWorked, Comments.
Should I bound all of these fields to the tblWorkLog or should a create unbound fields and create an append query which will add these fields to the table.
I don't want the users to enter their own start time and end time as they might not record it accurately.
Should the time be recorded this way: Have the user open the form, when the form opens that will record the start time. Have the user have the form remained open until the end of the day or when he or she finishes through out the day. When the form is closed that will record the end time.
What is an alternative of doing that though? Have the user open the form record all the info and have the startime recorded. Exit out of the database, when they finish their work, open the database go back to that record and record the end time?
My problem here would be there is no uniqe ID. The user can work on a Book for a week, and can also work on multiple books on the same day. How would they know which record to open.
I'm uncertain how to lay this out and begin it any insight will be greatly appreciated.
View 14 Replies
View Related
Jun 26, 2014
I'm creating a query for a someone who wants quantities summed by a weekly range and correspond to a week number. This person wants Access to do something that is a simple function on Excel.
I have no clue how to do this or if Access is even made to do something like this.
This is an example of what I have so far
Quote:
P/N QTYWork DateWW (Work Week)
25COMP16/11/201428
25COMP26/20/201425
25COMP27/9/201428
25COMP36/20/201425
....And so on
My code is
Code:
SELECT dbo_JBKLG.JKPRT AS P/N, dbo_JOB.JBQOR AS QTY, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4)) AS [Work Date], DatePart("ww",[Work Date]) AS [WW]
FROM dbo_JBKLG LEFT JOIN dbo_JOB ON dbo_JBKLG.JKJOB = dbo_JOB.JBJNO
GROUP BY dbo_JBKLG.JKPRT, dbo_JOB.JBQOR, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4))
There are more part numbers and the data from the other parts must be in the same format in the same query.
Quote:
P/N QTY WK WW
25COMP 15506/09-06/15WW24
25COMP 22706/16-06/22WW25
25COMP 16006/23-06/29WW26
25COMP 20006/30-07/06WW27
.....And so on
View 14 Replies
View Related
Jan 15, 2015
I have created a table for a Gym that shows which classes / activities are on which days. You will notice in the image below that i have assigned Boolean to associate a class with a day of the week.
I now am trying to create a Calendar STyle report that GROUPS all activities by the day they are on. So, for example, see below:
As you can see, all of Monday's activities will appear first (in a group) followed by Tuesdays.
One way I think I could achieve this is by doing a QueryMonday, QueryTuesday...etc so I have dataset or resultsset for each day. Then I could bring into a report, each query as a sub-report. I believe this is overly complex for what i'm trying to achieve. Also, using 5 grouping levels in one report doesn't achieve it either.
View 2 Replies
View Related
Feb 18, 2014
I have a database of around 15,000 users and I'd like to create a query that I can run on a weekly basis and save the results to an Excel spreadsheet. The results need to be logical and understandable by my coworkers.
Unfortunately, the actual results of the query are not (in their raw form) logical or easy to interpret.
Let's say I have a table called "users" and within that I have:
Surname
Forename
FieldA
FieldB
FieldC
FieldA has a value of either NULL or a 12-digit number
FieldB has the values are "ENABLED", "DISABLED" and "N/A"
FieldC contains a value of either "1" or NULL
This means nothing to my coworkers who want each user to be sorted into a "category". As I'm running this on a weekly basis, I'd like this query to do the work for me, so I don't have to manually assign everyone to a category in Excel. Plus, of course, there is no chance of human error if the query does this for me.
Sooo... I'd like my query to categorise for me as follows:
Category1 = FieldA IS NOT NULL and FieldB="ENABLED"
Category2 = FieldA IS NOT NULL and FieldB="N/A"
Category3 = FieldA IS NULL and FieldB="ENABLED"
Category4 = FieldA IS NULL and FieldB="N/A"
Category5 = FieldA IS NOT NULL and FieldC = 1
... etc.
I'd like the final column in the query results to simply list the category name, so I can simply copy and paste the data into an Excel spreadsheet and be done with it, safe in the knowledge that it makes sense to all.
View 14 Replies
View Related
Nov 4, 2013
I have my database and my form all created and working. Reports and everything are in place. But we need to have a record of what changes have been made. Essentially what I want to make is a table that is essentially a log of all the changes that have occurred.
What I have is essentially a time clock database. So when someone enters our facility the guard will check them in and out. What I want is a list of all the ins and out for a day. So it will be a running list. Then I will create a query to return results for a report. But where I am having problems is creating a table that will house this data. So out of my main table I want a child table that will have certain data fromt he parent table. Mainly the time in and out along with the name of the person. There will be multiple repeat data entries in this table and I am ok with that.
View 14 Replies
View Related
Mar 2, 2014
Basically, I have a database table that is maintained every week. It is about e-mail account licenses for Office 365.
Each column represents a week of license data for every mailbox account which is about 10 000 plus users with the date as the field headers( something like "License information as of 06122013").
Basically, I have created 52 queries based on the license type I require to be calculated & I have a form created to control it. However, right now it is all output only as I have yet to create any user parameter to specify on any of the queries about the date I want
As I am currently unsure how to specify all the queries to use one field date header which I want the user to specify via drop down list in order to calculate all the licenses on this specific date.
For example, if I want to see the license information for 06/11/2013, I would choose "License as of 06112013" & it would then run all the queries based on the header specified earlier & output that information on the form.
Right now the queries are all configured as the "Select" type.
View 1 Replies
View Related
Apr 24, 2006
I have a time tracking system where users can clock in and clock out. I want to have an error message when they clock in and then accidently clock in again. Therefore, they can only clock out after they have clocked in. The data goes into the table called "Timeclock Table". The fields in that table are SSN, Clock-In, Clock-Out, and TimeStamp. the clock in and clock out are check boxes. can you guys help me??????
View 1 Replies
View Related
Sep 10, 2007
Hello,
I'm having a hard time trying to figure out how I should go about designing the table structure for the hours tracking for the database I'm working on. Basically, I have a table with projects (tblProj) that I need to track the number of labor hours worked per month. The problem lies in the fact that each project could have varying time periods, anywhere from less than a year to more than a year and can start and end at any time of the year.
I was thinking of creating a second table (tblProjHours) to store the hours for the project but I'm not sure what the most efficient way of doing this would be. My idea was to create a table as follows:
tblProjHours
ProjHoursID (pk)
ProjID (fk)
BeginDate
EndDate
Month1
Month2
Month3, etc....
I would create a large number of fields/columns, something like Month1 to Month36 just to make sure I have enough months to enter in the hours. This is of course inefficient since some projects would be way shorter than the maximum allowed months set by the table structure and there is always the possibility of surpassing the maximum allowed months based on the table structure. I was wondering if someone had any better ideas on how I should pursue the design. Thanks in advance!
TheMach
View 2 Replies
View Related
Apr 17, 2014
I have two tables, One table containes customer name, etc., tblcustomers the other table contains the tbltimelog, log of activity start time end time. No issues. I want to generate a form based on the dateofentry for the time record. I am not able to get a summary lets say I spent 20 hours on ABC and 20 hours on CBS. If I spent 3 different days on ABC and 6 different days on CBS it will not add them together it will list it ABC 3 times and CBS 6 times. I have tried the distinct function but it doesn't work because the date is distinct. Is there a way to do this as an SQL or whatever. Thinking I could just create a new table and calculating the totals but that seems to be a waste.
View 3 Replies
View Related
Mar 26, 2008
Is there a way to track changes made by different users to access tables? For instance if several users update or change data in various records is there a way to track when and what was changed (before and after data)?
View 1 Replies
View Related
Oct 10, 2007
I consider myself an intermediate ACCESS user and can get around it with little trouble with the help of the internet and the help file.
I am trying to design a database that is fairly simple in design, it is for tracking the status of documents at our work center. Basically we have personnel reports that are passed around the different people and different dates.
I have a main form that has the info on the person along with dates that it is due.
Then a sub form that has the date sent and where it went to.
My problem lies in that I want to keep track of the whole history of the document but when i print out a report or export the data to excel to put up in powerpoint I need to have just the most recent date sent only item.
I have 2 tables one with user info and another with the routing info with its own primary key along with a identifier key to link the primary and sub form.
Of course it is easy to create a report with the data to show all the different routing steps but not just the one with the most recent date.
Thanks in advance for the help.
View 10 Replies
View Related
Nov 12, 2005
I would like a field on my main switchboard that displays the last time the database was modified.
Simple question, and I can not find an answer anywhere.
Thanks!
View 6 Replies
View Related
Jun 28, 2007
Hi team.
I've had a bit of a search around the forums and haven't found this anywhere.
Does anyone have a sample issue tracking database for an IT department?
I don't have too much Access experience so am looking for a little bit of a headstart on this. It seems relatively basic though.
The fields I think I will be needing are the following:
Log No
Status
Priority
Type
Project
Raised by Person
Raised by Department
Description of Issue
Status
1 Active
2 On-Going
3 Hold
4 Finished
5 Cancelled
Priority
1 High
2 Medium
3 Low
Type
1 Bug
2 Enhancement
3 Other
Project
1 Lotus Notes
2 File Maker
3 Tumbleweed
4 Windows
5 Microsoft Word
6 Microsoft Excel
7 Microsoft Outlook
8 Adobe Acrobat
Raised by Department
1 Research & Development
2 Customer Service
3 Finance
4 Data Solutions
5 Communication Services
6 Production
7 Stores
8 Operations
9 Other
View 4 Replies
View Related
Aug 3, 2005
Would like to hear from anyone that has designed a database that can be used to track employee compensation on a year to year basis. I am current designing a database to do just that but I'm having a hard time deciding on how to setup the tables. What they use this for is yearly reviews so I need it to be able to pull data from prior years and the current year. I've designed one but don't think its going to work for me. Just interested to see if and how someone has done the same thing.
Thanks,
Chester
View 4 Replies
View Related
Dec 6, 2007
Hello,
I am trying to figure out how to setup my tables. The tracking system i am trying to create will have these information:
the Tables will contain these information
- Package number
- Received date
- Sent date
- How long have we had the package
- Status (Sent or Pending)
I will also need to figure out who received and sent the package
- Employee name
Please advise if I setup the tables correctly.
I will also need to query these info:
- all packages received 3 days ago
- how many packages an employee received and sent
- How many sent or pending
Thank you for your help.
View 8 Replies
View Related
Mar 15, 2007
Hello Everyone,
I've been looking at all the fine db samples and advice provided in this forum and they are giving me a huge boost in my new job as a DB Admin. :D
Since this forum has a very active group and you guys know and done it all, ;) I thought I would ask if anyone out there has done or could give me an idea on how to buid the following db:
I need to create a db that will keep track of a specific item from the moment it comes in 'til it goes back out. This could be weeks in the process.
What happens is that this is a program where people donate old worned out wheel chairs and they get totally overhauled. As the chairs come in they need to be tracked from start to finish to see where they came from (Somewhere in the USA), what process it took to overhaul it (ItemID and Current location [Storage, Repair BIN, Repair Table, Completed Tabe, Outbound Table, En Route to Destination, etc] and status [For parts, fixable, paint room, repair room, completed]) and where they were donated to, a city in some foreign country (Africa, Asia, Mexico, South America, Etc.).
If someone has something I can use would be great, but if not any idea on how to do it best, would be greatly appreciated.
Thanks!
Ren
View 1 Replies
View Related
Jul 9, 2014
Our office needs a way to track reports that are due to us, so I'm trying to build an Access database to do that. What happens is this...
We put out a weekly tasking document (called an AFCTO) every Friday that tasks our outside agencies (units) to do various things. Each task in the AFCTO directs a single unit to do a specific thing. Units may be tasked multiple times in the AFCTO (one-to-many relationship), but each task only applies to one unit.
Some tasks require the units to send us reports on the status of that task, while other tasks don't. The reports that are due can occur at different frequencies. For instance, some tasks require our units to send us reports weekly on Thursdays; other tasks may require reports to be sent to us monthly on the 1st; other tasks may require daily reporting.
Now, with all that said, we need a way to see what's due to us each day. What I would like is a report that displays what's due for this week, similar to this:
So far I have a very rough mockup of what the form should look like (fmAFCTOTasks in the attached db):
The user can type in the AFCTO Task Number of the task, the start and end datetime group, the unit assigned to that task, the task desc, what type of report is due, what triggers the report, and the frequency at which the report is due. Reports can have one of two triggers...
1) event driven (something happens that requires a report to be due), or
2) date driven (report is due on certain date or day(s)).
Obviously if a report is event-driven, then there will be no frequency or date/day associated with it. How to structure the tables and the form.
View 1 Replies
View Related
Aug 19, 2013
how to build a construction submittal tracking database.. im doing all this in excel but i find it very hard to trace the data that i need.. i have a form where all the data is inputed.
View 11 Replies
View Related
Oct 24, 2004
I am developing a database for a friend and have all the relationships,
tables, forms, queries and reports working well. I used the orders template to
build this and just made a few changes. I have an orders form with an orders details
subform that works beautifully. There is also customers table, employee table,
products and inventory table. The inventory table has product ID, units in stock and reorder level
as the only fields. I need an easy way to have the orders detail form decrease inventory as
items are ordered and also some sort of way to enter restock. Can anyone offer advice that a
beginner can understand in regard to this?
I wrote an update query that he can run that will subtract the qty ordered from the units on stock
for orders with order date >= whatever date he enters, but if he enters a date he's previously updated it will update those records again. I think this is not a good way to go but the only way I could accomplish. I have him using the same order entry form with customer name Restock to add items to inventory by putting an if then else statement in the before mentioned update query. This just adds to inventory if customer name="Restock" else the qty ordered is inventory units in stock -qty ordered.
I'm sure someone can suggest a better way. A friend mentioned dsum function but can't explain how to apply it in this instance. Thanks so much!!!!
View 1 Replies
View Related
Jan 1, 2013
I am looking to create something called a "transactional" database that tracks historical information.I want to track and keep a record of employees at my organization who have had disciplinary action. We currently have a 5 step approach leading to termination.Eg. Step 1: receive Letter #1 Step 2: receive letter #2 + manager meeting step 3: receive letter 3 + manager and director meeting, etc etc.
I believe this is transactional because for each employee, we would need to see when they received the letter in the past, and an employee may receive two or three "step 1 letter 1" notices due to whatever circumstance.I stumbled upon this database and I found the bottom part where you Add/Edit Employee is what I am looking to do...except I can't Design view or anything to see how all this cool stuff is done! The link is: access-programmers.co.uk/forums/showthread.php?t=154187
Essentially, I want to create a user friendly database like the link above where a clerk/admin assistant can open Access, search for an employee by name or department or manager, and then be prompted to add information such as Letter #2 delivered, manager meeting occured, employee signature, etc.
View 1 Replies
View Related
Jul 24, 2013
I am working on a project where I am creating a database that tracks open orders (old and new orders that still need to be billed but haven't been billed for one reason or another).
I have my "Master" table built and now I need to create 2 queries, one query retrieves the Top 20 orders based on value and that are older then 30 days and the second query is to retrieve the Top 100 orders based on the same criteria but the Top 100 query should not include the orders that appear in the Top 20 query.
My Top 20 query is below
SELECT TOP 20 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER
WHERE (((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;
I have tried multiple ways to do the Top 100 query and exclude what is returned in my Top 20 query but so far no success.
View 10 Replies
View Related
Apr 17, 2014
I am designing a shipping container tracking system for my work and would like to know what the best design is for this.
There are many excel sheets that provide data on the status of containers along the supply chain i.e. shipping schedule, data from the logistics supplier showing status at the wharf, data for movements on our site etc.
Each container has a unique identifier according to what stock it carries and also a container no. designated by the shipping line. I was planning on use the first one as a primary key.
My first instinct was to have a main table that contained all of the information, with queries that updated the fields from the other data sheets.
View 8 Replies
View Related
Nov 13, 2005
Hi,
I have a list of 500 PDA users, spread over 7 offices.
I want to keep an eye on what happens with the devices and their SIM-cards. As they are often swapped between offices and SIM-cards get lost.
I am pretty sure that I don't have to re-invent the wheel.
Do you have any idea of a free/shareware tracking tool for PDA's and cell phones ?
Thanks in advance !
View 1 Replies
View Related
