Results 1 to 6 of 6

Thread: Query HELP!!!!!

  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Cool Unanswered: Query HELP!!!!!

    I need help,
    I have a 2 tables, one has autonumber for all the units i have, as well as colour and brand. The other table is a service history of the units i have serviced. I need a query that will display all the units i HAVE NOT serviced. PLEASE HELP.

  2. #2
    Join Date
    Sep 2003
    Location
    Gloucester
    Posts
    28
    This may help you


    SELECT Units.Brand, Units.Type, Units.Colour, Service.ServiceDueDate, Service.Serviced
    FROM Units INNER JOIN Service ON Units.UnitID = Service.UnitID
    WHERE (((Service.Serviced)=No));

    assuming your Service field is a yes/no field

  3. #3
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    If you want a list of units that have no associated records in the Services table, you could try a left join. Borrowing Jumper's code:

    SELECT Units.Brand, Units.Type, Units.Colour, Service.ServiceDueDate, Service.Serviced
    FROM Units LEFT JOIN Service ON Units.UnitID = Service.UnitID
    WHERE ((isnull(Service.UnitID)));

  4. #4
    Join Date
    Oct 2003
    Posts
    2
    The service isnt a yes / no, the service is a table, everytime i service something i record yes/no for things i do (painting, weed eating for ex.)
    in table 1 i have the fire hydrant number (autonumber & address)
    in table 2 (service) i have the hydrant number, date serviced, and what i did (yes/no stuff)
    if i never serviced hydrant #7, it is not in the service table because no data entry was done.
    I need to get a list of every hydrant # that does not appear in table 2

  5. #5
    Join Date
    Sep 2003
    Location
    Gloucester
    Posts
    28
    Okay know where you are going now - I think!!!!

    You want to find all the units that do not appear in your service table

    try this

    SELECT Units.UnitID, Units.Brand, Units.Type, Units.Colour
    FROM Units LEFT JOIN Service ON Units.UnitID = Service.UnitID
    WHERE (((Service.UnitID) Is Null));

  6. #6
    Join Date
    Oct 2003
    Posts
    706

    Cool

    This involves what's called an "Outer Join" query. When you have joined two tables in the query view, click on the connecting line. You'll see that you have three choices. When either the second or third choice is selected, the line becomes an arrow.

    A so-called "inner join" query is the usual type. In two tables U and S, the query considers only rows that match in both tables. This would find only units that you have serviced: only IDs that occur in both U and S.

    A so-called "outer join" query causes all of the rows in one table (left join) or the other (right join) to be included whether or not there is a matching record in the other.

    In your example, you probably want "U LEFT JOIN S," an arrow pointing from U to S, indicating that you want to consider all rows in U even if there are no matching records in S.

    So how do you see if there is a matching record? Include the key field from S, and then check for NULL. A key value can never be null (a unit can never lack an ID), so if the joined column is NULL it means that there is no record there at all.

    So... add the tables...join them...click on the join line...select a left-join...add the fields from "Units" you want to see...then add the key-field from "Service"...enter criteria "Is Null"...uncheck the display box on that column (don't need to see it).
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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