Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 37
  1. #16
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    bro, I'm really sorry for tempting you,I am struck in this I really don't know how to proceed further, my database already have 49500 records as the column is more it is not displaying by date wise.

    what I said is an assumption....



    Option 1: if I can get the Reghrs and OT hrs directly from the query then my problem solved
    Option 2: even if I get the total hrs then I taught of using " Reg Hrs: ([Total Hrs]-2)" just a taught
    looking for possibilities

    Click image for larger version. 

Name:	query 1.JPG 
Views:	17 
Size:	128.0 KB 
ID:	27448

  2. #17
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    we'll try a different way - what is the sql to your qryemployeetimecard?

  3. #18
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    QryEmployeeTimeCard SQL Code:

    SELECT QryEmployeeDetails.EmployeeName, QryEmployeeDetails.Company, QryEmployeeDetails.JobTitle, QryEmployeeDetails.IDCardNO, QryEmployeeDetails.Group, QryWorkHours.[FC Reps], QryWorkHours.[Worked Date], QryWorkHours.WO, QryWorkHours.PirMocNo, QryWorkHours.[Regular Hrs], QryWorkHours.[OT Hrs], QryWorkHours.[Active Status], QryWorkHours.[TOTAL HRS], QryWorkHours.Remarks, QryEmployeeDetails.EmployeeID, QryEmployeeDetails.RegHrsRate, QryEmployeeDetails.OTHrsRate, QryWorkHours.[Total Reg Hrs Rate], QryWorkHours.[Total OT Hrs Rate], QryWorkHours.[Total Cost], QryWorkHours.WorkedYear, QryEmployeeDetails.[Contract Type]
    FROM QryEmployeeDetails LEFT JOIN QryWorkHours ON QryEmployeeDetails.EmployeeID = QryWorkHours.EmployeeID
    WHERE (((QryWorkHours.[TOTAL HRS])<>0));

    Click image for larger version. 

Name:	emp.JPG 
Views:	17 
Size:	297.2 KB 
ID:	27453

  4. #19
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK, this uses another query, qryWorkHours - please can you supply the sql to that one - and if it uses more queries, please provide the sql to those as well

  5. #20
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    QryEmployeeTimeCard has two Queries
    1.QryEmployeeDetails
    2.QryWorkHours
    Click image for larger version. 

Name:	QryEmployeeTimeCard.JPG 
Views:	16 
Size:	36.2 KB 
ID:	27456

    1.QryEmployeeDetails SQL Code:

    SELECT tblEmployeeDetails.EmployeeID, tblEmployeeDetails.EmployeeName, tblEmployeeDetails.Company, tblEmployeeDetails.JobTitle, tblEmployeeDetails.IDCardNO, tblEmployeeDetails.[E-mail Address], tblEmployeeDetails.Group, tblEmployeeDetails.[Business Phone], tblEmployeeDetails.[Home Phone], tblEmployeeDetails.[Mobile Phone], tblEmployeeDetails.[Bravo No], tblEmployeeDetails.Address, tblEmployeeDetails.City, tblEmployeeDetails.[State/Province], tblEmployeeDetails.[ZIP/Postal Code], tblEmployeeDetails.[Country/Region], tblEmployeeDetails.Notes, tblEmployeeDetails.Attachments, tblEmployeeDetails.RegHrsRate, tblEmployeeDetails.OTHrsRate, tblEmployeeDetails.Status, tblEmployeeDetails.[Contract Type], tblEmployeeDetails.Status, tblEmployeeDetails.Attachments
    FROM tblEmployeeDetails;
    Click image for larger version. 

Name:	QryEmployeeDetails.JPG 
Views:	16 
Size:	28.1 KB 
ID:	27457
    2.QryWorkHours SQL Code:

    SELECT tblWorkHours.WorkID, tblWorkHours.EmployeeID, tblWorkHours.[FC Reps], tblWorkHours.[Worked Date], tblWorkHours.WO, tblWorkHours.PirMocNo, tblWorkHours.[Regular Hrs], tblWorkHours.[OT Hrs], tblWorkHours.[Active Status], tblWorkHours.OTHrsRate, tblWorkHours.RegHrsRate, tblWorkHours.[TOTAL HRS], tblWorkHours.[Total Reg Hrs Rate], tblWorkHours.[Total OT Hrs Rate], tblWorkHours.[Total Cost], tblWorkHours.LogInID, tblWorkHours.Remarks, tblWorkHours.WorkedYear, tblEmployeeDetails.Company, tblEmployeeDetails.JobTitle
    FROM tblEmployeeDetails LEFT JOIN tblWorkHours ON tblEmployeeDetails.EmployeeID = tblWorkHours.EmployeeID;

    Click image for larger version. 

Name:	QryWorkHours.JPG 
Views:	16 
Size:	45.9 KB 
ID:	27458

  6. #21
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    Mr.Ajax,

    have you found a solution?.....

  7. #22
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sorry, did not see your post.

    I've not seen timecard data presented like that but we can work with it

    You will need a similar structure to the one you presented in post#9

    for the regular hours crosstab

    TRANSFORM Sum(tblWorkHours.[Regular Hrs]) AS [SumOfRegular Hrs]
    SELECT .tblWorkHours.EmployeeID, Sum(tblWorkHours.[Regular Hrs]) AS [Total Of Regular Hrs]
    FROM tblWorkHours
    WHERE (((Month([Workdate]))=Month(Forms!frmTimesheetTesting!txtMonth)) And ((Year([workdate]))=Year(Forms!frmTimesheetTesting!txtMonth)))
    GROUP BY tblWorkHours.EmployeeID
    ORDER BY Format("RT" & format([WorkDate],"dd")
    PIVOT Format("RT" & format([WorkDate],"dd") In ("RT01","RT02"....,"RT31")

    for the overtime

    TRANSFORM Sum(tblWorkHours.[OT Hrs]) AS [SumOfOT Hrs]
    SELECT .tblWorkHours.EmployeeID, Sum(tblWorkHours.[OT Hrs]) AS [Total Of OT Hrs]
    FROM tblWorkHours
    WHERE (((Month([Workdate]))=Month(Forms!frmTimesheetTesting!txtMonth)) And ((Year([workdate]))=Year(Forms!frmTimesheetTesting!txtMonth)))
    GROUP BY tblWorkHours.EmployeeID
    ORDER BY Format("OT" & format([WorkDate],"dd")
    PIVOT Format("OT" & format([WorkDate],"dd") In ("OT01","OT02"....,"OT31")

    Then join these to your employee table as you did in post #9 - you may need to use LEFT JOINS between employees and these two queries, otherwise an employee with regular time and no overtime would not be reported

    check this works as a query providing the results in a single row before applying to your form

    then in your form, change the control sources of your time controls top RT01,RT02.... and OT01, OT02.... etc

  8. #23
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    It's give a syntax error, I tried combination and checked but no luck

    Click image for larger version. 

Name:	NewQuery.JPG 
Views:	14 
Size:	80.7 KB 
ID:	27511

  9. #24
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry, that's me just copying your code

    replace

    Format("OT" & format([WorkDate],"dd")

    with

    "OT" & format([WorkDate],"dd")

    you have it in several places

  10. #25
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Did you get a change to look at the link in post #4

  11. #26
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    Mr. Ajax,

    its working great when I running the single Crosstab,
    Click image for larger version. 

Name:	RegQry.JPG 
Views:	10 
Size:	165.0 KB 
ID:	27521
    Click image for larger version. 

Name:	OTQry.JPG 
Views:	10 
Size:	144.3 KB 
ID:	27522

    but when I join two Queries with another Query or Table its not displaying the result
    As you said I have used let join.

    Click image for larger version. 

Name:	NewQueryFeb.JPG 
Views:	10 
Size:	127.8 KB 
ID:	27523

    SELECT RegHrsCrosstabFeb2017.RT01, RegHrsCrosstabFeb2017.RT02, RegHrsCrosstabFeb2017.RT03, RegHrsCrosstabFeb2017.RT04, RegHrsCrosstabFeb2017.RT05, RegHrsCrosstabFeb2017.RT06, RegHrsCrosstabFeb2017.RT07, RegHrsCrosstabFeb2017.RT08, RegHrsCrosstabFeb2017.RT09, RegHrsCrosstabFeb2017.RT10, RegHrsCrosstabFeb2017.RT11, RegHrsCrosstabFeb2017.RT12, RegHrsCrosstabFeb2017.RT13, RegHrsCrosstabFeb2017.RT14, RegHrsCrosstabFeb2017.RT15, RegHrsCrosstabFeb2017.RT16, RegHrsCrosstabFeb2017.RT17, RegHrsCrosstabFeb2017.RT18, RegHrsCrosstabFeb2017.RT19, RegHrsCrosstabFeb2017.RT20, RegHrsCrosstabFeb2017.RT21, RegHrsCrosstabFeb2017.RT22, RegHrsCrosstabFeb2017.RT23, RegHrsCrosstabFeb2017.RT24, RegHrsCrosstabFeb2017.RT25, RegHrsCrosstabFeb2017.RT26, RegHrsCrosstabFeb2017.RT27, RegHrsCrosstabFeb2017.RT28, RegHrsCrosstabFeb2017.RT29, RegHrsCrosstabFeb2017.RT30, RegHrsCrosstabFeb2017.RT31, RegHrsCrosstabFeb2017.[Total Of Regular Hrs]
    FROM OTHrsCrosstabFeb2017 LEFT JOIN (RegHrsCrosstabFeb2017 LEFT JOIN QryEmployeeTimeCard ON RegHrsCrosstabFeb2017.EmployeeID = QryEmployeeTimeCard.EmployeeID) ON OTHrsCrosstabFeb2017.EmployeeID = QryEmployeeTimeCard.EmployeeID;

  12. #27
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    HItechCoach

    it is a good tool for Scheduling but it is different from what I am looking.... thanks a lot for taking special effort...

  13. #28
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the left joins need to go the other way - parent is the employee query

  14. #29
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    mr. Ajax,

    Thanks a lot, my query is working Fine. but each time when I open the query its asking me for "Forms!frmTimesheetTesting!txtMonth" in a small pop box if I enter the dates it is displaying results in my query.
    the same popup box appears when I open the form, even when I enter the dates the results are not displaying. but in reports if I enter the dates it is displaying the result....
    I have verified the text box name as "txtMonth", i have verified the form name as "frmTimesheetTesting" I have set a macro to refresh on open. but still no luck.

  15. #30
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suspect because this is a crosstab you need to predeclare your parameters

    in the query design window, on the ribbon, click on parameters/ Enter 'Forms!frmTimesheetTesting!txtMonth' in the parameter column (no quotes) and in the datatype column, select Date/Time

Page 2 of 3 FirstFirst 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