Results 1 to 5 of 5

Thread: query help

  1. #1
    Join Date
    Jul 2009
    Posts
    16

    Unanswered: query help

    this query worked before i added the mileage and break fields to the tables... now this query adds another row to the results for the day that break or mileage is entered. Any Help???

    Query
    SELECT locations.location_id, DateValue(equipment_history.record_date) AS [Date], Max(TimeValue(equipment_history.record_date)) AS [Time Out], equipment_history.break, equipment_history.mileage
    FROM locations INNER JOIN equipment_history ON locations.location_id = equipment_history.from_location
    GROUP BY locations.location_id, DateValue(equipment_history.record_date), equipment_history.break, equipment_history.mileage, locations.location_type="Employee"
    HAVING (((locations.location_type)="Employee"))
    ORDER BY locations.location_id;

    Results (*truncated to save room)
    location_id Date Time Out break mileage
    13 9/26/2009 12:40:29 PM 30 60
    13 9/26/2009 12:40:10 PM
    6 9/26/2009 3:02:10 PM

    as you can see location_id 13 has 2 records here becasue he has break and mileage entered, but location_id 6 only has one, because he doesnt...

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    It is doing as "it is told"...

    A row will be returned for each unique set of vaules in the Group By fields (including equipment_history.milage and equipment_history.break)

    Can you give some example data and desired output?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Jul 2009
    Posts
    16
    here is a pdf of my report. If you look down at employee id 6650 you will see he has two entries for 9/25 and 9/26....

    i put the break and mileage in only on 6650 and those dates just to test functionality....

    what i need it to do is for 6650 to not list both entries, only the entry with break and miles.

    i have attached the .pdf file

    thanks for your help
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2009
    Posts
    16
    figured it out, i just put a max on break and mileage and it worked.... the only problem now is if someone forgets to put in break or mileage, my report won't total up correctly

    thanks for your help...

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Indeed

    "if someone forgets to put in break or mileage"

    ...Don't allow them to do this then;

    Trap with code, cater for nulls and erroneous data on your input form?...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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