Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Left Join Issue (Access 2003)

    I'm sure this is simple; I've searched but don't find an answer..

    I have two tables: tblErrors(TimeSlot as Number, Device as Number) and tblAllTimeSlots(TimeSlot as Number). tblAllTimeSlots contains all timeslots within a time period. They are in order but not sequential. If you're interested, TimeSlot is a number ddhh, so 2600 is 00:00 - 00:59 on the 26th day. Hence the sequence is 2600-2623,2700-2723 etc. Let's forget about month end for the moment.

    I have a query to count the number of errors for a device which looks like:
    SELECT DISTINCTROW tblErrors.TimeSlot, Count(*) AS ErrorCount
    FROM tblErrors
    WHERE tblErrors.Device=23614
    GROUP BY tblErrors.TimeSlot;

    Obviously, if there are no errors for a timeslot, the query does not return an entry. Also, I have one of these queries for each device.

    I want to do two things. First, I want to left join tblAllTimeSlots so that I get a record for every time slot, even if there are no errors at that time.

    I tried:
    SELECT tblAllTimeSlots.TimeSlot, qryErrorCount23614.ErrorCount
    FROM tblAllTimeSlots LEFT JOIN qryErrorCount23614 ON tblAllTimeSlots.TimeSlot = qryErrorCount23614.TimeSlot;

    This returns all the times as expected, but every row has ErrorCount null. What am I doing wrong?

    The second thing I'd like to do is turn the whole lot into a crosstab query so I end up with a single query
    qryAllErrors(TimeSlot,Device1ErrorCount,Device2Err orCount..DevicenErrorCount)
    Since I can't get the first part working I haven't looked at the second.

    Thanks in advance for any suggestions.

    Marty

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Left Join Issue (Access 2003)

    Try

    SELECT tblAllTimeSlots.TimeSlot, Nz(qryErrorCount23614.ErrorCount,0) AS N
    FROM tblAllTimeSlots LEFT JOIN qryErrorCount23614 ON tblAllTimeSlots.TimeSlot = qryErrorCount23614.TimeSlot;

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Left Join Issue (Access 2003)

    Hans,

    The nz() expression results in all timeslots from tblAllTimeSlots taking the value 0, irrespective of the records in qryErrorCount23614. I have done a sanity check for typos, can't see anything. I'd have expected the original query to work even with nulls (that's the purpose of the left join?).

    Thanks,
    Marty

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Left Join Issue (Access 2003)

    This would imply that the Timeslot values in qryErrorCount23614 don't match any of the Timeslot values in tblAllTimeSlots. Check this carefully.

    If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Left Join Issue (Access 2003)

    Blush mumble mumble. Sorry, very silly mistake and it all works as it should, without the nz().
    Got the crosstab mostly worked out. Currently takes three queries (I'm a fan of the single clever query) and it gives me an average column I don't need, but it works.

    Thanks again and apologies for being a duffer.

    Martin

Posting Permissions

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