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$
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!!!
My subreport provides appointment stats for every month and is grouped by month in the main report. I manage to include the number of days for each month using:
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
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.
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 .
I have a table of employees, and dates they worked on. These are seasonal employees who want to get in as many days as possible before the season ends, but regulation states they must take a compulsory rest day after x days.So I need to create a query that can return the list of employees, with a count of consecutive workdays up until current date.
If today is 20/09/2013, and Johnny worked on, 19, 18, 17, 15, 14, 13, his count must be 3, because he was absent on 16. Therefore only from 17 through 19 is regarded as consecutive.
If Peter worked 19, 18, 17, 16, 15, 14, 13, his count would be 7, because unlike Johnny, Peter still worked on 16.
I have a table in access that has 1 ) persons ID and ) log date/time 3) direction in or out
I need to calculate the amount of time spent by the person in the office. e.g User with ID 1 will come in at 8 in the morning and go out after an hour then come in again and go out. Till he leaves out for the day. I need to find the first time he came in and last time he went out and find the difference for the hours he worked
Hi, I have a main form with a subform inside it. The subform contains two records but only one is shown during form view, is there any way to show both records at the same time?
I am using continuous forms view for my subforms as i have to.
i want the query to do this 12/02/2001 02:00 18 12/02/2001 05:00 25
i cant find anything anywahere and its use ids for a chart in which the chart wizard allows grouping by 1 hour and not by more than 1 hour any help will be much obliged
This crosstab query has the employee's name as a row heading, each calendar day as a column heading and the sum of Hours worked as the value.
TRANSFORM Sum(tbl_Tracker.Hours) AS SumOfHours SELECT tbl_Employee_Master.Full_Name FROM tbl_Employee_Master INNER JOIN tbl_Tracker ON tbl_Employee_Master.user_no = tbl_Tracker.User_Id GROUP BY tbl_Employee_Master.Full_Name ORDER BY tbl_Employee_Master.Full_Name, tbl_Tracker.TheDate PIVOT tbl_Tracker.TheDate;
It returns data that looks like this: FullName 2/3/2014 2/4/2013 2/5/2014 John Smith 8 0 8 Joe Jones 0 8 0 Cathy Wise 8 8 8
I want the data returned to look like this: 2/3/2014 2/4/2014 2/5/2014 John Smith Joe Jones John Smith Cathy Wise Cathy Wise Cathy Wise
The hours worked are not important - only the names associated with the dates.
I ran a very similar code with near-identical SQL, with only the fields and tables changed, and it had worked before. However, after changing the fields and tables, I repeatedly get error 3134 for "syntax". The code was
Code: Private Sub UpdateI_Click() Dim PerName As String, strsubb As String, Popup As Role, Strsub As String PerName = InputBox("What is their name?") strsubb = "INSERT INTO Company-personID (CompanyNo, Name) VALUES ('" & Me.CompanyNo & "', '" & PerName & "');" DoCmd.RunSQL strsubb
[Code] ....
You can ignore the userform and inputbox if they're not important in this, but I figured they might just be. When I run "Strsub" and "strsubb" by themselves, they both come up with errors, so it's not one or the other. There are two tables in this. A table of companies, which has its form open, and a child table of companies and their people.
I wanted strsubb to put in a record where a person is added, their name based on the inputbox, and their associated company to be marked as the one that is in the field "companyno".
Strsub is meant to set the same person's roles to be whatever was clicked on the userform. If it's important, the roles are in a lookup wizard with multiple choices.
Can anyone tell me if there is a way of displaying a number, which represents the number of minutes of elapsed time, into the format days:hours:minutes elsapsed?
For instance the number (in minutes) 1530 would display as = 01:01:30. 1 day, 1 hour & 30 minutes of elapsed time.
Missing something simple here and can't figure it out. I want to display a time in military 24-hour format but can't get it to show the first "0" between the times 0000 - 0959.
I have a very large dataset with over 11 million data records in three different databases! Currently the data is collected on average every minute, which is much too detailed! I would like to pull data for every site on the hour, or somewhere near the hour as some data was collected at different time intervals. (this latter part is a completely different issue, I would be happy just to get my first query working, then deal with data not collected exactly on the hour)
I wrote a query and tested it out on a very small portion of the data. It seems to be working, but for some reason won't pull data for the 2 pm, 5 pm, or 8 pm hours...even though the data is there in the table. Any reason why this may be? Even if I simplify the query to only pull out those hours, and not all 24 at once, the data still doesn't get selected. I really can't think of why it's not working. Any suggestions?
I've currently set up the Criteria in the time column of the query to look like this: #1:00:00 AM# Or #2:00:00 AM# Or #3:00:00 AM# Or #4:00:00 AM# Or #5:00:00 AM# Or #6:00:00 AM# Or #7:00:00 AM# Or #8:00:00 AM# Or #9:00:00 AM# Or #10:00:00 AM# Or #11:00:00 AM# Or #12:00:00 PM# Or #1:00:00 PM# Or #2:00:00 PM# Or #3:00:00 PM# Or #4:00:00 PM# Or #5:00:00 PM# Or #6:00:00 PM# Or #7:00:00 PM# Or #8:00:00 PM# Or #9:00:00 PM# Or #10:00:00 PM# Or #11:00:00 PM# Or #12:00:00 AM#
Is there a better way to go about this? Thanks!
Ok, I am still interested in knowing why the above query wasn't working should anyone know. But while thinking of how to deal with my other data not on the hour...seemed to have made a few queries that do the trick! Access is great...but sometimes very slow to get what you want done when you're still learning!
Hello my beautiful worldwide friends :D I am trying to calculate employee Overtime hours from their recorded TimeIn and TimeOut if over 7.5 hours. So anyday they work past 7.5 hours should be calculated and totalled at the end of the week. And i can't seem to figure it out, maybe my maths is bad? Maybe it's my query? The section of my report? What could i be doing wrong here, i have spent hours and im getting no where.
I have attached my report. Can you help me figure out why my daily total shows but the weekly total is not showing?
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.
I’m trying to create a query that will COUNT the number of values within 1 hour time bins (e.g. 00:00 to 00:59, 01:00 to 01:59 etc). The catch is that I need to have every hour bin represented whether there is any value in it or not. I believe that I need to create a separate table of the bins that I want (include bin start-value & stop-values) and then bring this table into my query and join it to the original table.
I was able to construct the make table query below to COUNT the values in hour bins for which there were records, but I also need rows for all of the zero values;
SELECT [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]) AS [Year], DatePart("m",[All NWHI Detections.Date]) AS [Month], [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed] AS [TimeSinceTagged(days)], CDate2Julian([All NWHI Detections.Date]) AS [Julian Date], DatePart("h",[All NWHI Detections.Time]) AS HourBin, Count([All NWHI Detections].Time) AS TotalNumberofDetections INTO [GLM HourlyBins] FROM SharkTaggingSummaryTable INNER JOIN [All NWHI Detections] ON SharkTaggingSummaryTable.Transmitter = [All NWHI Detections].Transmitter GROUP BY [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]), DatePart("m",[All NWHI Detections.Date]), [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed], CDate2Julian([All NWHI Detections.Date]), DatePart("h",[All NWHI Detections.Time]) HAVING ((([All NWHI Detections].Species)="tiger")) ORDER BY [All NWHI Detections].Transmitter;
Q1? Creating the hour bins table
This table will be very large because it will need to have 4 years worth of hour bins for each transmitter (N=15) and location (N=12). This results in 6,307,200 hour bins! How can I write a query to create this table, or is there a better way of doing this?
Q2? Bringing the hour bins table into the main query.
How do I include the hour bins table in the main query to get my final result.
is there a way to count - in one query - the number of records that fall between specific times:
Time CountOfRecords 0000-0100 7 0100-0200 2 etc 2300-2400 4or do i have to do separate grouping queries then separate counting queries for every hour of the day? a crosstab? something else perhaps? feels like i'm missing something obvious (i hope...).
I am trying to round off times to the nearest half hour. To be clear, I don't want to only round down or only round up. I need the rounding to be to the nearest half hour. I want to do this in the query, not vba. I've attached a picture of the query.
I have a form in continuous forms mode... Each entry is something the user is supposed to check on once and hour and update. I have a "Last updated" field. Now() goes into that field every time the record is updated. I have a querry that will show the users records that are over an hour old.
How can I get Access to pop-up a window or something to alert the user they need to look at something? It would be really nice if Aspect would start blinking in the task bar or something.
