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

Queries :: Crosstab Query - Sum Of Hours Worked

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

Need Help With Time/ Hours Worked Calculation

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

Time Keeping DB - Best Field Format For Working Out Hours Worked

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

Analyse Access Data With Excel

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

Queries :: Subtract 8 Hours Of Work From Result Hours

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

Tables :: Attendance Tracking System - Adding Hours For Each Student Enrolled In Class Session

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

Tables :: Primary Web Based Inventory System - Update Raw Data From Another System

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

Code To Link From Access To Company System

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

Queries :: Consecutive Days Worked?

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

Queries :: Automated System - Appending New Contacts To Data / Different Field Names

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

Queries :: Error 3134 On A Query That Worked Before

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

Importing .csv Data Each As An Individual Record

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

Importing .csv Or Deliminated Data Each As An Individual Record

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

Forms :: Access 2010 - Importing RECORD Consisting Of Fields Of XLS Data

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

Queries :: Extract Day Of Week From A Date To Link To Data

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

Queries :: Importing Data Where Foreign Keys Are Involved

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

Queries :: Truncation Error While Importing Data In Access 2007

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

Queries :: Link Table To Represent Data - Format Text As Date

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

Queries :: After Importing Data Query Not Picking Up Newly Added Records

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

Queries :: Importing Data From Excel 2007 When File Path And Sheet Name Always Same

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

Queries :: Number Of Days Between Two Dates With Irregular Days Worked

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

Analyse Results In Any Format

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

Access2000 - Analyse Report

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

Queries :: Query Group By Hours

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

Queries :: Check Entry In Last 24 Hours?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved