Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Query Problem

  1. #1
    Join Date
    Jun 2008
    Posts
    8

    Unanswered: Query Problem

    Hi there folks, I am having a problem with a query.

    I am trying to pull records from three related tables using a range of dates.

    The tables are called mechanics, jobs and timesheets. I want to compare the amount of hours a mechanic has worked on individual jobs,
    compared to the the amount of hours that they have claimed on their time sheet for either a single day or a range of days.

    Now when I use just one day as the date range i.e.

    startDate - 01/01/08
    endDate - 01/01/08

    it works fine. However if I extend the range i.e.

    startDate - 01/01/08
    endDate - 02/01/08

    This is quite hard to explain, so I have taken a screen shot to show what I mean:-

    http://i32.tinypic.com/e9hus4.jpg



    I am using the query builder for my query, but if I view it's sql, it looks like this:-

    SELECT Mechanics.mechanicName, Jobs.startTime, Jobs.finishTime, Jobs.hoursWorked, Jobs.jobDescription, TimeSheets.checkIn, TimeSheets.checkOut, TimeSheets.totalhours, Jobs.jobDate, TimeSheets.timesheetDate
    FROM (Mechanics INNER JOIN Jobs ON Mechanics.mechanicID = Jobs.mechanicID) INNER JOIN TimeSheets ON Mechanics.mechanicID = TimeSheets.mechanicID
    WHERE (((Mechanics.mechanicName)=[Forms]![frmSearchDateRange]![txtMechanicName]) AND ((Jobs.jobDate) Between [Forms]![frmSearchDateRange]![txtStartDate] And [Forms]![frmSearchDateRange]![txtEndDate]) AND ((TimeSheets.timesheetDate) Between [Forms]![frmSearchDateRange]![txtStartDate] And [Forms]![frmSearchDateRange]![txtEndDate]));

    I am getting the mechanic name, the start date and end date from text boxes on a form.
    Attached Thumbnails Attached Thumbnails report.JPG  

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    It looks to me like you're using multiple records in one of the tables; either the Jobs table, you're using multiple records to define a job, or in the TimeSheets table, to define a day's work. That's not necessarily wrong. However, if that's the case, I suggest you use the form to build a temporary table one record at a time in VBA (behind the form), and run the report on that table.

    Hope this helps,
    Sam

    PS. By the way, I suggest you use a combo box to obtain the mechanic's name from your form, not a text box, as it's readily adaptable to a simple select query, and you know it'll only give you valid choices.

  3. #3
    Join Date
    Jun 2008
    Posts
    8
    Thanks for the reply Sam. Could you give me an example of how to do what you suggest using VBA? as I am a total newbie to it.

    Also, I tried using a combo box for exactly the reasons you suggested, but couldn't get it to run for whatever reason. Will look at that problem again also though.

    *Edit - just to clarify what the problem is - if I use just one day as the date range to search, it does what I want. If I use more than one day, then it repeats the records that are in each table, on the report. So in the screen shot that I have shown in my first post, there are two clutch jobs and two brake jobs, but in the jobs table, there is only one clutch job and one brake job.

    And the totals on the report (Total Hours Worked, Total Hours Clocked in and Difference) are not fields in any table, they are simply calculated on the report using the relevant totals from the tables.

    Hope that makes sense!
    Last edited by theglobe; 06-04-08 at 20:48.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'm not seeing a problem here. Looks to me like the query is correct.

    The total hours clocked in just needs to be able to show 32 hours instead of being a time format which doesn't work with values like 32 hours (32 hours - 24 hours in a day is 8 hours). Remove the format from this object and multiply the result by 24. You'll have to do the same with the difference object too.

    Mind you I am sleep deprived atm, so I may just not be seeing the problem ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jun 2008
    Posts
    8
    I have uploaded an example of my form, query and report to make things easier to explain.
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup... Sleep deprived.

    However:

    First up, get used to the Compact and Repair command. Your 14MB database quickly becomes 376K.

    Second, put a combo box on the form so that the user can select the mechanic name.

    Third, I'd get rid of the input masks.

    Fourth, get rid of the TotalHours / HoursWorked fields. They should be calculations; there is no need to store them.

    Now onto the problem ^^

    You're going to need to create three queries. One to get the correct JOB data, one to get the correct TIMESHEET data and one to join the two queries together to support the report.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jun 2008
    Posts
    8
    Thanks for the reply. Can you give me an example of how to join two queries together like you suggest?

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think I better sleep first before I give some bad advice

    Remember though, the reason why you are getting four records is because there are four records! Two JOB records and two TIMESHEET records. The only way to stop that from happening is summarising the report -- not showing all the data like you are doing, but just have, say one record for JOB data, summarising the totals and one record for TIMESHEET data, again summarising the totals.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jun 2008
    Posts
    8
    So how do I summarise my report? When I use the wizard to create it, I don't see any options to summarise certain records etc

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like you want to GROUP BY mechanic and Sum() the amounts, right?
    George
    Home | Blog

  11. #11
    Join Date
    Jun 2008
    Posts
    8
    If I use GROUP BY wont it still return 4 records when I only really want to see 2 though? - 1 for the job that the mechanic has worked that day, and one for the time sheet he has submitted that day.

    If I only wanted to group jobs by each mechanic for each individual day I think that would work, but I am trying to compare the total hours worked for each job with the total on the time sheet.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The key word in the confusing description above was "total".

    to find totals, you use aggregate functions, which require grouping.

    Your requirements don't make all that much sense to me (it's been a long day). Can you provide us with some sample data and required output please?
    George
    Home | Blog

  13. #13
    Join Date
    Jun 2008
    Posts
    8
    Yes sure mate. I uploaded a sample form, report and query earlier that shows what I am trying to do.

    I have three tables - jobs, mechanics and timesheets.

    jobs and mechanics are related through mechanicID. timesheets and mechcanics are also realted through mechanicID

    If a job is entered into the jobs table, this is what the user enters on the form

    Job Number
    Mechanic - on the form it is a name that the user choses from a combo, but obviously its really the mechanicID which is used
    Date
    Start time
    Finish time
    Job description

    Now there is a separate form where the user enters time sheets. The enter the following:-

    Mechanic name - again from a combo
    Date
    Clock in time
    Clock out time



    What I want is a form where the user can chose a mechanic and then enter a start date and an end date.

    They click a button and a report shows each job that particular mechanic has done between the dates chosen and totals the hours the jobs took to complete. I can get this bit working.

    But I also want the report to show the total amount of hours that the mechanic has claimed on their time sheets for the same period.

    So for example - he might have completed 10 jobs which took 10 hours in total, but his time sheets are showing he was clocked in for 20 hours.

    Does that make it a bit clearer?

  14. #14
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Query1 - use jobs table Group By Mechanic and Job Number, use Where for JobDate and set the criteria to Between [Forms]![frmSearchDateRange]![txtStartDate] And [Forms]![frmSearchDateRange]![txtEndDate] and add a field for JobHoursum(Finish Time - Start Time)

    Query2 - use time sheet table Group By Mechanic, use Where for WorkDate and set the criteria to Between [Forms]![frmSearchDateRange]![txtStartDate] And [Forms]![frmSearchDateRange]![txtEndDate] and add a field for HoursWorkedum(Clock Out - Clock In)

    Query3 - Use Query1 and Query2, join on Mechanic, bring in all fields from query2 and JobNumber and JobHours from query1


    Create a report based on query3. You will have HoursWorked for each record. Set this control's visible property to no. Create a textbox in the footer that takes the average of the now hidden control.

  15. #15
    Join Date
    Jun 2008
    Posts
    8
    Quote Originally Posted by rogue
    Query1 - use jobs table Group By Mechanic and Job Number, use Where for JobDate and set the criteria to Between [Forms]![frmSearchDateRange]![txtStartDate] And [Forms]![frmSearchDateRange]![txtEndDate] and add a field for JobHoursum(Finish Time - Start Time)

    Query2 - use time sheet table Group By Mechanic, use Where for WorkDate and set the criteria to Between [Forms]![frmSearchDateRange]![txtStartDate] And [Forms]![frmSearchDateRange]![txtEndDate] and add a field for HoursWorkedum(Clock Out - Clock In)

    Query3 - Use Query1 and Query2, join on Mechanic, bring in all fields from query2 and JobNumber and JobHours from query1


    Create a report based on query3. You will have HoursWorked for each record. Set this control's visible property to no. Create a textbox in the footer that takes the average of the now hidden control.
    Thanks for that Rogue.

    When I try and group the first query just by Mechanic and Job Number, I get the following error message:-

    You tried to execute a query that does not include the specified expression 'startTime' as part of an aggregate function

    I am using the query builder, not writing the SQL myself. If I leave the Group By option in the rest of the fields, the query will run. Is it ok to do this?
    Last edited by theglobe; 06-06-08 at 08:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •