Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69

    Unanswered: Duplicate entry return

    Hello,
    I have a problen:

    I am using IIF statement in my query and for not sure what reason it return double enrty.
    One is with actual text data, another with blank for the same ID
    How can I get read of blank data?

    SELECT
    IIf(dbo_object!object_type_id=0,dbo_object!object_ id) AS ID,
    entblProducts.[Product Sku],
    IIf(dbo_attr__string_attribute!attribute_id=0,dbo_ attr__string_attribute!value) AS Name,
    IIf([dbo_attr__text_attribute]![attribute_id]=4,[dbo_attr__text_attribute]![value]) AS [Description 1],
    IIf([dbo_attr__string_attribute]![attribute_id]=71,[dbo_attr__string_attribute]![value])
    FROM ((dbo_object INNER JOIN dbo_attr__string_attribute
    ON dbo_object.object_id = dbo_attr__string_attribute.object_id)
    INNER JOIN entblProducts ON dbo_object.object_id = entblProducts.id)
    INNER JOIN dbo_attr__text_attribute ON dbo_object.object_id = dbo_attr__text_attribute.object_id
    GROUP BY IIf(dbo_object!object_type_id=0,dbo_object!object_ id), entblProducts.[Product Sku],
    IIf(dbo_attr__string_attribute!attribute_id=0,dbo_ attr__string_attribute!value),
    IIf([dbo_attr__text_attribute]![attribute_id]=4,[dbo_attr__text_attribute]![value]),
    IIf([dbo_attr__string_attribute]![attribute_id]=71,[dbo_attr__string_attribute]![value]);


    Many thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    1. Encapsulate ID in []'s.
    2. Your last Iif() statement doesn't have a field name.
    3. Could you post a couple sample rows and what's wrong with them?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    This is what I got, dups code with Description 1 of null and data,
    and I need only data.

    Thank you


    code ID Product Sku Name Description 1
    104 women-legwear-104 women-legwear-104 Women's Professional Style 104
    104 women-legwear-104 women-legwear-104 Women's Professional Style 104 These professional weight fishnet /durability and comfort.

    1074 nycb-pirouette-1074 nycb-pirouette-1074 NYCB Pirouette Princess Leotard
    1074 nycb-pirouette-1074 nycb-pirouette-1074 NYCB Pirouette Princess Leotard This Tank has princess seams

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What does this do?

    IIf([dbo_attr__string_attribute]![attribute_id]=71,[dbo_attr__string_attribute]![value])


    You're grouping on it, and it appears to be the value that's changing...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    I'm sorry,

    its for description 2 field

    Description 2: IIf([dbo_attr__string_attribute]![attribute_id]=71,[dbo_attr__string_attribute]![value])

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by LP2005
    I'm sorry,

    its for description 2 field

    Description 2: IIf([dbo_attr__string_attribute]![attribute_id]=71,[dbo_attr__string_attribute]![value])
    I see...

    What is it you're trying to do here exactly? Taking a vague stab at what you seem to be attempting, perhaps you could try this instead:

    SELECT
    dbo_object!object_type_id AS ID,
    entblProducts.[Product Sku],
    dbo_attr__string_attribute!attribute_id AS Name,
    [dbo_attr__text_attribute]![attribute_id] AS [Description 1],
    [dbo_attr__string_attribute]![attribute_id] As [Description 2]
    FROM ((dbo_object INNER JOIN dbo_attr__string_attribute
    ON dbo_object.object_id = dbo_attr__string_attribute.object_id)
    INNER JOIN entblProducts ON dbo_object.object_id = entblProducts.id)
    INNER JOIN dbo_attr__text_attribute ON dbo_object.object_id = dbo_attr__text_attribute.object_id
    WHERE dbo_object!object_type_id=0 AND dbo_attr__string_attribute!attribute_id=0 AND [dbo_attr__text_attribute]![attribute_id]=4 AND [dbo_attr__string_attribute]![attribute_id]=71
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2005
    Posts
    102
    In reply to: I'm sorry,

    its for description 2 field

    Description 2: IIf([dbo_attr__string_attribute]![attribute_id]=71,[dbo_attr__string_attribute]![value])
    The only true wisdom is in knowing you know nothing.

  8. #8
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69

    multy rows, neew one

    Hello ,
    I have a query in Access that has to return data all some IDs
    and in my case data comes back on two rows

    I have two attrib_id (4 and 71) and if I my ID has both data description for 4 and for 71, my results are in two rows.

    ID atrib_type Descrip1 Descrip2
    104 4 some text
    10 71 Some text

    The Attrib ID not ness. has to be shown

    My question is:
    Is it possible to have Desc1 and Desc2 in one row ? for partic ID.


    Thank you very much

Posting Permissions

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