Queries :: Importing Data From Clocking On System To Analyse Worked Hours - Link Paired Record?
Jul 5, 2013
I have been tasked with importing data from a clocking on system to analyse worked hours.
There is no option to amend the format of the export which is not great
surnameforenameField2Field3xxx
xxx
11/06/201319.18
xxx
xxx
11/06/20135.37+
xxx
xxx
12/06/20135.41+
xxx
xxx
12/06/201319.21
The plus indicates a night shift worker
eg started at 19.18 and finished at 5.37 on the 12th
so for every record I have staff id , date, and clock time ( I have stripped out the + ) and created a yes/no field to identify the records where field2 should actually be field2+1
I have sat in query design screen for ages and cant think how on earth I am going to calculate hours worked for a given staff member and date combination.
for every combination of staff id and date there should be 2 records - a clock in and a clock out
I thought about creating new fields clockin and clockout but struggling to see how I can link the 2 "paired" records together
vb script with dlookup for every record ?
View Replies
ADVERTISEMENT
Feb 12, 2014
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.
View 2 Replies
View Related
Jan 22, 2006
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 4 Replies
View Related
Jun 18, 2006
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 1 Replies
View Related
Feb 23, 2006
Access2000 converts data to Excel2000 in the following way: I have specified a column data type as long integer with no "null" decimal place - whenever I analyse the table with Excel the mentioned column suddenly has 2 decimal places??????????
On the other hand when I convert data WITH 2 decimal places from Access to Excel those are displayed as "zero" (e.g. 9,15 --> 9,00) ?!?!
Thanks for any advise!
View 8 Replies
View Related
Nov 29, 2014
I want to subtract 8 hours of work of the Result of employee work hours/
There is attachment...
View 3 Replies
View Related
Jul 12, 2013
I'm creating an attendance tracking system, the attendance system needs to : Allow an instructor to enroll students into one or more classes, and then add attendance by date to each class(es). The problem that I'm having is, attendance is tracked by the number of hours that a student attends class. For instance, if class1 is in session from 8a.m. - 12p.m. student1 may only stay 2.50 hours of that class and student2 may stay 3 hours. The teacher needs to be able to add attendance to class1 for July 12, 2013 and add hours for each student enrolled in this class session who attended on that day. I have the following so far, minus the attendance:
Students -This is just the basic demographic information that instructors will need to enter on students.
StudentID (PK)
StudentLastName
StudentFirstName
DateOfEnrollment
LevelOfStudy
DateOfBirth
Location - These are the locations in which our students can attend classes
LocationID (PK)
LocationName
County
City
LocationPhoneNumber
LocationFaxNumber
Classes - These are the classes that our students can attend.
CRNNo (PK)
ClassName
LocationID
InstructorID
Instructor - These are the instructors that teach our classes.
InstructorID (PK)
InstructorLastName
InstructorFirstName
InstructorEmailAddress
Enrollment - This table enrolls the student into a class
StudentID(PK)
CRNNo (PK)
LocationID (PK)
View 11 Replies
View Related
Mar 13, 2014
I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.
View 2 Replies
View Related
Oct 21, 2004
Through word of mouth I hear that you can creat a link that can go from Access and link to the personal company system. Is this true? If it is, is there a standard code to use?
View 2 Replies
View Related
Sep 19, 2013
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.
View 12 Replies
View Related
Jan 30, 2015
I'm creating an automated system on access, basically it uploads client's files and analyses their data. The files will always be different, with the amount of fields changing and with different field names each time
One part of it, is appending new contacts to their data. This means records which we can add new contacts to, needs to be duplicated with the new contact placed at the end. So it needs to be like
Company Name New Contact Name
A
B
B Tom
B Harry
Because it's automated with different field names each time, the duplicating part is an issue. I can use the * rule which appends all fields, however this will not work in this case, if we are adding more than 1 new contact, the new contact will be duplicated rather than having 2 new different contacts.
Ideally I want rule saying, append all fields EXCEPT the fields where the new contacts are placed, but I don't think this is possible
I'm using Access 07 for this. Using a mix of VBA and SQL in the modules
View 2 Replies
View Related
Aug 29, 2013
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.
View 9 Replies
View Related
Mar 9, 2007
Man I thought this would be simple, but it quite clearly isn't!
I have a .csv file that contains around 250 email addresses. I simply want to import these into an Access table, each email creating a new record!
NO using the import function only seems to allow you to import data as long as each separate piece of data is on field in a record! I understand whent this might be the case, but why the hell can I not import it so each piece of data is a separate record in the table!!!!!!!!!!!
Please note I understand some sort of complex query may do it, but I am not that experienced with that yet and so don't know how it's done!
Please can anyone offer a simple solution? From where I am sitting, there does not appear to be one!!!!
Thanks.
View 3 Replies
View Related
Mar 9, 2007
Man I thought this would be simple, but it quite clearly isn't!
I have a .csv file that contains around 250 email addresses. I simply want to import these into an Access table, each email creating a new record!
NO using the import function only seems to allow you to import data as long as each separate piece of data is on field in a record! I understand whent this might be the case, but why the hell can I not import it so each piece of data is a separate record in the table!!!!!!!!!!!
Please note I understand some sort of complex query may do it, but I am not that experienced with that yet and so don't know how it's done!
Please can anyone offer a simple solution? From where I am sitting, there does not appear to be one!!!!
View 2 Replies
View Related
Mar 12, 2014
I inherited a defective Access 2010 and am now attempting to import a RECORD consisting of fields of xls data.
I am attempting to import from an existing Form Datasheet so that it overwrites (or appends the new data to the datasheet) from another identical Form Datasheet because it contains an additional 1000 contacts in it.
I have resulted to Using an .mdb file from over a year ago because the current .mdb keeps hitting "The Microsoft Access database engine could not find the object (Error 3011)"
My initial opinion is that the more recent .mdb it is corrupt.
View 4 Replies
View Related
Dec 8, 2014
I want to use the expression Date() in a query to get today's date and then format this to "ddd" to give me the day of the week.
I then want to link this "ddd" to data in my table that contains the three letter days of the week.
How do I make the day become "static" in order to be able to link.
View 6 Replies
View Related
Jul 10, 2014
I have what I think is a normalised database that uses foreign keys quite a bit.
For example if I had a 'contracts' table which refers to 'clients', then there would be a tblClients, where each client has a primary key. The tblContracts would then refer to the relevant client via that clients FK only which would be linked to the client's PK in tblClients via a one-to-many relationship.
If I need any user for any purpose to see contract related information that makes sense to a human being, I simply construct a query with the necessary relationships that will show client information alongside contract information by substituting tblContracts' client FK with required information from tblClients via the appropriate relationships.
I think that is reasonably basic stuff (hopefully correct practice!)
But what about when I come to import a block of new data that needs to go into tblContracts? I'm not going to be given a list of client keys (obviously) I'm going to given their real names.
MS Access has (in theory) all the information it needs (via the relationships) to substitute client IDs (keys) for their real names and thus slot these IDs into tblContracts with the new data as appropriate, but how do I make it do this? (I know it could kick out errors if there are any duplicate client names, but let's put that to one side for a moment).
View 1 Replies
View Related
Mar 4, 2015
I am getting "Truncation Error" while importing data from Excel to access 2007. I have tried changing the format to short text but the result is the same.
View 3 Replies
View Related
Nov 15, 2013
I have a link table that has a field that represents dates - but they are actually just text. It's a long story but the source is not going to change - so I have to try and deal with it. I need it to behave as a date - and am hoping to do this in a query. The data looks like this:
7/24/12
10/08/13
I have tried various things but it does not seem to totally do the trick - if I sort on it it still does not sort as one would expect from an actual date field. How do I do this?
View 5 Replies
View Related
Apr 21, 2013
I created a database. exported some Excel data into Access tables, created a number of lookups, default values etc. and created a pretty simple query.
My problem is that after importing and tweaking the data, when I add new records, my queries do not pick them up!
I have tried:
1. saving, closing, opening and re-running the queries.
2. putting an Nz expression for each field in the query as I read that null values may cause a problem.
3. wrote the query again, field by field to see if all records were received.
4. Exported the table back to Excel and imported to a new Access table in my database
None of this works.
View 7 Replies
View Related
Sep 29, 2014
I have an access file in which have a table name dataupdated
I have an excel 2007 file(Datanew) which have a sheet name data
I want to update table dataupdated at regular period
How can I create a query to import data from excel while file path and sheet name is always same and other steps to import data is same every time...
View 5 Replies
View Related
Apr 14, 2014
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.
View 1 Replies
View Related
Dec 8, 2005
Hi all,
newish to Access so please forgive if this is really easy.
I have a database where people have answered questions to a survey. The returns are
Yes
No
Don't Know
I want to find a way of showing how many of each entries are in each question.
At the moment I am exporting to excell and using pivot tables, but I am sure there must be a way to do this inside Access.
All help appreciated.
Thanks,
Chris.
View 1 Replies
View Related
Dec 14, 2005
Hi! We have a problem with analyse reports generated automatically since we are using Access2000. Attached to this mail you may find a screenshot of the desired report. When we try printing query analyses with the sql syntax exactly that happens (see attachment). Thanx for your help,... Stefan
View 2 Replies
View Related
Aug 10, 2014
I have a table with a list of records, each record has a timeFrom this I have a query, what I would like the query to do, is group the records in the query by hours.
View 1 Replies
View Related
Oct 14, 2013
I need to run a query in Access 2007 to find any records that have been entered in the last 24 hours.
My field is of type datetime and the name in the database is "EntryDate"
How would I check which records had been input in the last 24 hours?
View 2 Replies
View Related