LostAndConfused: you seem to be in good hands and I am late to the party (and uninvited I might add) but I can't resist commenting that there are at least 2 things in the requirements that I would probably never do. So this is just for your future consideration.
1) Each traffic offense type is uniquely identified by its offense description. No way, and ssanfu has "corrected" the flaw in his proposed design, but I don't see a reason provided. A bit simplified, but a descriptive field does not a good relationship make. RAN RED LIGHT is as valid as RAN RED LITE since they are unique, yet they mean the same thing. This situation can arise with simple spelling variances, and when queried, you won't get LITE records when the input for a query is LIGHT.
2) year in service (i.e. 2) Again, no way. This is like having an Age field, which is never static from day to day. Neither is YearsOfService. The correct approach would be a StartDate and an expression in a query that subtracts StartDate from Date (the current date, which is another reserved name). Here is a good source for this info, as I don't see where it was provided.
Speaking of names, if you adopt a convention (especially for forms, reports, code variables, etc) you will seldom run in to this problem. Of course, name prefixes don't really apply to table fields, but something like camel case will ensure you avoid bad design through use of spaces or reserved names (e.g. StartDate and not Date and not Start Date).
See http://access.mvps.org/access/general/gen0012.htm and https://access-programmers.co.uk/for...d.php?t=225837
As for the query, it looks to me as if the problem is the outer join between 2 left tables and an inner join on the right. Change the right side join to show all from the center and those from the right that match, or make both equal. Not sure why you'd want all motorist info plus tickets where there is a match, assuming there's no motorist info in your db if there's no ticket related to them.
As always, my peers will comment on items 1 and 2 if they don't agree (or anything else, for that matter).
Last edited by Micron; 06-27-2018 at 03:10 PM.
Reason: clarification & added info
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.