Results 1 to 5 of 5

Thread: Group By

  1. #1
    Join Date
    Apr 2011

    Unanswered: Query

    Hi guys,

    what I'm trying to do is get a breakdown of all jobs by location and outcome (fix or tow)

    I have the following two tables:

    Job {JobID#, City, GPScoordinate}
    TowedInformation {JobID#, Notes}

    Location can be either by city or gps coordinates. If a jobID appears in TowedInformation table it implies that it is "towed", if JobID doesnt feature in TowedInformation it is "Fixed"

    Any help would be greatly appreciated!

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by AccessDatabase View Post
    what I'm trying to do is get a breakdown of all jobs by location and outcome (fix or tow)
    what is a "breakdown" -- a count of jobs?

    or does "breakdown" have something to do with being unable to drive away and therefore must be towed? | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011

    Group By


    I have two tables:

    Job {JobID#, City}
    TowedInformation {JobID#, Notes}

    I now need to do a group by City and Outcome query. If the job appears in TowedInformation it always has notes and was towed, if it doesn't appear in the TowedInformation table it means it is Fixed (roadside).

    I did the following query:

    Select Job.City, TowedInformation.Notes, count (*) as total
    FROM Job, TowedInformation
    Group by Job.City, TowedInformation.Notes;
    However this produces duplicates and it groups the notes which is not very helpful.

    Ideally I would like to get something like this:

    City Outcome Count
    London Fixed 4
    Nottingham Fixed 6
    Nottingham Towed 2
    Any idea how I could modify my query to get something similar to what I am looking for? ie get Access to understand that if a job appears in TowedInformation table it is towed, and if it doesn't that it is fixed? And group appropriately?


  4. #4
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Merged duplicate threads.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2011
    Solved thanks
    Last edited by AccessDatabase; 04-16-11 at 11:07.

Posting Permissions

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