Results 1 to 5 of 5

Thread: Joins (A2002)

  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Joins (A2002)

    Can somebody please help, my brain has fried.
    I have a table (called tblCustomerService) with amongst other fields a CallCentre field (this field can have either the values of 'Main' or 'Advisory' in it).
    I have another table (call tblCallCentre) with just the CallCentre field in it (only has one record). It could hold either 'Main', 'Advisory' or be Null.

    I need to create a query that can select:
    1. All records from tblCustomerService where the CallCentre field in tblCallCentre is Null, or
    2. Just the records from tblCustomerService where the CallCentre field in both tables is 'Main' , or
    3. Just the records from tblCustomerService where the CallCentre field in both tables is 'Advisory'.

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

    Re: Joins (A2002)

    Create a new query in design view.
    Add tblCustomerService and tblCallCentre to the query, but don't join them.
    Add * from tblCustomerService to the query grid
    Add CallCentre from tblCallCentre to the query grid.
    Clear the Show: check box for CallCentre.
    Enter the following expression in the Criteria: line for CallCentre:

    [tblCustomerService].[CallCentre] Or Is Null

    I think this will do what you want.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Joins (A2002)

    That does not work for where the tblCallCentre has no record.

    However if I have a blank record in tblCallCentre then it works. It seems strange, but then, when you test for Null you obviously need a record with a Null Call Centre field for it to work.

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

    Re: Joins (A2002)

    Yes, Is Null tests for an empty, blank value; it doesn't handle an absent, non-existing value. Offhand, I don't know of a simple way to handle tblCallCentre having no record, so if you can live with using a blank record, that would be easiest.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Joins (A2002)

    I certainly can live with that restriction, case solved.
    I had developed your query earlier (before I put the question on the forum) but to no avail because I was trying to test with no record in the tblCallCentre table, oh well, alls well that ends well.

    Thanks for your time Hans.

Posting Permissions

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