none
Problem with calculating total time in hours and min

    Question

  • while demonstrating my db project the supervisor noticed a major glitch in one of my reports and I can't figure out how to correct it.

    The report is suppose to show total combined hours worked for each employee listed for a specified period of time.

    I will include the SQL Querry at the end of this outline.

    In the form I use to enter criteria for my queries I selected: Start Date 04-01-11 and End Date 04-23-11. I had the table pre populated with the following information that pertains to the report.

    DateofRequest        Employee          Completed             Hrs         Min

    04-09-11                John C                  Yes                    0           55

    04-11-11                John C                  Yes                   0            10

    04-11-11               John C                   Yes                   1            35

    04-14-11               John C                   Yes                   0            30

    04-16-11              John  C                   Yes                   2          45

    4-20-11               John C                      Yes                 0            45

    4-22-11               John C                     Yes                  1           10

    I used the same name to show how the report adds up all the times together for each employee. In reality there will be as many as 7 different employees at a time shown on the report.

    After entering the start and end dates I clicked on the command button to open the report and got the following results

    Name                 total hours

    John C                   00:55:00

    John C                    01:45:00 (combined both 4/11/11 times)

    John C                  00:30:00

    John C                 02:45:00

    John C                00:45:00

    John C                01:10:00

    What the report should have showed was John C       07:55:00

    I need to know how to combine all the times for each individual employee and have each employee listed seperate on the report.

    SQL Query

    SELECT tblWorkRequest.DateofRequest, tblWorkRequest.WorkPerformedBy, tblWorkRequest.WorkCompleted, Sum(JobTimeHrs+(9JobTimeMn/60))*24 AS totHrs, TimeElapsed(Sum((JobTimeHrs/24)+(JobTimeMn/1440))) AS TotHrsFormatted

    FROM tblWorkRequest

    GROUP BY tblWorkRequest.DateofRequest, tblWorkRequest.WorkPerformedBy, tblWorkRequest.WorkCompleted

    HAVING (((tblWorkRequest.DateofRequest)>=Forms![All Wk RqQrt Rpts]!StartDate And (tblWorkRequest.DateofRequest)<DateAdd("d",1,Forms![All WkRq Qrt Rpts]!EndDate)) And ((tblWorkRequest.WorkCompelted)=Yes));

    Please advise on the solution or if you need any further information. NOTE: the TiemElapsed is a module withing the DB and I can supply that if you need it.

    Thanks

    Bill D

    Sunday, April 24, 2011 1:23 AM

Answers

  • take out the DateOfRequest in you GROUP BY statement and you may get what you want.

    SELECT Max(tblWorkRequest.DateofRequest) AS MaxOfDateofRequest, tblWorkRequest.Employee, tblWorkRequest.Completed, Sum(Hrs+(Min/60))*24 AS totHrs
    FROM tblWorkRequest
    GROUP BY tblWorkRequest.Employee, tblWorkRequest.Completed
    HAVING (((Max(tblWorkRequest.DateofRequest))>=#4/11/2011# And (Max(tblWorkRequest.DateofRequest))<DateAdd("d",1,#4/22/2011#)) AND ((tblWorkRequest.Completed)=Yes));

     

    something like this instead

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 2:58 AM
  • If you're storing the duration as minutes in the database, you could go with a field on the report whose Visible property is set to False and then use two calculated fields to display the result in hrs/minutes. For the totals, you would use the running SUM on the hidden minutes field and then use a calculated field in the group footer to convert the value to the hrs/minutes.
    David H
    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 3:20 AM
  • David,

    The code that I have calculates the time for me with no problem. The SQL and the VBA code gives me the hours and min as I need them. I also have to be able to have the hours go into the hundreds and maybe sometimes thousands so the code I have I need to keep as that works without giving me any errors.

    What I need to do is to combine the hours for each person so instead of having:

    John C    2:45

    John C    1:10

    John C      :55

    I have one line that would read:   John C   4:50 (actually it would display 04:50:00)

    When I run the report it will combine the same names and add the hours and min if the DateofRequest fields are the same. But if the DateofRequest fields are different it won't add them up.

    If you know how to get the code I supplied to add all the times for the same person together for the specified date range that is what I need.

    Thanks

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 4:04 AM
  • SuzyQ,

    I tried the code you sent me and it does not work. All I get are error messages when I run the query from DateOfRequest messages to improper use of operators with AS MaxOfDateofRequest highlighted.

    I did get the query to run one time but it and the report gave me "error" in the time control.

    I need to keep the code that I have along with the VBA that calculates elapsed time, and just need help combining the times for each person for example now I get

    John  C     2:45

    John C      1:10

    John C       :55

    And what I need is:   John C    4:50 (the display is really 04:50:00)

    If you have any idea how to do that without changing how the code does the calculation that would be great

    Thanks

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 4:09 AM
  • You could try this instead ... you will have to add your functions back in, I took them out to test.  this seems to work just fine.

     

    SELECT tblWorkRequest.Employee, tblWorkRequest.Completed, Sum(Hrs+(Min/60))*24 AS totHrs
    FROM (SELECT tblWorkRequest.DateOfRequest,  tblWorkRequest.Employee, tblWorkRequest.Completed, Hrs, min
    FROM tblWorkRequest
    WHERE tblWorkRequest.DateOfRequest Between #4/1/2011# And #4/22/2011#)
    GROUP BY tblWorkRequest.Employee, tblWorkRequest.Completed

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 4:24 AM
  • SuzyQ,

    Before I go to far with this I want to make sure that we are on the same page and that I totally understand what you are saying.

    In my table tblWorkRequest I have two fields one for job hours (jobhrs) and one for job min (jobmn). I need to have the calculation to not be in time format as with time format the hrs and min cannot go past 23 hrs 59 min. It is possible that one employee over a three month period (quarterly) could have time worked in the thousands of hours when totaled up.

    Will this code give me a result for example:

    John C during the period 1/1/11 thru 3/31/11 worked on 297 work requests and his total time spent on these is 1,126 hrs 35 min.

    Will the result show:    John C    1,126:35:00.

    The code that supplied above in my first question gave me those types of results if the date of request were all the same day or when I tested one time I entered a number in the thousands to see if the format would show that long of a result. which it did.

    Your code is much shorter and does not involve using a VBA function in it so if you get the results of displaying the employees name one time with the total of all hours worked on all projects I will go with the code you supplied and remove the code that I have in the db now.

    Thanks

    Bill D 

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 3:09 PM
  • I did not do anything to the way you formatted the time.  In fact I took your function out to test what I thought was your initial problem and that was not grouping all the John C records together into one record.  The sql I gave you will put all your John C records together on one record.  you will have to put back your formatting the way you want it.  Essentially use your sql as is except remove the date field from the list of field and instead of using "from tblworkrequest" you will use a sub query to define which records will be selected from that is where you decide which dates to pick up

    I.E.

    FROM (SELECT tblWorkRequest.DateOfRequest,  tblWorkRequest.Employee, tblWorkRequest.Completed, Hrs, min
    FROM tblWorkRequest
    WHERE tblWorkRequest.DateOfRequest Between #4/1/2011# And #4/22/2011#)

     

    substituting your form controls form my hard coded dates.

    EDITED TO ADD Complete SQL STATEMENT

    SELECT tblWorkRequest.WorkPerformedBy, tblWorkRequest.WorkCompleted, Sum(JobTimeHrs+(9JobTimeMn/60))*24 AS totHrs, TimeElapsed(Sum((JobTimeHrs/24)+(JobTimeMn/1440))) AS TotHrsFormatted

    FROM (SELECT tblWorkRequest.DateOfRequest, tblWorkRequest.WorkPerformedBy,  tblWorkRequest.WorkCompleted, JobTimeHrs, JobTimeMn
    FROM tblWorkRequest
    WHERE tblWorkRequest.DateofRequest>=Forms![All Wk RqQrt Rpts]!StartDate And tblWorkRequest.DateofRequest<DateAdd("d",1,Forms![All WkRq Qrt Rpts]!EndDate And tblWorkRequest.WorkCompelted=Yes)

    GROUP BY tblWorkRequest.WorkPerformedBy, tblWorkRequest.WorkCompleted


    • Edited by -suzyQ Sunday, April 24, 2011 5:20 PM additional information
    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 4:26 PM
  • SuzyQ,

    I entered your code in my db and noticed that you had a "(" between FROM and SELECT but no end bracket. If I put the bracket after "JobTimeMn then I get an error message "Syntax Error in FROM Clause" with the 2nd FROM highlighted.

    I moved the closing "()" to the end of FROM tblWorkRequest and then when I run the the querry I get the following this also happens when I run the report from my Reports Form:

    Enter Start Date: I enter 04-01-11

    Enter End Date: I enter 04-24-11

    Click on the command button to run the Report

    Value Box - Enter Parameter Value: tblWorkRequest.WorkPErformedBy  (I enter John C)

    Value Box - Enter Parameter Value: tblWorkRequest.WorkPerformedBy (I enter John C)

    Value Box - Enter Parameter Value: WorkPerformedBy  (I enter John C)

    Report then opens but there is no name in the Work Performed By box and there are no hours listed in the Total Hours Box

    When re-opened the query I noticed that Access added a bunch of "()" and "[]" so here is the code that I ended up with in my db Also Note in the first SELECT that the db inserted Expr1 and Expr2

    SELECT tblWorkRequest.WorkPerformedBy AS Expr1, tblWorkRequest.WorkCompleted AS Expr2, Sum(JobTimeHrs+(JobTimeMn/60))*24 AS TotHrs, TimeElapsed(Sum((JobtimeHrs/24)+(JobTimeMn/1440))) AS TotHrsFromatted

    FROM(SELECT tblWorkRequest.DateofRequest, tblWorkRequest.WorkPerfromedBy, tblWorkRequest.WorkCompleted, JobTimeHrs,JobTimeMn

    FROM tblWorkRequest)

    WHERE ((([tblWorkRequest].[DateofRequest])>=[Forms]![All Wk Rq Qrt Rpts]![StartDate] And ([tblWorkRequest].[DateofRequest])<DateAdd("d",1,([tblWorkRequest].[DateofRequest])=[Forms]![All Wk Rq Qrt Rpts]![EndDate] And [tblWorkRequest].[WorkCompleted]=Yes)))

    Please help.

    Thanks

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 9:26 PM
  • The missing ) should actually go after your dateAdd( function after [Forms]![All Wk Rq Qrt Rpts]![EndDate]

    I removed all of the extra () that were in your having statement when I changed it to a where statement in the sub query and I must have gotten carried away.  Put the ) where indicated below

     

    FROM (SELECT tblWorkRequest.DateOfRequest, tblWorkRequest.WorkPerformedBy,  tblWorkRequest.WorkCompleted, JobTimeHrs, JobTimeMn
    FROM tblWorkRequest
    WHERE tblWorkRequest.DateofRequest>=Forms![All Wk RqQrt Rpts]!StartDate And tblWorkRequest.DateofRequest<DateAdd("d",1,Forms![All WkRq Qrt Rpts]!EndDate) And tblWorkRequest.WorkCompelted=Yes)

    • Proposed as answer by -suzyQ Monday, April 25, 2011 6:14 AM
    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Sunday, April 24, 2011 10:04 PM
  • Biil,

    Make sure your module calculating date/time did not include the "Seconds" and that the input in your table is correct. If you leave the format as "General" in the table for example, it might be the reason that your module include the "Seconds". That might be the reason why you had an additional 5 more minutes in your total.

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Monday, April 25, 2011 1:32 AM
  • SuzyQ,

    I made the changes with the "()" and removed all the other stuff that Access added in and everything works great. You are the greatest.

    I wanted to ask you though and this does not affect the results at all but I would like to know for future reference:

    After I ran the report which I ran with several different date criteria I went back into the code to see if Access added anything to the SQL on its own.

    Between .....WorkCompleted=Yes) and GROUP BY Access added As[%$##@_Alias]

    Also when I went into design view int the upper window where the tables would be located there is a table window and in the heading for the table is says - Table:%$##@_Alias

    This is also true in the lower section under each of the fields for table it has the same thing.

    Do you know what that means?

    Is this a common thing that I could run across in the future?

    Again thank for all of you help and taking all the time to work with me. I am still in the learning stages of writing code especially SQL. You have been a great help to me and I really do appreciate it more than I can say. During this whole project I have been creating a notebook/cheat sheets for future use with all the code and ribbon XML that I have learned. I am up to about 40+ pages so far.

    Again thank you so very much for all you help

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Monday, April 25, 2011 4:08 AM
  • Vandal,

    Thanks for the info. I am not calculating time and the format in the table is set to number. I have a field for hours and a field for min.

    The SQL works with the VBA module to give me total hours and min combined. I had to stay away from date/time format as the hours and min will always go way past 23:59 in the date/time format so I have code that converts the number value fields for hours and min to actual hours:min.

    The code I got from SuzyQ gives me the correct output that I need so all is well. The biggest problem I was having was when one person had multiple entries it was not combining that persons time into one line on the report.

    Thanks again for the input I will make a note of that for future use.

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Monday, April 25, 2011 4:14 AM
  • Access just added an alias to the sql so that it could reference it.  you could actually add your own reference if you want it to be more readable
    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Monday, April 25, 2011 6:13 AM

All replies

  • take out the DateOfRequest in you GROUP BY statement and you may get what you want.

    SELECT Max(tblWorkRequest.DateofRequest) AS MaxOfDateofRequest, tblWorkRequest.Employee, tblWorkRequest.Completed, Sum(Hrs+(Min/60))*24 AS totHrs
    FROM tblWorkRequest
    GROUP BY tblWorkRequest.Employee, tblWorkRequest.Completed
    HAVING (((Max(tblWorkRequest.DateofRequest))>=#4/11/2011# And (Max(tblWorkRequest.DateofRequest))<DateAdd("d",1,#4/22/2011#)) AND ((tblWorkRequest.Completed)=Yes));

     

    something like this instead

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 2:58 AM
  • If you're storing the duration as minutes in the database, you could go with a field on the report whose Visible property is set to False and then use two calculated fields to display the result in hrs/minutes. For the totals, you would use the running SUM on the hidden minutes field and then use a calculated field in the group footer to convert the value to the hrs/minutes.
    David H
    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 3:20 AM
  • David,

    The code that I have calculates the time for me with no problem. The SQL and the VBA code gives me the hours and min as I need them. I also have to be able to have the hours go into the hundreds and maybe sometimes thousands so the code I have I need to keep as that works without giving me any errors.

    What I need to do is to combine the hours for each person so instead of having:

    John C    2:45

    John C    1:10

    John C      :55

    I have one line that would read:   John C   4:50 (actually it would display 04:50:00)

    When I run the report it will combine the same names and add the hours and min if the DateofRequest fields are the same. But if the DateofRequest fields are different it won't add them up.

    If you know how to get the code I supplied to add all the times for the same person together for the specified date range that is what I need.

    Thanks

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 4:04 AM
  • SuzyQ,

    I tried the code you sent me and it does not work. All I get are error messages when I run the query from DateOfRequest messages to improper use of operators with AS MaxOfDateofRequest highlighted.

    I did get the query to run one time but it and the report gave me "error" in the time control.

    I need to keep the code that I have along with the VBA that calculates elapsed time, and just need help combining the times for each person for example now I get

    John  C     2:45

    John C      1:10

    John C       :55

    And what I need is:   John C    4:50 (the display is really 04:50:00)

    If you have any idea how to do that without changing how the code does the calculation that would be great

    Thanks

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 4:09 AM
  • You could try this instead ... you will have to add your functions back in, I took them out to test.  this seems to work just fine.

     

    SELECT tblWorkRequest.Employee, tblWorkRequest.Completed, Sum(Hrs+(Min/60))*24 AS totHrs
    FROM (SELECT tblWorkRequest.DateOfRequest,  tblWorkRequest.Employee, tblWorkRequest.Completed, Hrs, min
    FROM tblWorkRequest
    WHERE tblWorkRequest.DateOfRequest Between #4/1/2011# And #4/22/2011#)
    GROUP BY tblWorkRequest.Employee, tblWorkRequest.Completed

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 4:24 AM
  • SuzyQ,

    Before I go to far with this I want to make sure that we are on the same page and that I totally understand what you are saying.

    In my table tblWorkRequest I have two fields one for job hours (jobhrs) and one for job min (jobmn). I need to have the calculation to not be in time format as with time format the hrs and min cannot go past 23 hrs 59 min. It is possible that one employee over a three month period (quarterly) could have time worked in the thousands of hours when totaled up.

    Will this code give me a result for example:

    John C during the period 1/1/11 thru 3/31/11 worked on 297 work requests and his total time spent on these is 1,126 hrs 35 min.

    Will the result show:    John C    1,126:35:00.

    The code that supplied above in my first question gave me those types of results if the date of request were all the same day or when I tested one time I entered a number in the thousands to see if the format would show that long of a result. which it did.

    Your code is much shorter and does not involve using a VBA function in it so if you get the results of displaying the employees name one time with the total of all hours worked on all projects I will go with the code you supplied and remove the code that I have in the db now.

    Thanks

    Bill D 

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 3:09 PM
  • I did not do anything to the way you formatted the time.  In fact I took your function out to test what I thought was your initial problem and that was not grouping all the John C records together into one record.  The sql I gave you will put all your John C records together on one record.  you will have to put back your formatting the way you want it.  Essentially use your sql as is except remove the date field from the list of field and instead of using "from tblworkrequest" you will use a sub query to define which records will be selected from that is where you decide which dates to pick up

    I.E.

    FROM (SELECT tblWorkRequest.DateOfRequest,  tblWorkRequest.Employee, tblWorkRequest.Completed, Hrs, min
    FROM tblWorkRequest
    WHERE tblWorkRequest.DateOfRequest Between #4/1/2011# And #4/22/2011#)

     

    substituting your form controls form my hard coded dates.

    EDITED TO ADD Complete SQL STATEMENT

    SELECT tblWorkRequest.WorkPerformedBy, tblWorkRequest.WorkCompleted, Sum(JobTimeHrs+(9JobTimeMn/60))*24 AS totHrs, TimeElapsed(Sum((JobTimeHrs/24)+(JobTimeMn/1440))) AS TotHrsFormatted

    FROM (SELECT tblWorkRequest.DateOfRequest, tblWorkRequest.WorkPerformedBy,  tblWorkRequest.WorkCompleted, JobTimeHrs, JobTimeMn
    FROM tblWorkRequest
    WHERE tblWorkRequest.DateofRequest>=Forms![All Wk RqQrt Rpts]!StartDate And tblWorkRequest.DateofRequest<DateAdd("d",1,Forms![All WkRq Qrt Rpts]!EndDate And tblWorkRequest.WorkCompelted=Yes)

    GROUP BY tblWorkRequest.WorkPerformedBy, tblWorkRequest.WorkCompleted


    • Edited by -suzyQ Sunday, April 24, 2011 5:20 PM additional information
    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 4:26 PM
  • SuzyQ,

    I entered your code in my db and noticed that you had a "(" between FROM and SELECT but no end bracket. If I put the bracket after "JobTimeMn then I get an error message "Syntax Error in FROM Clause" with the 2nd FROM highlighted.

    I moved the closing "()" to the end of FROM tblWorkRequest and then when I run the the querry I get the following this also happens when I run the report from my Reports Form:

    Enter Start Date: I enter 04-01-11

    Enter End Date: I enter 04-24-11

    Click on the command button to run the Report

    Value Box - Enter Parameter Value: tblWorkRequest.WorkPErformedBy  (I enter John C)

    Value Box - Enter Parameter Value: tblWorkRequest.WorkPerformedBy (I enter John C)

    Value Box - Enter Parameter Value: WorkPerformedBy  (I enter John C)

    Report then opens but there is no name in the Work Performed By box and there are no hours listed in the Total Hours Box

    When re-opened the query I noticed that Access added a bunch of "()" and "[]" so here is the code that I ended up with in my db Also Note in the first SELECT that the db inserted Expr1 and Expr2

    SELECT tblWorkRequest.WorkPerformedBy AS Expr1, tblWorkRequest.WorkCompleted AS Expr2, Sum(JobTimeHrs+(JobTimeMn/60))*24 AS TotHrs, TimeElapsed(Sum((JobtimeHrs/24)+(JobTimeMn/1440))) AS TotHrsFromatted

    FROM(SELECT tblWorkRequest.DateofRequest, tblWorkRequest.WorkPerfromedBy, tblWorkRequest.WorkCompleted, JobTimeHrs,JobTimeMn

    FROM tblWorkRequest)

    WHERE ((([tblWorkRequest].[DateofRequest])>=[Forms]![All Wk Rq Qrt Rpts]![StartDate] And ([tblWorkRequest].[DateofRequest])<DateAdd("d",1,([tblWorkRequest].[DateofRequest])=[Forms]![All Wk Rq Qrt Rpts]![EndDate] And [tblWorkRequest].[WorkCompleted]=Yes)))

    Please help.

    Thanks

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:54 PM
    Sunday, April 24, 2011 9:26 PM
  • The missing ) should actually go after your dateAdd( function after [Forms]![All Wk Rq Qrt Rpts]![EndDate]

    I removed all of the extra () that were in your having statement when I changed it to a where statement in the sub query and I must have gotten carried away.  Put the ) where indicated below

     

    FROM (SELECT tblWorkRequest.DateOfRequest, tblWorkRequest.WorkPerformedBy,  tblWorkRequest.WorkCompleted, JobTimeHrs, JobTimeMn
    FROM tblWorkRequest
    WHERE tblWorkRequest.DateofRequest>=Forms![All Wk RqQrt Rpts]!StartDate And tblWorkRequest.DateofRequest<DateAdd("d",1,Forms![All WkRq Qrt Rpts]!EndDate) And tblWorkRequest.WorkCompelted=Yes)

    • Proposed as answer by -suzyQ Monday, April 25, 2011 6:14 AM
    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Sunday, April 24, 2011 10:04 PM
  • Biil,

    Make sure your module calculating date/time did not include the "Seconds" and that the input in your table is correct. If you leave the format as "General" in the table for example, it might be the reason that your module include the "Seconds". That might be the reason why you had an additional 5 more minutes in your total.

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Monday, April 25, 2011 1:32 AM
  • SuzyQ,

    I made the changes with the "()" and removed all the other stuff that Access added in and everything works great. You are the greatest.

    I wanted to ask you though and this does not affect the results at all but I would like to know for future reference:

    After I ran the report which I ran with several different date criteria I went back into the code to see if Access added anything to the SQL on its own.

    Between .....WorkCompleted=Yes) and GROUP BY Access added As[%$##@_Alias]

    Also when I went into design view int the upper window where the tables would be located there is a table window and in the heading for the table is says - Table:%$##@_Alias

    This is also true in the lower section under each of the fields for table it has the same thing.

    Do you know what that means?

    Is this a common thing that I could run across in the future?

    Again thank for all of you help and taking all the time to work with me. I am still in the learning stages of writing code especially SQL. You have been a great help to me and I really do appreciate it more than I can say. During this whole project I have been creating a notebook/cheat sheets for future use with all the code and ribbon XML that I have learned. I am up to about 40+ pages so far.

    Again thank you so very much for all you help

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Monday, April 25, 2011 4:08 AM
  • Vandal,

    Thanks for the info. I am not calculating time and the format in the table is set to number. I have a field for hours and a field for min.

    The SQL works with the VBA module to give me total hours and min combined. I had to stay away from date/time format as the hours and min will always go way past 23:59 in the date/time format so I have code that converts the number value fields for hours and min to actual hours:min.

    The code I got from SuzyQ gives me the correct output that I need so all is well. The biggest problem I was having was when one person had multiple entries it was not combining that persons time into one line on the report.

    Thanks again for the input I will make a note of that for future use.

    Bill D

    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Monday, April 25, 2011 4:14 AM
  • Access just added an alias to the sql so that it could reference it.  you could actually add your own reference if you want it to be more readable
    • Marked as answer by billd5132 Tuesday, April 26, 2011 2:55 PM
    Monday, April 25, 2011 6:13 AM