Results 1 to 5 of 5
  1. #1
    angie is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    232

    Query with three fields need to group by name

    Hi
    I have a query with the following fields

    CustomerID ServiceDate Invoice Rate Helper1 Helper2 Helper3



    I use this query to run a report.

    Example: I could have helper1 be "matt" on one job but on another job he might be helper2.

    I would like it to group my the name I do not need to know which helper he is I just need to know which job he was on and group on the report.

    Thanks Angie

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    angie is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    232
    SELECT Workorders.[Customer ID], Workorders.[Service Call Date], Workorders.[Employees On Job], Workorders.[Invoice Number], Workorders.Rate, Workorders.Hours, Workorders.[Arrived Time], Workorders.[Completed Time], Workorders.[Type Of Call], Workorders.[Helper 1], Workorders.[Helper 2], Workorders.[Helper 3], Workorders.[Projected Cost], Int(DateDiff("n",[arrived time],[completed time])/60) & " hours " & DateDiff("n",[arrived time],[completed time]) Mod 60 & " minutes" AS Expr1
    FROM Workorders
    WHERE (((Workorders.[Service Call Date]) Between #7/13/2016# And #7/26/2016#) AND ((Workorders.[Helper 1]) Is Not Null))
    ORDER BY Workorders.[Employees On Job];
    Quote Originally Posted by orange View Post
    Please post the SQL for the query.

  4. #4
    MrMom is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Location
    Virginia
    Posts
    11
    Angie,
    You could normalize your data by removing [Helper 1], [Helper 2], and [Helper 3] from the Workorders table. Create a separate table, say Workorder_Helper containing, [Customer ID], [Invoice Number], and Helper as data columns. [Customer ID] and [Invoice Number] would relate Workorders and Workorder_Helper to each other. For each workorder, you would have only as many rows in Workorder_Helper as there were actual Helpers on the job.

  5. #5
    angie is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    232
    Not sure I understand
    i enter in the workorder on each one the helpers. There might be one or three. I have to track who was on the job as helpers.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-09-2015, 09:51 AM
  2. Group duplicate text fields as one?
    By jset818 in forum Queries
    Replies: 13
    Last Post: 10-24-2014, 01:05 PM
  3. GROUP by yet SELECT fields not within GROUP BY
    By johnseito in forum Access
    Replies: 25
    Last Post: 11-03-2013, 10:20 PM
  4. Replies: 3
    Last Post: 04-18-2013, 12:02 PM
  5. Query to group fields and sum
    By rlsublime in forum Queries
    Replies: 5
    Last Post: 07-13-2012, 12:48 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