Changing Hours From Daily To Weekly To Accumulate Vacation?
May 21, 2012
I am building an attendance database. The attendance data will be inputted at a daily level. That is working fine and the hours are calculating correctly. The next thing I need to do is create a Table (maybe) that will show the total hours worked in a week. The purpose of this, and the reason I don't want a report, is that the employee will accumulate vacation based upon the numbers of hours worked per week. I will want to be able to show a running total on the vacation, and will eventually create something to allow them to deduct from those vacation hours when they are used.
I have a table with the start and end of their shifts.
We have four possible locations and four weeks.
So I have four tables for each location.
I have a module that can work out hours and deductions based on time in and out. I built that into a seperate databse working on a one time in and one time out setup.
I want to incorporate the two so I can get the hours worked over four weeks at all locations and divide that by 4 to get the average weekly hours for a staff member.
Access Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.
For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.
The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!
At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).
I'm having a spot of trouble trying to get conditional formatting to work. I have an overview form which displays current quotes going through the system, the QuoteStartTime field is generated from the Now() command.I Would like if possible to show, 1 hour = Green, 2 hours = Amber and 3+ hours Red.
I've tried variations of hour([QuoteStart])>1 DateAdd("h",1,[QuoteStartTime])
I've done this before with a date only field before, but working with time is taking too much time.
Oil quantity Accumulate Oil quantity Date Recordnumber
I want to get value of "Accumulate oil quantity" of current record to be equal to sum of "Oil quantity" from first record till the current record:
Let say after I input "oil quantity" in the record # 1 ,2 & 3 The "Accumulate oil quantity" of record value show below:
"Accumulate oil quantity" of record#1 = "Oil quantity" of record#1 "Accumulate oil quantity" of record#2 = "Oil quantity" of record#1 + "Oil quantity" of record#2 "Accumulate oil quantity" of record#3 = "Oil quantity" of record#1 + "Oil quantity" of record#2+"Oil quantity" of record#3
I was wondering if there was a way to make a calendar in Access 2003? I work in a group of 60 employees and we track their vacation, and personal days in a web-based application. The web-based application is going away, as it it no longer supported. Could I use Access to create a calendar for the 60 employees and have it require a user name and password for them to log into the database to look at what vacation they have?
Would Visual Basic or C++ be a better program for this?
There are some ideas floating around these forums on "booking" databases and whatnot, but I have a bit of a different twist with what I am trying to accomplish.
I am trying to develop at database that will allow users to log in and schedule their vacations. I have 2 tables at the moment tblEmployees and tblVacations. In the tblEmployees table you have:
the weekseligible field will be entered manually and be between 3 to 6 weeks of total possible vacation weeks.
The tblVacations has the following fields:
ID (autonumber) weekofyear (date field with week starting sunday for all 52 weeks) vacation1 vacation2 vacation3 vacation4
Only 4 people can take vacation any one week of the year. The vacation fields will be combo boxes where an employee can select their name from a query based on the employees table.
What i need to happen is to prevent someone from scheduling more weeks than their weeks eligible - I have no idea how to do this. My guess is there should be someway to do this using queries and somesort of a count function, but I am having a hard time getting started.
My requirements is to send the alert messages with the approaching due date on every Monday of the week. I am able to send alert messages on Monday. My Problem is: How to send alert messages on Tuesday if Monday is Vacation. Is there a way to do it in access? I don't have much experience in access.
I'm fairly new to access, and have learned much in the past three weeks I've been "learning" how to use it.
I was simply sick of the process that was going on at my place of business where I'm a Manager (I've been there for about 3 months). We keep about 10 Excel forms for the different pieces of information (production numbers and such) we keep for each individual in the department. There are 115 people, 3 shifts working 24 hours a day. Each morning I would go in and open each excel file to analyze what happened the day before.
We're also tracking an individual's days off, tardiness, quality... you name it.
I finally decided it was time for a database... I had worked with them in the past, but I had never actually "built" one. I've actually knocked out a significant portion of what needs to be done, and I'm proud of my product thus far... except for one thing:
Vacation accrual is "kicking my posterior"!
I've searched these forums (finding some answers, most of which I don't understand), but I almost need to be handed the answers. My other struggles I've overcome with the help of these very forums and Google.
I'll lay out our policy and what I need and hopefully someone can help me. As I learn access (and I will!), maybe one day I could come back here and help someone else that may be in need.
Our policy is as follows:
90 Days to be eligible for accrual.
1-3 years of service - 6.67 Hours a Month (10 Day a year or 80 Work Hours... all the same, and whichever makes things easier) 4-7 years of service 10 hours a month (same deal as above as 15 days or 120 work hours) +8 years -13.33 hours a month (blah blah)
The hours do NOT carry over, they are all use it or lose it. I can make manual entries when necessary, so it doesn't have to be all "automatic".
Like I said, the searching I've done has come up with some things, but not in the language I yet understand. I do know "modules" and some expressions, but the more that could be "spelled out" the better.
I've got your (what I consider) normal Employee fields, and would like to use something like "DateStarted" as a beginning point, but I'm open to anything.
Thank you VERY much in advance for any help that can be given. This has stalled my project almost a week trying to figure out a solution!
I'm sure this must have been the subject of a thread before, but after an hour seaching my head aches.
I'm running (Access 2000 on XP Prof) a sort of planning board. The week commencing dates change automatically, as per previous postings on here. I have a job run from a command button to then change a field representing the week the job is to be run in ie week 2 jobs to week 1, week 3 to week 2 and so on. So far so good.
I would like this job kicked off automatically once the first time the db in opened in the new week. Any advice or pointers to appropriate thread welcome.
I'm not really sure what to do on this one, I'd like to hear some suggestions on what everyone thinks..
I have a database where users enter information Bi-Weekly into a form. I have 2 date fields set up, one is called the RecordDate, it is the date at which the user has entered the actual data into the database. Ignore this date.
I have another date, called MainDate, which is the date that all departments need to have submitted the reports (Each department fills out these bi-weekly reports [approx 100 departments]), meaning that one complete set of 100 reports is entered every 2 weeks. Even though they are all entered at various dates within those 2 weeks, usually almost at the end of the 2 weeks, all of the reports will contain this MainDate and it will be the same for all departments every 2 weeks.
I'm looking for the easiest method to accomplish letting a user select these bi-weekly dates and making sure that the dates are correct, validation?.
Some thoughts I had 1) For instance, if typing in the bi-weekly dates, I want it so that they can only type in the dates of every 2 weeks, no other dates will be allowed.
** Best option in my opinion 2) Have a small calendar that expands when clicked right next to the MainDate field so that on the calendar you can only click on dates every 2 weeks, or have every 14 days a different color on the small box for the date. [the users seem to like the ease of using calendars, one of the reaons is is a good option in my opinion]
3) Have a dropdown? That shows the current date that is allowed to be selected based on some sort of 14 day interval cycle. (i.e. after 14 days of one cycle are up, the only option in the dropdown is to click the next MainDate, which will be exactly 14 days from that just passed.
Anyway to have this date automatically filled in after 14 since the last MainDate entry? Bi-Weekly reports are due every second Thursday.
I am creating a booking system and I need to create a query for a report that will show how many bookings are made per week and the total of the bookings. I have managed to make a query that will get the number of bookings and the total, but i have no idea on how to do this for each of the weeks in a month. From this query i hope to then make a report showing 4 weeks at a time.
On the attached jpg(s) you will see the query I'm working with. It's fairly simple except for a couple of things and those are the items I'm stuck on.
What I'm trying to do is get a representation of the amount of traffic that has come through our salescenters in the last week. Some centers had no traffic and others had multiple prospects. So when I look at the query for week 33 (this week) I would like to see all the centers even if they had zero traffic - the query should show zero.
If you look at Traffic_Query.jpg you'll see how the table is laid out. With the query I'm trying to show all of our centers for the current week even if no traffic was entered.
I may not be explaining it clearly so if you have any questions let me know.
Thanks, Chester Campbell Joseph Freed and Associates
I am just starting to use Access and have set up many simple forms, datasheets, reports, summaries, etc. I have not worked with macros, etc. and need help for this project. I have set up a task table
F1 - DateOfCall - date - now() F2 - Client - text F3 - DateCompleted - date F4 - DueDate - date F5 - problemDescription - memo F6 - Resolution - memo
I need to run a query and/or report every week summarizing what has been done the past week. Would also like to view previous weeks. I need help. How do I go about doing this.
My next challenge/task is to create a way to record weekly attendance. I have looked at several databases available online but not that really reflect what I need. Also I am trying to do it the simpliest way possible (for now) with my limited knowledge of VBA code.
Here is what I am hoping to do. I have a form linked to a query that will select the students assigned to a faculty member in all the courses that faculty teaches. I would then like to be able to use the same form, and a second combo box, to create records in an AttendanceTable that stores the attendance for that student in that course for that week. I have created a key using StudentID-CourseID to identify each student with each of the course they are enrolled in. It is also used to relate all the involved tables.
We import data to a table on a weekly basis. We delete the table and replace the data. Most of the data is the same. It's date's, group names, ...
Now we would like to save the weekly table and add a saved date. Then we would like a query that puts all the saved tables together and that we could filter.
For example. A team is planned to do a work on 01/01/13. The next week it has changed to 07/01/13. So the data in the table it updated but we can't track the changed date. If we had the different tables we could filter that team and so that the work was originaly planned on date x to be done on the 01/01/13, on date y it was 07/01/13....
Of course if we would save the complete table every time it would mean a lot of useless data that hasn't changed. So a better solution would be that a query could see which data has changed and it only saved those lines on that date.
Below is the code that I have for a query. Currently the GrandTotal Column appears to the left of all the Weekly Columns. Is there a way I can have the GrandTotal column appear to the right of all the Weekly Columns? The weekly columns will expand or contract depending on the dates selected.
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 ); TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal FROM [Trends-1-3TON-WEEK] GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
I'm new to Access, and I've been able to figure out a way around most of the problems I've run up against, but this one has me completely stumped. I would really appreciate any help!
I need to create a query that will produce a report that will follow the sales performance data of new agents for one year. I need to have two pieces of data for each week: number of policies sold and total value of policies.
The format looks like this:
Jan1 Jan2 Jan3 Jan4 Feb1 Number 12 4 6 3 6 Value 1554 320 229 221 1824
I have been pulling weekly sales data from an external database weekly and importing it into individual tables in my Access database. I had no problem for the first 3 months, but now I am getting the error message "Too Many Fields". If I understand it right, you can have up to 255 fields in a query. I don't have anywhere near that many. What could be wrong?
I have an excel report which I would like to run through Access to drive trend analysis and compare with other similar reports. The excel report has a cumulative spend figure each week and not the actual weekly spend numbers, the budget figure also can change depending on the actuals.
Excel report:
Week 1 Product ID Customer Yearly Budget Spend
1122 Sam 100 3
1123 John 200 4
[code]...
Will I need to create a new table each week or can I link the file and it updates automatically?Can Access store the weekly data and just update it one week at a time?
