Access/SQL Question

Discussion in 'OT Technology' started by Penguin Man, Aug 6, 2003.

  1. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    OK, so at work I have an Access database that has all the employees in it. It also has all the incidents (injuries, car accidents, etc...) in it. It's organized like this:
    [​IMG]

    So, I have a query that should include all the people hired before a given date, and all the incidents that people have had since a certain date. So, the way I have it setup right now, it includes all the records from the employee table, and all the records in the incidents table that have a date later than a given date. The trouble is, if a person has an incident that occurred before the given date, rather that just having the person show up without the incident, the person just doesn't show up at all. I need that person to show up, but without that incident beside their name.

    So, c'mon all you Access/SQL masters, tell me how to get Access to do this or give me some fancy SQL statement that will do this for me (I know some SQL, but I'm no expert).
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Create a query of the incidents based on the date range you want. Then make another query based on the employee and the first query with whatever criteria you want - as long as you don't limit the incident criteria (since you've already done that), it will return what you want.
     
  3. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    If I make it into two queries, can I combine them onto a report?
     
  4. EPD Hater

    EPD Hater New Member

    Joined:
    Aug 26, 2001
    Messages:
    538
    Likes Received:
    0
    Sounds like you need an OUTER JOIN to pull that off. For example:

    SELECT *
    FROM Employees A LEFT OUTER JOIN Incidents B ON
    (A.ID = B.Employee AND A.Hiredate <= [date] AND B.Date >= [date])

    The LEFT OUTER JOIN tells to bring back rows on the LEFT side of the join (the Employees table) even though there is no match to the Incidents table.
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well if I was doing it, what I would do is create a query/report of your employees, however you want, and do a subreport of the incidents query. The subreport would be linked to the main report via the EmployeeID. Then basically when the report runs it will run each employee regardless, and then in a subreport underneath it, return any incidents, if any. This report can be set to shrink or grow depending on how many fuckups, I mean incidents the employee makes.
     
  6. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Ah, that makes sense. I haven't played with sub-reports, I guess that's what I'll get paid for tomorrow.

    EPD: I think that's similar to what was there before, but I'm not sure. Maybe I'll give that a try too.

    Thanks all :wavey:
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    That statement doesn't work in Access I don't think - not sure if it would work in MySQL or other. I think you can also do a subquery (which is the same thing as I suggested but it looks uglier). This is because the limiting criteria is applied to just the incidents, not the whole recordset. If there was no limiting criteria on the incidents, it would work fine.

    Penguin: Don't sweat subreports - they're just like subforms.

    BTW, the reason I do it this way is because with a query the way you were originally thinking of, the report will always come up with 1 incident with blank fields which you then have to deal with uniquely (depending on your layout). The subreport is different in that it automatically can shrink to nothing if there are no matches.
     
    Last edited: Aug 7, 2003
  8. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Riiiight, I know how to do subforms... Yea, I'm an Access n00b. Oh well, I'll figure them out I'm sure.

    The way I had it (one query, one report) it actually looked good (and worked fine for our purposes), but it did have a problem because of the limiting criteria on the incidents. The other confusing factor is that this query also links to the foremen table, which sort of links to itself for sorting purposes (very confusing).
     
  9. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Got it working this morning, thanks :bigthumb:
     
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Good job - welcome to the wonderful world of Microsoft...

    MuwahahahahahhaahahahaahahahHAHAHAHAHAHAHAHA. PLEDGE YOUR YOUNGEST BORN TO ME OR YOU WILL BE PLAGUED FOREVER WITH GPF'S FOR THE REST OF YOUR LIFE
     
  11. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    :rofl: Yea, welcome to the wonderful world of building databases for construction workers.
     

Share This Page