Need help with query summing hours

Tring to get total worked hours and total maintenance hours summary.
I have a table with the clock hours
WorkID
WorkDate
WorkEmployeeID
WorkHours
And have a maintenace table
MaintID
MaintDate
MaintEmployeeID
MaintHours
Needing help combining the information to get summary something like this..

Date   - Employee    - Clock Hours - Maintenance Hours 
1/1/10  Joe Billy Bob         8                      6
1/1/10  Billy Joe Bob         8                      7

Whay type of query do I need to build, any help would be great.

Thanks

-- 
Message posted via http://www.accessmonster.com

0
Russ
2/23/2010 8:40:37 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
896 Views

Similar Articles

[PageSpeed] 28

Russ,
use a Crosstab query. The wizard should walk you through most/all of it.

-- 
Message posted via http://www.accessmonster.com

0
PieterLinden
2/23/2010 9:22:29 PM
Not sure that will do it.
How would I sum all maintenance hours for the day and sum hours worked for
the day and math only that day providing a summary list?

example
From the maintenance table...(recordID, date, employee, time)
on 1/1/09 Jim had a total of 5 hrs from from maintenance records he worked on
that day 2hrs from record 1 and 3hrs  from record 2. 

From the Time table.....(recordID, date, employee, time)
on 1/1/09 Jim had a total of 8 hrs from from his clock in/out times he worked
from 8am to noon giving him 4 hrs then came back and worked from 6pm-10pm
give him a total of 8 hours for that day. 

Trying to get summary of the days work hours and actual time

1/1/09 / Jim / 5hrs Maint / 8hrs actual time

I am just not getting the link, is it somehow in the date?

Thanks in advance for any help.


PieterLinden wrote:
>Russ,
>use a Crosstab query. The wizard should walk you through most/all of it.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

0
Russ
2/24/2010 12:44:55 AM
Use a union query to make an employe/date query and then left join to the 
other tables.
   qryWorkEmpMaint --
SELECT WorkDate, WorkEmployeeID
FROM tblWorkHours
UNION SELECT MaintDate, MaintEmployeeID
FROM tblMaintenace; 

SELECT WorkDate, WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock 
Hours], Sum(MaintHours) AS [Maintenance Hours] 
FROM  (qryWorkEmpMaint LEFT JOIN tblWorkHours ON qryWorkEmpMaint.WorkDate = 
tblWorkHours.WorkDate AND    qryWorkEmpMaint.WorkEmployeeID = 
tblWorkHours.WorkEmployeeID) LEFT JOIN tblMaintenace ON 
tblMaintenace.MaintDate = qryWorkEmpMaint.WorkDate AND 
tblMaintenace.MaintEmployeeID = qryWorkEmpMaint.WorkEmployeeID;  




-- 
Build a little, test a little.


"Russ via AccessMonster.com" wrote:

> Tring to get total worked hours and total maintenance hours summary.
> I have a table with the clock hours
> WorkID
> WorkDate
> WorkEmployeeID
> WorkHours
> And have a maintenace table
> MaintID
> MaintDate
> MaintEmployeeID
> MaintHours
> Needing help combining the information to get summary something like this..
> 
> Date   - Employee    - Clock Hours - Maintenance Hours 
> 1/1/10  Joe Billy Bob         8                      6
> 1/1/10  Billy Joe Bob         8                      7
> 
> Whay type of query do I need to build, any help would be great.
> 
> Thanks
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
2/24/2010 4:49:01 AM
Karl,
Thanks for the help. I seem to be getting closer.
But currently getting error when trying to run final query.

You tried to execute a query that does not include the specified expression
"worked date" as part of the aggregate function

qryWorkEmpMaint...
SELECT WorkedDate, WorkEmployeeID
FROM QryEmployeeWorkedHours
UNION SELECT EmpMaintDate, MaintEmployeeID
FROM QryMaintenanceHrs;

SELECT qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID AS Employee,
Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance Hours]
FROM (qryWorkEmpMaint LEFT JOIN QryEmployeeWorkedHours ON (qryWorkEmpMaint.
WorkedDate = QryEmployeeWorkedHours.[WorkedDate]) AND (qryWorkEmpMaint.
WorkEmployeeID = QryEmployeeWorkedHours.[WorkEmployeeID])) LEFT JOIN
QryMaintenanceHrs ON (qryWorkEmpMaint.WorkedDate = QryMaintenanceHrs.
EmpMaintDate) AND (qryWorkEmpMaint.WorkEmployeeID = QryMaintenanceHrs.
MaintEmployeeID);


KARL DEWEY wrote:
>Use a union query to make an employe/date query and then left join to the 
>other tables.
>   qryWorkEmpMaint --
>SELECT WorkDate, WorkEmployeeID
>FROM tblWorkHours
>UNION SELECT MaintDate, MaintEmployeeID
>FROM tblMaintenace; 
>
>SELECT WorkDate, WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock 
>Hours], Sum(MaintHours) AS [Maintenance Hours] 
>FROM  (qryWorkEmpMaint LEFT JOIN tblWorkHours ON qryWorkEmpMaint.WorkDate = 
>tblWorkHours.WorkDate AND    qryWorkEmpMaint.WorkEmployeeID = 
>tblWorkHours.WorkEmployeeID) LEFT JOIN tblMaintenace ON 
>tblMaintenace.MaintDate = qryWorkEmpMaint.WorkDate AND 
>tblMaintenace.MaintEmployeeID = qryWorkEmpMaint.WorkEmployeeID;  
>
>> Tring to get total worked hours and total maintenance hours summary.
>> I have a table with the clock hours
>[quoted text clipped - 16 lines]
>> 
>> Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

0
Russ
2/24/2010 1:04:19 PM
Left off the group by --
SELECT qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID AS 
Employee, Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance 
Hours]
FROM (qryWorkEmpMaint LEFT JOIN QryEmployeeWorkedHours ON 
(qryWorkEmpMaint.WorkedDate = QryEmployeeWorkedHours.[WorkedDate]) AND 
(qryWorkEmpMaint.WorkEmployeeID = QryEmployeeWorkedHours.[WorkEmployeeID])) 
LEFT JOIN QryMaintenanceHrs ON(qryWorkEmpMaint.WorkedDate = 
QryMaintenanceHrs.EmpMaintDate) AND (qryWorkEmpMaint.WorkEmployeeID = 
QryMaintenanceHrs.MaintEmployeeID)
GROUP BY qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID;

-- 
Build a little, test a little.


"Russ via AccessMonster.com" wrote:

> Karl,
> Thanks for the help. I seem to be getting closer.
> But currently getting error when trying to run final query.
> 
> You tried to execute a query that does not include the specified expression
> "worked date" as part of the aggregate function
> 
> qryWorkEmpMaint...
> SELECT WorkedDate, WorkEmployeeID
> FROM QryEmployeeWorkedHours
> UNION SELECT EmpMaintDate, MaintEmployeeID
> FROM QryMaintenanceHrs;
> 
> SELECT qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID AS Employee,
> Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance Hours]
> FROM (qryWorkEmpMaint LEFT JOIN QryEmployeeWorkedHours ON (qryWorkEmpMaint.
> WorkedDate = QryEmployeeWorkedHours.[WorkedDate]) AND (qryWorkEmpMaint.
> WorkEmployeeID = QryEmployeeWorkedHours.[WorkEmployeeID])) LEFT JOIN
> QryMaintenanceHrs ON (qryWorkEmpMaint.WorkedDate = QryMaintenanceHrs.
> EmpMaintDate) AND (qryWorkEmpMaint.WorkEmployeeID = QryMaintenanceHrs.
> MaintEmployeeID);
> 
> 
> KARL DEWEY wrote:
> >Use a union query to make an employe/date query and then left join to the 
> >other tables.
> >   qryWorkEmpMaint --
> >SELECT WorkDate, WorkEmployeeID
> >FROM tblWorkHours
> >UNION SELECT MaintDate, MaintEmployeeID
> >FROM tblMaintenace; 
> >
> >SELECT WorkDate, WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock 
> >Hours], Sum(MaintHours) AS [Maintenance Hours] 
> >FROM  (qryWorkEmpMaint LEFT JOIN tblWorkHours ON qryWorkEmpMaint.WorkDate = 
> >tblWorkHours.WorkDate AND    qryWorkEmpMaint.WorkEmployeeID = 
> >tblWorkHours.WorkEmployeeID) LEFT JOIN tblMaintenace ON 
> >tblMaintenace.MaintDate = qryWorkEmpMaint.WorkDate AND 
> >tblMaintenace.MaintEmployeeID = qryWorkEmpMaint.WorkEmployeeID;  
> >
> >> Tring to get total worked hours and total maintenance hours summary.
> >> I have a table with the clock hours
> >[quoted text clipped - 16 lines]
> >> 
> >> Thanks
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1
> 
> .
> 
0
Utf
2/24/2010 3:45:01 PM
Karl,
You pointed me in the direction I needed, I now have it working. Thank for
taking the time to help!
Russ

KARL DEWEY wrote:
>Left off the group by --
>SELECT qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID AS 
>Employee, Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance 
>Hours]
>FROM (qryWorkEmpMaint LEFT JOIN QryEmployeeWorkedHours ON 
>(qryWorkEmpMaint.WorkedDate = QryEmployeeWorkedHours.[WorkedDate]) AND 
>(qryWorkEmpMaint.WorkEmployeeID = QryEmployeeWorkedHours.[WorkEmployeeID])) 
>LEFT JOIN QryMaintenanceHrs ON(qryWorkEmpMaint.WorkedDate = 
>QryMaintenanceHrs.EmpMaintDate) AND (qryWorkEmpMaint.WorkEmployeeID = 
>QryMaintenanceHrs.MaintEmployeeID)
>GROUP BY qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID;
>
>> Karl,
>> Thanks for the help. I seem to be getting closer.
>[quoted text clipped - 39 lines]
>> >> 
>> >> Thanks

-- 
Message posted via http://www.accessmonster.com

0
Russ
2/24/2010 7:15:19 PM
Reply:

Similar Artilces:

Excel 97 VBA Help File
In the MS Excel Visual Basic Reference help file contents page, I click on Functions and it only offers me functions beginning with the letter S. So, I have a list of Solver and SQL functions. But what about all the other functions in VBA, for example for doing arithmetic and manipulating dates and strings? Why don't they show up? Are they left out because those functions are all part of Visual Basic generally, and the Excel VBA help file is specific to the _extra_ functions in Excel VBA? It's the only explanation I can think of. Am I right, or have I got a corrupted help file (vbaxl...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

Help, I cannot Save!
I created a document and locked the worksheet to protect the formulars before creating a template for the document. But now when I open th document and insert a new sheet using the template I created, th document will refuse to save. Once I click on save, office assistant will say "doc not saved". Wha could I have done wrong? PLease help. computerfinema -- computerfinema ----------------------------------------------------------------------- computerfineman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3716 View this thread: http://www.excelforum.c...

Help Required
Hi, Whenever I open Outlook 2003, I am getting a dialog box which displays the following message: Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience When I click Debug it displays a message box with the following error message "The instruction at "0x3007e993" referenced memory at "0x0000000:. The memory could not be read" When I click No it Visual Studio JIT debugger pops up. I uninstalled and installed several times but still the problem persists. Is there any regsitry entry that I've to modify/delete? ...

HELP Recovering addresses and email from Outlook 2003
I had some serious driver issues that required re-installing XP from disc. I did use the backup option and have a backup of all the old data. And of course had to reinstall Office 2003. Will third party software restore my old email and addresses or am I out of luck?? Thanks for the help texraid wrote: > I had some serious driver issues that required re-installing XP from > disc. I did use the backup option and have a backup of all the old > data. And of course had to reinstall Office 2003. > > Will third party software restore my old email and addresses or am I > out of lu...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Help with Registration
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I've tried to register my copy of Office for Mac through the Mactopia page. I log in successfully, but then it just keeps on loading and doesn't refresh or change. Any advice? On 6/16/09 6:35 PM, in article 59b76b64.-1@webcrossing.caR9absDaxw, "theconfuzed1@officeformac.com" <theconfuzed1@officeformac.com> wrote: > I've tried to register my copy of Office for Mac through the Mactopia page. I > log in successfully, but then it just keeps on loading and doesn't refresh or > ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

HELP! Outlook POP3 problem(s)
Hello. I am so lost. I have a few e-mail accounts set up on my computer which retrieves my mail from a couple of different providers and deposits the mail into my Outlook Inbox. Up until yesterday, my mail always has worked fine. For some strange reason, my Outlook is now (Again) retrieving my messages from all of my accounts I had set up, which are all duplicates of my messages. There is now nearly 4,000 duplicate messages in my folders. I can't seem to stop the download of these already retrieved messages. To top things off, a couple of my email account login windows keep p...

Spam Filtering HELP
I recently started a new job, and discovered after day one, that I had inharited a spam mess. Now the previous admin ad installed a Symantic Spam Server Prox which in my opinion, was a complete waste of money as it does not allow for blocking IP addresses. Now here is the question; I am running Exchange 2003, and am looking at setting up the Conection Filter under Message Delivery to block messages based on IP address. The problem is that when I save the IPs to be blocked, I get a message stating that the Connection filter "has to be enabled manually through the specific SMTP virtual serv...

Need macro help to close excel
I have created a button in Access2000 that opens an Excel Spreadsheet. What I need now is assit in closing excel upon completion. I can get an excel macro to save my file and close the worksheet, but it is not closing excel entirely. I'm on project with this employer and could use a response today to fix this before I leave. Thanks much to any and all. My macro is as follows: Sub SaveClose() ' ' SaveClose Macro ' Macro recorded 9/27/2004 by cdjohnso ' ' Keyboard Shortcut: Ctrl+Shift+C ' ChDir "I:\SchoolsSurvey\Graphs_Reports" ActiveWorkb...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

If Then Help!!!!
Hi! I'm stuck. I have a working macro but it needs a small tweek. The macro executes a find statement and performs calculations from the find to the end of the column. The problem is when nothing is found. I need an if statement or suggestion on how to tell it to skip the calculations if there is nothing found. This is what I have so far(with no if's): Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Selection.End(xlToRi...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

Sum a group of numbers
When I first started using Excel, if I had a bunch of different totals on a worksheet, I could hold down the right mouse button and cover the group I wanted to add. The sum of these numbers would appear on the bottom of either the worksheet or a task bar. I have lost the ability to do this. Or I have lost the task bar. How do I get it back? Hi Barbie, That would be the status bar. If it is not showing, go to View>Status Bar to turn it on. If the sum function is still not showing, right click anywhere on the status bar and select which option you want. HTH Martin "Barbie...

Odd e-mail duplication problem
All e-mails promoted or Tracked in CRM Outlook client are being copied to one particular Account that is unrelated. The e-mails are copied correctly to the history of the desired accounts and Contacts but also copy to one particular Accountl as well. As anyone seen anything like this and have any ideas what could be wrong? -- Darryl - dh@mtccrm.com -www.mtccrm.com - Only Microsoft CRM ...

Integration Transactions Pls help
Can anyone let me know how much time does the integration manager takes to upload a 10,000 lines(SOP) into GP There are two methods of importing SOP transactions. One uses the SQL optimized adapter and one does not. The method used depends upon the data you would need to map. There are some fields and business logic not available with the SQL adapter. But, assuming you are using the SQL adapter, you should be able to import 10,000 transactions in about 60 minutes or less. If you are using the other one, start it and go home for the night. It will take hours. If you have some reso...

Help! charts disappearing, new charts crashing powerpoint
When i open a presentation the charts vanish...i have restarted, they come back. I try to edit a chart, and it crashes powerpoint after a few clicks. What can I do...whole afternoon going crazy! If you are using v 2007 there is a hotfix which is meant to cure some chart crashing problems http://support.microsoft.com/default.aspx?scid=kb;en-us;976479&sd=rss&spid=11264 -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "UKExcelgeek" wrote: > When i open a pres...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

Sum items in a lookup table.
I work for a bus company that has 240 different routes. I also have a table in Excel that lists monthly ridership for each route. I use it as a lookup table to get data for another report. The problem? There are 6 of the 240 routes that are served by more than one garage so they appear twice in the lookup table and I would like to get the sum of the ridership for that particular route from the table. Is this possible and if so how do I do it? I gather you're using a lookup function now to find the ridership from a given route. VLOOKUP will return (0 or) 1 value. If you use SU...