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?