Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    34

    Unanswered: Gouping in a query with Null values

    Hi All,

    I have a query that returns two records that are very similar to each other except some of the values on the second record are null.

    I can't exclude all the nulls because sometimes a single record is returned with null values that I need to keep.

    So is there a way to group them so the two records that are similar get grouped into one record? The only difference between them is null values and when I try to group, it seperates them based on that.

    Thanks,

    SD

  2. #2
    Join Date
    Dec 2002
    Posts
    97

    Null

    I guess the real question is do those Null value constitute a truly different record? Meaning is it really a different instance and if it is then why don't you want to retain the distinction between the records?

    If it isn't then either a) don't include those fields or b) populate them with the value that are in the other record so both records are correct.

    Again, the question is how you are handling your data because technically, you have two different records.

    -C

  3. #3
    Join Date
    Dec 2002
    Posts
    34

    Re: Null

    Originally posted by Mr_Yabbo
    I guess the real question is do those Null value constitute a truly different record? Meaning is it really a different instance and if it is then why don't you want to retain the distinction between the records?

    If it isn't then either a) don't include those fields or b) populate them with the value that are in the other record so both records are correct.

    Again, the question is how you are handling your data because technically, you have two different records.

    -C
    I don't need to retain the distinction.

    For Example:

    The fields are Company Location.

    I have two tables. tblCompany and tblLocation.

    I have a left join from tblcompany.companyid to tbllocation.companyid.

    I need a left join because some the location field in tbllocation are null and I need all the companies in tblcompany and if there is a location in tbllocation then bring it in too.

    What is happening is that I am getting all the company records. Then all the company records again where there is a location.

    I don't want the two records if there is a location.

    Hope I haven't confused you too much. Thanks for the reply.

    SD

  4. #4
    Join Date
    Dec 2002
    Posts
    97

    Hmm.....

    Okay,

    I'm not sure I completely understand but here may be a solution.

    Create a query/table that has all possible instances of the company ID matched with the appropriate company Location.

    Use that table as the intermediary between the two tables so that you will always have clean data. Then create a report based on the query that uses that table.

    For instance if tblCompany has 1 = San Fran, 2 = NY but tblLocation has 1 = San Fran, 2 = Null then create a query/table that takes all possible instances and remove the Null values from that. So 2 = NY. Now use that table as the basis for your report or to join the two tables and populate the Null values as needed.

    Does that help or kind of answer the problem?

    -C

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have a LEFT JOIN from tblCompany to tblLocation matching on companyid

    this gives you all companies, and those that have a matching location record, the location fields will be available too, and in this case, it appears that you want the location from the tblLocation record to be used instead

    use IIF to pick the location from the tblLocation instead of the location from the tblCompany whenever the companyid did match (in which case tblLocation.companyid is not null)
    Code:
    select tblCompany.companyid
         , IIF(isnull(tblLocation.companyid)
              , tblCompany.location
              , tblLocation.location) as thelocation
      from tblCompany
    left outer
      join tblLocation
        on tblCompany.companyid = tblLocation.companyid
    rudy
    http://r937.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
  •