Calculating Employee Overtime Worked (weekly Total)

Calculating Years Employees Have Worked For
hi, i want to calculate how long each person has worked at a company.

i am using access 97.

would i be able to enter something in the criteria that would work this out for me?

something like year([START DATE]) - year(Now())
this does not work but i want to find out the number of years a person has worked for the company from the field START DATE .

thanks for you help.

View Replies !

---

Use A Query To Do A Running Total & Comparison With Weekly Data Points
I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:

View Replies !

---

Running Total Per Employee
I've been tasked to create a report that shows the date an employee hit a loss of $200.00 or greater for the company. Each day they work, they will either have an overage or a shortage in their till fund. They start out each day with a set amount of money in their till and at the end of the day they are expected to turn in that same amount; the tills are used for making change. If they are short, and the amount is fairly large, hence the $200.00 mark, then it becomes an issue that needs investigating.

I need to total these amounts up and when the total hits a negative $200.00 or greater, I need to display the date it occurred and then display the amount the company would have saved if the employee had been either terminated or transferred to a non-revenue department.

Getting the "saved" amount should not be a problem once I figure out how to get the above indicated loss amount.

Any ideas?

Kevin

View Replies !

---

Need Help With Calculating Total Row In Query
I quess it should be simple. But I couldn't find an answer or example in Access books and online.
I have an access 2000 database with a Accounts Receivable table. I am building a query that returns accounts what are 60 days past due and calculating total ballance of ONLY past 60 days accounts. I managed to write a code to display 60 days past due acounts, but when I use UNION query, it calculates total from entire Accounts Receivable table, but I need only total ballance of past due accounts.

Here is my SQL code

SELECT [Accounts Receivable].InvoiceID, [Accounts Receivable].[Patient Last Name], [Accounts Receivable].[Patient#], [Accounts Receivable].InvoiceDate, [Accounts Receivable].PaymentAmount
FROM [Accounts Receivable]
WHERE ((([Accounts Receivable].InvoiceDate)<Date()-60))
UNION SELECT 'TOTAL', "","","",Sum([Accounts Receivable].PaymentAmount)
FROM [Accounts Receivable];

I need my query to look like this.

InvoiceID Patient Last Name Invoice Date Payment Amount

23 Smith 05/01/2007 $100
25 Doe 04/03/2007 $200
Total - - $300

Please help.

View Replies !

---

Calculating Total From A Subform
Hi,
Attached is a project I'm working on. We want to calculate the total delays on each day. In a form called 'main' there is a subform for the delays on that date. But the 'Total Delays' control does not work.
I have used the following format to get the total from the subform:
=Delays.Forms!TotalDelay
'TotalDelay' is the name of the control which calculates the total of the delays within the subform.
I have used the =[Delays].[Forms]![TotalDelay] format in other projects and it works. However in this program when I want to save the form, the control source is changed to :=Delays.Forms!TotalDelay (the brackets are removed)
Thank you

View Replies !

---

Calculating Total In A Subform
I have a Main form with a sub form. Every record in the main form has multiple sub form records. (they are linked in a one to many relationship)

Each subform record has a "price" text box. I would like a total of all the "price" text boxes for that main record to be displayed in the main form.

It needs to be updated when a new subform record is added or changed.

Any help is welcomed. Thanks in advance
Joe

View Replies !

---

Calculating Total Cost
I am currently making a database project for a school assignment.

I have based the project idea on a local hotel, which consits of a diffrent price dependant on the date booked.

So i have set up a table called "prices" which consits of the following fields:

PriceID
RoomID *
Price
Start Date
End Date

the RoomID is the foreign key, as it is linked (one-to-many) with my Room's table, which consists of the following fields:

RoomID *
Room Number
.. (unecessary fields for the problem)

The room ID is also linked to the bookings table (again one-to-many) with the bookings table consisting of:

BookingID
CustomerID
RoomID
Start Date
End Date
... (uneccessary fields)


what i am hoping to do is (i assume) make a query that will calculate the cost the customer has to pay. So it will look at the start and end date booked in the bookings table and comapre it to the dates in prices table, and find out the cost from this....

any help on going about this would be greatly appreciated, and i hope i have supplied sufficient information (i'll try attach a graphical image of the relationships if that is helpful?)

thanks

michael

View Replies !

---

Calculating Records Total
hiYA everyone!
in my report footer, i want a number of records displayed
in the report. how do i do that? my report is based on a query.

View Replies !

---

Calculating Total Costs From 2 Different Fields
I have a table with products and their costs per unit of measure.

some products are costed by weight and others by piece.

table looks something like this:

product
pcs
weight
unit of measure (EA or KGS)
cost

Query should look like this:

product
pcs
weight
Total Cost

Is it possible to write an expression that, depending on the unit of measure uses the pcs or weight field to calculate the total cost ??????

View Replies !

---

Calculating Fiscal Year Total
I need to provide a report on data over a fiscal year that has the following format:

apr 03
may 03
jun 03
q1 total
jul 03
aug 03
sep 03
q2 total
oct 03
nov 03
dec 03
q3 total
jan 04
feb 04
mar 04
q4 total
year total

I can group by quarter and sum() in the group footer which works, but when I group by year I get totals for 03 and 04 separately. I have also tried sum() and dsum() in the page footer, which gives #error, and vba in page footer:

Dim pageTotal As Long
Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
' Add to Page Total
pageTotal = pageTotal + Me![ClientsServed]
End Sub
Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer)
' Show Page Total Price
Me![yrClientsServed] = pageTotal
End Sub

which calculates properly in the detail section (var watching "pageTotal") but outputs nothing in the footer, though the name of the text box in the footer is correct.
If anyone can see an error in what I'm doing or suggest another approach, I would really appreciate it, this is driving me nuts.

View Replies !

---

Calculating Total Prices Only For Specific Records
Look at this query:

http://i237.photobucket.com/albums/ff304/hankymskdghdfag/qryUvoz.jpg

I wan't to calculate the price totals but for each instance of Uvoz ID separately. I've sucessfully used Dsum function but I managed only to calculate all price totals for all instances of Uvoz ID. Is it possible to do it for each number of Uvoz ID separately? How do I do it?

View Replies !

---

Calculating The Total Time Of All Recorded Time (MS Access 2000 And 2003)
Hello,

Here I am having a little bit confusion about how to calculate the total time of steps of a process (for example: registering a new user for a website) in MS Access:

- step1: entering a user name in a text box will take 00:00:10,94
- step2: entering a user password in a text box will take 00:00:03,12
- step3: entering a user confirmation password will take 00:01:30,16
- step4: entering a user email address in a text box will take 00:00:07,79

so, the total time of each step will be 00:00:52,01

the idea is that I have two fields in a table named "Total Time".

the first field i named it "Process Type", where I type each step of a process as I mentioned above. And the second field I named it as "Timing", where I type the time manually for each step.

in a form, there will be a control source "text box" that will calculate or sum all time that are in the "Timing" field which are listed manually by me.


I need a solution of it, because it is my first time to deal with this concept????

View Replies !

---

Number Of Hour Worked
Hello!!!
I've a trouble:
I have an Access DataBase with two tables:
1--Workers with fields (ID, Name and $/hour)
2--Marks with fields(ID, WorkerID, Datetime of mark)

Well, I need to generate a report with the following structure:

Name Worker / Number of days worked / Total Cost in $
---------------------------------------------------------------------------
Worker 1 / 19 / 13000$
Worker 2 / 20 / 21354$
Worker 3 / 12 / 12000$
...

Example of Workers Table Data:
ID / Name / $/hour
-------------------------------------------------------------------------
21367 / Worker 1 / 21,34$
54883 / Worker 2 / 54,87$
87364 / Worker 3 / 5,38$

Example of Marks Table Data:
ID / IDWorker / DateTime-Mark
-------------------------------------------------------------------------
6754-54 / 21267 / 19/05/2006 8:05:32
4327-12 / 54883 / 19/05/2006 8:07:12
7645-98 / 87364 / 19/05/2006 8:13:54
5432-99 / 21267 / 19/05/2006 14:05:32
7584-23 / 54883 / 19/05/2006 14:07:12
9485-23 / 87364 / 19/05/2006 14:13:54
... / 21267 / 19/05/2006 16:05:32
... / 54883 / 19/05/2006 16:07:12
... / 87364 / 19/05/2006 16:13:54
... / 21267 / 19/05/2006 20:05:32
... / 54883 / 19/05/2006 20:07:12
... / 87364 / 19/05/2006 20:13:54
... / 21267 / 20/05/2006 8:05:32
... / 54883 / 20/05/2006 8:07:12
... / 87364 / 20/05/2006 8:13:54
... / 21267 / 20/05/2006 14:05:32
... / 54883 / 20/05/2006 14:07:12
... / 87364 / 20/05/2006 14:13:54
... / 21267 / 20/05/2006 16:05:32
... / 54883 / 20/05/2006 16:07:12
... / 87364 / 20/05/2006 16:13:54
... / 21267 / 20/05/2006 20:05:32
... / 54883 / 20/05/2006 20:07:12
... / 87364 / 20/05/2006 20:13:54

The condition to increment number of days is:
YEAR(date-mark) != YEAR(date-mark) OR
MONTH(date-mark) != MONTH(date-mark) OR
DAY(date-mark) != DAY(date-mark)

for example:
1)
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
-----------------------------------> 1 days worked
2)
20/05/1982...time-mark // Worker 1
20/05/1982...time-mark // Worker 1
21/05/1982...time-mark // Worker 1
21/05/1982...time-mark // Worker 1
-----------------------------------> 2 days worked

Do you understand it?

Well, I've tried several SQL sentences, but I can't make the definitive sentence. Can you help me, please?

Note --> each Mark-Datetime represents an instant of time!!!

View Replies !

---

Need Help With Time/ Hours Worked Calculation
Hi Guys

I need to create a method of calculating -week number,hours worked per week and a leiu hours total.

Explanation

Users enter their worked hours per day. I need to calculate the total hours worked per user per week and if this is greater or less than their contracted hours a record of this figure over a continual time period (or year).

Variables :- Time worked,Holiday hours,Leiu hours taken,Sick leave, Other authorised leave.

I realise that i'm asking on a lot but working for a charity has financial limits.

Attached the basic database-so far

Thanks in advance for your help

Row

View Replies !

---

Time Keeping DB - Best Field Format For Working Out Hours Worked
Hi, I am creating a time keeping db.

Before I jump in and start creating a way to record times worked I thought it best to ask for advice.

The db is created and the last part is to capture by member:
Basically I have the following fields:

MemberID
Job Type
Date of Job
Time Started
Time Finished

From the data captured I need to create a report to show month and ytd hours worked.

It would be easy to ask the user to calculate the hours worked and input the number of hours, however I would like to capture start and finish times.

Can anyone give me any pointers on the best field types and field formats for capturing times which would then make it easier to work out hours worked.

Thanks in advance

Darrell.....

View Replies !

---

Weekly Job.
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.

Ta

View Replies !

---

Total Daily Sales Queries By Model/Total
Hi,

1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.

2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)

3) I start with daily (Lets don't be too overly ambitious).

4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.

5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?

PS: Please forgive my ignorance :o:(

Thanks (In advance) & God Bless.

View Replies !

---

Bi-Weekly Dates
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.


Thanks in advance to any input you can offer
Dave

View Replies !

---

Weekly Query
hi,

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.

Any help would be great. :)

View Replies !

---

Query Weekly Totals
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

View Replies !

---

Weekly Summary Of Tasks
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.

View Replies !

---