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 Replies


ADVERTISEMENT

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 10 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

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

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

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 :: 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 :: Setting Zero Value In Crosstab Query

Jun 19, 2014

I have a crosstab query to summarise the counted string values from another query: E.g.;

TRANSFORM Count(Table1.Viable) AS CountOfViable
SELECT QryTable01.productName
FROM QryTable01
GROUP BY QryTable01.productName
PIVOT Table1.Viable;

As there are some null values returned (ie blank cell in the pivot table)

I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;

So if I add a text box in the report with = [viable] + [Not viable]
t
Then the report row with the values:

Viable Not viable
14 12
displays as 1412

So how do I add the values in the rows???

View 1 Replies View Related

Queries :: Data On 1 Row Crosstab Query

Sep 17, 2014

Below is the SQL view of a Crosstab Query I am running. However I am lost at how to keep it from creating a serpate row for the same data on the column. For instance in the row data if I have an ATM batch come in for 1111 on 9/16 and then one come in for 1111 on 9/17 instead of keeping it on the same row it drops 9/17 down to the next row. Is there a way to keep the data on the same row?

Code:
PARAMETERS [forms]![frmReporting]![txtDatefrom] DateTime, [forms]![frmReporting]![txtDateTo] DateTime;
TRANSFORM Sum(Query1.CountOfATM) AS SumOfCountOfATM
SELECT Query1.ATM, Query1.Store, Query1.Type, Sum(Query1.CountOfATM) AS [Total Of CountOfATM]
FROM Query1
WHERE (((Query1.[Date Stamp])>=[forms]![frmReporting]![txtDateFrom] And (Query1.[Date Stamp])<=[forms]![frmReporting]![txtDateTo])) OR (((Query1.[Date Stamp]) Is Null))
GROUP BY Query1.ATM, Query1.Store, Query1.Type, Query1.[Date Stamp], Query1.[Date Stamp]
PIVOT Format([Date Stamp],"Short Date");

View 4 Replies View Related

Queries :: Crosstab Query With No Aggregation

Apr 16, 2013

I have the table "tblProductionForecast" with the following fields:

ProductionForecastProductId
ProductionForecastMonth
ProductionForecastQuantity

I want to show this in a report, similar to an spreadsheet, with:

ProductionForecastProductId = Rows
ProductionForecastMonth = Columns
ProductionForecastQuantity = Data

I thought of using a crosstab query like this:

Code:
TRANSFORM avg(tblProductionForecast.[ProductionForecastQuantity]) AS AvgOfProductionForecastQuantity
SELECT tblProductionForecast.[ProductionForecastProductId]
FROM tblProductionForecast
GROUP BY tblProductionForecast.[ProductionForecastProductId]
PIVOT tblProductionForecast.[ProductionForecastMonth];

but I don't want the average of ProductionForecastQuantity. I want the actual value!

Is a crosstab query the wrong method to use?

If no, how do I show the actual value of ProductionForecastQuantity

If yes, how should I create the report?

View 5 Replies View Related

Queries :: Sum Data Across Crosstab Query?

Nov 12, 2013

I have a crosstab query that I would like to sum data across. Each row is a seperate experimental product, and each column is a test phase that each product goes through. The data is the number of days each product was in each test phase. What I need is the sum total of days that all products were in all phases. Think of this table as a single item, and each row is a slightly different version of the item to test. So what I need is essentially how many days this item has been in testing across all variations.

View 1 Replies View Related

Queries :: Query Error When Data Changes In Crosstab Source Query

Aug 12, 2014

I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.

I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .

My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.

View 9 Replies View Related

Queries :: Sort Row Headings For Crosstab Query

Feb 26, 2015

I have a created a crosstab query which gives me the results I need, but I want to sort the row headings differently. These are not numbers, but machine sizes which range from 4 Metre to 20 Metre. Currently, the crosstab gives me:

10 Metre
12 Metre
14 Metre
17 Metre
20 Metre
3 Metre
4 Metre
5 Metre etc

But I need to show it as:

3 Metre
4 Metre
5 Metre
10 Metre
12 Metre
14 Metre
17 Metre etc.

The field is short text data type and data comes from an ODBC linked table to SQL server table, and I am using Access 2013.

View 4 Replies View Related

Queries :: Crosstab Query With Fixed Row Headings

Apr 23, 2014

I've setup some crosstab queries with good success and set column headings that appear with blank spaces when there's no data as expected, but I have 1 report that is formatted with set row headings (its a specifically formatted report that i need to produce, that i can't just change).So I need to basically do the same but with the row headings set (I have got the crosstab working, just not showing the rows with no data as yet).

View 3 Replies View Related

Queries :: Crosstab Query Using Dates For Next 12 Months

Jun 23, 2014

I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.

I could represent an activity using a colour i.e. yellow for delivery, green for design etc.

Also do I need to create a table with all the dates? Is there a quick way of doing this?

View 7 Replies View Related

Queries :: CountIf Between Dates In Crosstab Query

Nov 4, 2013

I have generated a table using a crosstab query and it gives the information as below

12/10 12/11 12/12 13/01
Part 1 1 2
Part 2 4 4 4
Part 3 5

So basically part, qty used, year/month used - it works fine up to this point.

Now I want to either add more columns to the crosstab query to count number of times Part 1 was used in last 12 months & 24-13 months

Or

Create another query to do this.

But I want the count function to start counting from current month to last 12 months and from month 13 to month 24 (in another column).

The problem I am having is that this query will run every month and I don't want to update the column headers (in query property sheet) instead I want query to pick this up automatically.

I use the design view to generate queries.

View 1 Replies View Related

Queries :: Add Percentage Column To Crosstab Query

Apr 12, 2013

I have created a cross tab query that contains a row heading for Entity and Total Cases. I would like to have a percentage of the Case Total for Each Category as well as the count for each category. It works fine for just the counts and here is the sql behind it:

TRANSFORM Count(CASEDATA.CASESTATUS) AS CountOfCASESTATUS
SELECT IUIDCODES.CODEDESCRIPTION, Count(CASEDATA.CASETYPECODE) AS CountOfCASETYPECODE
FROM (CASEDATA INNER JOIN CASESTATUSCODES ON CASEDATA.CASESTATUS = CASESTATUSCODES.CODEID) INNER JOIN IUIDCODES ON CASEDATA.IUID = IUIDCODES.CODEID
WHERE (((CASEDATA.CASETYPECODE)=63) AND ((CASEDATA.FYSTATUS)=6 Or (CASEDATA.FYSTATUS)=7 Or (CASEDATA.FYSTATUS)=8))
GROUP BY IUIDCODES.CODEDESCRIPTION
PIVOT CASESTATUSCODES.CODEDESCRIPTION;

View 2 Replies View Related

Queries :: Filter Not Working In Crosstab Query?

Apr 17, 2014

I have a table like this

Cultureworkload
ReasonfortestingIRLNumberDiagnostic sputum specimens processedFollow up specimens processedReception
Follow up7/20130102-Jan-13
Diagnosis8/20131002-Jan-13
Follow up9/20131002-Jan-13
Diagnosis10/20131002-feb-13
Follow up11/20131003-feb-13
Diagnosis14/20131003-Mar-13
Diagnosis15/20131003-Apr-13

I built a query with the wizard to group by Reason for testing

The SQl was

TRANSFORM Count(Cultureworkload.IRLNumber) AS CountOfIRLNumber
SELECT Cultureworkload.Reasonfortesting, Count(Cultureworkload.IRLNumber) AS [Total Of IRLNumber]
FROM Cultureworkload
GROUP BY Cultureworkload.Reasonfortesting
PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

Although I filtered reception field by month to 1st quarter it shows empty months like Apr,May Jun etc.

View 3 Replies View Related

Queries :: Adding Fields To Crosstab Query?

Oct 9, 2014

I have created a crosstab query that I will use in a subform and a subreport. However, Access won't let me add the fields to the query that I need to link the subform to the form. Is it possible to add fields to a crosstab query?

View 6 Replies View Related

Queries :: How To Use Criteria Or Parameter In Crosstab Query

Oct 5, 2013

I have a table that has three columns: Nationality, Gender, and Date. First I had the problem of grouping the Nationality column by Gender. That i resolved by creating a crosstab query. Now, I want to put a criteria on Date field so that when the query is run it will ask for the date input and will only show the Nationality and Gender result for specific dates. But when I write something in Criteria field it shows an error saying: "The Microsoft Access database engine does not recognize "[Registration Date]" as a valid field name or expression"..

View 3 Replies View Related

Queries :: Crosstab Query Which Can Append To Table

Jan 8, 2015

I have a crosstab query which i would like to append to my table..can't change it to a append query...it changes the structure.

View 1 Replies View Related

Queries :: Cannot Get Crosstab Query To Work In A Form

Dec 30, 2013

When I run this query

TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt
SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
FROM [rpt LEAD TYPE SUCCESS RATE2]
GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;

I get valid results.

When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.

I am running MS Access 2010 on a Window 7 pc.

View 10 Replies View Related

Queries :: Crosstab Query - Counting Number Of Records For Each FY

Sep 19, 2013

I have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form.

View 7 Replies View Related

Queries :: Rounding Value In Calculated Field In Crosstab Query

Aug 4, 2015

I have a very basic query with the following calculated field in it:

ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0)

I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.

But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.

I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.

View 4 Replies View Related

Queries :: Taking User Input In CrossTab Query

Jul 29, 2013

I have a cross tab query that takes input from the user. The code is as under:

PARAMETERS [START_MONTH] DateTime, [END_MONTH] DateTime;
TRANSFORM Round(Sum(([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM
SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block=PRODUCTION.FIELD_BLOCK
WHERE ((PRODUCTION.MONTH) Between Format([START_MONTH],"dd-mm-yyyy") And Format([END_MONTH],"dd-mm-yyyy")))
GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
PIVOT PRODUCTION.ACTIVITY;

I am facing 3 problems with the above code:

1. Access prompts to enter the start month and end month even when I click the save button.
2. When I run the query it prompts to enter the start month and end month twice.
3. I have generated a report based on the above query. When I run the report it keeps on prompting for start month and end month and does not give the final result.

Is it a common problem with cross tab queries?

View 5 Replies View Related







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