Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94

    Displaying Time sheet data in Horizontally

    Gents,

    I need help on this type of format I have tried and spent most of my time in this format,

    I have separate table for employees(tblEmployeeDetails) and Employee Work Hours (tblWorkHours)

    I have created the attached format in a form.... I need help on, when I choose the month in the form(colored in blue) the below boxes need to get the Straight Time from the tblworkhours table for the particular date




    Click image for larger version. 

Name:	Timesheet format.JPG 
Views:	50 
Size:	67.9 KB 
ID:	27421

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    A crosstab query will give you results horizontally. Or you can populate the fields using VBA.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use a crosstab query as the recordsource to the form (I presume it is a continuous form)

    the field that designates ST or OT would be the row header, you would need a calculation to determine the column header - something like "D & format([WorkDate],'dd') and then the value would be the relevant field. Also Ensure you set the column headers - D01, D02, D03 up to D31

    On the form, your control sources for the ST row would be D01, D02 etc, whilst the headings 01, 02 etc could be a label, and Sun/Mon etc would be a calculation based on your month entry field - something like =format(dateadd("d",1,me.monthdate,"ddd")

  4. #4
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    This my be helpful:

    Schedule Grid - DesGrid


    The company that originally deployed this product no longer supports it. The product has since given it to public domain soley as an "as is" device. Their information has been stripped out of the attachments and manual. This tool is what it is and is considered "skilled" to "advanced" for level of developer skills to manipulate successfully.

  5. #5
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    Gents,

    Thanks a lot for responding, I have created a crosstab as you said but its not getting the ST or OT data's but it is getting total ST & OT hours

    herewith I have attached crosstab SQL Code and Screenshot

    Cross Tab SQL Code for Regular Hours(ST):

    TRANSFORM Sum(QryEmployeeTimeCard.[Regular Hrs]) AS [SumOfRegular Hrs]
    SELECT QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeName, QryEmployeeTimeCard.JobTitle, QryEmployeeTimeCard.Company, Sum(QryEmployeeTimeCard.[Regular Hrs]) AS [Total Of Regular Hrs]
    FROM QryEmployeeTimeCard
    GROUP BY QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeName, QryEmployeeTimeCard.JobTitle, QryEmployeeTimeCard.Company
    ORDER BY Format("D & format([WorkDate],'dd'")
    PIVOT Format("D & format([WorkDate],'dd'") In ("D01","D02","D03","D04","D05","D06","D07","D08"," D09","D10","D11","D12","D13","D14","D15","D16","D1 7","D18","D19","D20","D21","D22","D23","D24","D25" ,"D26","D27","D28","D29","D30","D31");

    Click image for larger version. 

Name:	form design mode.jpg 
Views:	42 
Size:	270.4 KB 
ID:	27432

    Click image for larger version. 

Name:	blank.JPG 
Views:	42 
Size:	58.3 KB 
ID:	27434

    Click image for larger version. 

Name:	Date Format.JPG 
Views:	42 
Size:	134.1 KB 
ID:	27433

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you also need a row heading for the field which defines ST and OT.

    Would also replace your ST an OT labels on the form with textboxes to use this field. Plus looks like you also need a WHERE criteria to limit the records returned to month requested

    TRANSFORM Sum(QryEmployeeTimeCard.[Regular Hrs]) AS [SumOfRegular Hrs]
    SELECT QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeName, QryEmployeeTimeCard.JobTitle, QryEmployeeTimeCard.Company, QryEmployeeTimeCard.TimeType, Sum(QryEmployeeTimeCard.[Regular Hrs]) AS [Total Of Regular Hrs]
    FROM QryEmployeeTimeCard
    WHERE month(Workdate)=month(txtMonth) AND year(workdate)=year(txtMonth)
    GROUP BY QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeName, QryEmployeeTimeCard.JobTitle, QryEmployeeTimeCard.Company, QryEmployeeTimeCard.TimeType
    ORDER BY Format("D & format([WorkDate],'dd'")
    PIVOT Format("D & format([WorkDate],'dd'") In ("D01","D02","D03","D04","D05","D06","D07","D08 "," D09","D10","D11","D12","D13","D14","D15","D16","D 1 7","D18","D19","D20","D21","D22","D23","D24","D 25" ,"D26","D27","D28","D29","D30","D31");


    Change TimeType to the name of the relevant field

  7. #7
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    I tried it is giving an Syntax error.

    TRANSFORM Sum(QryEmployeeTimeCard.[Regular Hrs]) AS [SumOfRegular Hrs]
    SELECT QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeName, QryEmployeeTimeCard.JobTitle, QryEmployeeTimeCard.Company, QryEmployeeTimeCard.Regular Hrs, Sum(QryEmployeeTimeCard.[Regular Hrs]) AS [Total Of Regular Hrs]
    FROM QryEmployeeTimeCard
    WHERE (((Month([Workdate]))=Month([txtMonth])) AND ((Year([workdate]))=Year([txtMonth])))
    GROUP BY QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeName, QryEmployeeTimeCard.JobTitle, QryEmployeeTimeCard.Company, QryEmployeeTimeCard.Regular Hrs
    ORDER BY Format("D & format([WorkDate],'dd'")
    PIVOT Format("D & format([WorkDate],'dd'") In ("D01","D02","D03","D04","D05","D06","D07","D08"," D09","D10","D11","D12","D13","D14","D15","D16","D1 7","D18","D19","D20","D21","D22","D23","D24","D25" ,"D26","D27","D28","D29","D30","D31");

    Click image for larger version. 

Name:	error.JPG 
Views:	40 
Size:	71.7 KB 
ID:	27436

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you have spaces in your names so you need to use square brackets.

    But, why are you grouping on regular hours when you are summing it?

    Think you need to provide the source to your QryEmployeeTimeCard as I have made certain assumptions

    And just to check, a) is the control on your form really called txtMonth? and b) I provided this on the basis you would reference it properly depending on where your query is running from. If this is an external query then it would be something like forms!TrackingForm!txtMonth

  9. #9
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    yes in the form the month textbox is named as txtMonth, and it is in external Query,

    when I used the below code I am getting another error


    TRANSFORM Sum(QryEmployeeTimeCard.[Regular Hrs]) AS [SumOfRegular Hrs]
    SELECT QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeName, QryEmployeeTimeCard.JobTitle, QryEmployeeTimeCard.Company, QryEmployeeTimeCard.[Regular Hrs], Sum(QryEmployeeTimeCard.[Regular Hrs]) AS [Total Of Regular Hrs]
    FROM QryEmployeeTimeCard
    WHERE (((Month([Workdate]))=Month(Forms!frmTimesheetTesting!txtMonth)) And ((Year([workdate]))=Year(Forms!frmTimesheetTesting!txtMonth)))
    GROUP BY QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeID, QryEmployeeTimeCard.EmployeeName, QryEmployeeTimeCard.JobTitle, QryEmployeeTimeCard.Company
    ORDER BY Format("D & format([WorkDate],'dd'")
    PIVOT Format("D & format([WorkDate],'dd'") In ("D01","D02","D03","D04","D05","D06","D07","D08"," D09","D10","D11","D12","D13","D14","D15","D16","D1 7","D18","D19","D20","D21","D22","D23","D24","D25" ,"D26","D27","D28","D29","D30","D31");

    Click image for larger version. 

Name:	error 1.JPG 
Views:	38 
Size:	67.4 KB 
ID:	27437

    Click image for larger version. 

Name:	Query.JPG 
Views:	39 
Size:	132.7 KB 
ID:	27438

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    as explained before - you are trying to group by a value you are summing which to me seems wrong.

    However the error is due to you not including it in the group by part of the query

    I will be very surprised if this gives you what you want - you should have a single crosstab query, not one for each of regular hours and overtime hours

    your qrytimecard should look something like

    EmployeeID..workdate..timetype...regularhours
    1.................1/1/2017..ST............10
    1.................1/1/2017..OT............2

  11. #11
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    yes you are right I should add QryEmployeeTimeCard.[Regular Hrs], in grouping.

    But,

    "your qrytimecard should look something like

    EmployeeID..workdate..timetype...regularhours
    1.................1/1/2017..ST............10
    1.................1/1/2017..OT............2"

    I don't have Time type in my table or in any form, I just add a label to show 1st row is ST and 2nd Row is OT. just for identification.
    if you see the above query relationship QryEmployeeTimecard is a simple Query which is collecting the data's from the table.
    So what I did now I deleted the qryOTHrs_crosstab instead of qryRegularHrs_crosstab I added qryTotalHrs_crosstab(which gives 10hrs (reghrs-8 & OThrs-2))
    so when I choose the month(dd-mm-yy) it has to display the data's for the employee 1 from jan 01 to jan 31.

  12. #12
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    yes you are right I should not add QryEmployeeTimeCard.[Regular Hrs], in grouping.

    But,

    "your qrytimecard should look something like

    EmployeeID..workdate..timetype...regularhours
    1.................1/1/2017..ST............10
    1.................1/1/2017..OT............2"

    I don't have Time type in my table or in any form, I just add a label to show 1st row is ST and 2nd Row is OT. just for identification.
    if you see the above query relationship QryEmployeeTimecard is a simple Query which is collecting the data's from the table.

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So how, in your two crosstabs are you identifying those hours that are overtime and those that are regular?

  14. #14
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    if the total hrs are displaying correctly on each particular boxes then I can use the formula to subtract from the total hours. to display 8 and 2

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    We are clearly not communicating, so I don't think I can help any more.

    final question - what is the formula?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 06-03-2015, 10:45 AM
  2. Replies: 1
    Last Post: 12-04-2014, 10:33 AM
  3. Help Displaying Data Horizontally In Report
    By bgwool in forum Reports
    Replies: 10
    Last Post: 09-04-2014, 03:32 PM
  4. Displaying multiple records horizontally
    By Juicejam in forum Forms
    Replies: 14
    Last Post: 01-19-2012, 03:05 AM
  5. Time sheet setup help
    By xAkademiks in forum Access
    Replies: 1
    Last Post: 10-13-2010, 10:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums