Results 1 to 7 of 7
  1. #1
    opensste is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Location
    Sydney, Australia
    Posts
    3

    Question Including records in query when no matching records exist

    I have 2 Access Database tables, 1. Element (ElementID, Name) and associated 2. Property (PropertyId, ElementID, PropertyKey, PropertyValue).

    i.e. an Element can have none, one or many Properties where the PropertyId is the unique primary key for an Element Property, ElementId is the foreign key to the associated Element and PropertyKey is the type of Property.

    I am trying to produce a query which displays one line per element under columns ElementId, ElementName, PropertyKeyA (display the value for this PropertyKey), PropertyKeyB (value) and PropertyKeyC (value) where an element may or may not have all of these properties i.e. PropertyKeyA, PropertyKeyB and PropertyKeyC are valid values for PropertyKey (amongst others).

    My problem is that where an Element does not have all three properties, it does not include the element in the report. I can easily produce a list of all elements and valid properties by linking the two tables in the query via an Outer Left Join and criteria of "PropertyKey in ("PropertyKeyA", "PropertyKeyB", "PropertyKeyC") but this gives me multiple lines for each element with duplicated values for Element Id and ElementName.

    However, when I link the Element table with three separate "instances" of the Property table using ElementId, and using Join Type 2 and criteria values "PropertyKeyA", "PropertyKeyB", and "PropertyKeyC" for each respective table instance join, the resulting SQL statement combines all three joins using "And" for the Where clause conditions, forcing all three properties to exist for an element to be included in the list.

    NB I do not want to add "null" records to the Property table for all property types (property keys) that do not apply to an Element.

    I provide the following sample column name and data values, using "/" as a logical separator for this example only:

    Element:


    Element Id/ElementName
    1/Element1
    2/Element2
    3/Element3

    Property:
    PropertyId/ElementId/PropertyKey/PropertyValue
    1/1/PropertyKeyA/ValueA1
    2/1/PropertyKeyB/ValueB2
    3/1/PropertyKeyC/ValueC3
    4/1/PropertyKeyD/ValueD4
    5/2/PropertyKeyA/ValueZ1
    6/2/PropertyKeyB/ValueZ2
    7/2/PropertyKeyD/ValueW5
    8/3/PropertyKeyA/ValueG1
    9/3/PropertyKeyB/ValueH2
    10/3/PropertyKeyC/ValueCK3

    Using the above values, I want the query to return:
    Element Id/ElementName/PropertyKeyA/PropertyKeyB/PropertyKeyC

    1/Element1/ValueA1/ValueB2/ValueC3
    2/Element2/ValueZ1/ValueZ2/
    3/Element3/ValueG1/ValueH2/ValueCK3

    Instead I get:
    1/Element1/ValueA1/ValueB2/ValueC3
    3/Element3/ValueG1/ValueH2/ValueCK3

    Could anyone advise how I can re-write the query to achieve the above?

    Thanks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My problem is that where an Element does not have all three properties, it does not include the element in the report. I can easily produce a list of all elements and valid properties by linking the two tables in the query via an Outer Left Join and criteria of "PropertyKey in ("PropertyKeyA", "PropertyKeyB", "PropertyKeyC") but this gives me multiple lines for each element with duplicated values for Element Id and ElementName.
    Well that is exactly how queries work. To put each of the Properties of an Element on a single line you will need a function.
    See this one from Allen Browne.

    It would be clearer if you would post your query SQL and the specific result you get. Also, could you tell us more about Property key and Value. You may be missing a table.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Just how are you getting those results if you aren't using VBA function like Allen Browne's?

    Why is PropertyKeyD excluded?

    Don't use INNER JOIN in query joining the tables - use outer (LEFT or RIGHT).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    opensste is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Location
    Sydney, Australia
    Posts
    3
    Thanks for your replies. I will provide examples of queries to give you a better picture...

    1. The following query shows you the table contents of Element and Property:

    SELECT Element.ElementId, Element.ElementName, Property.PropertyId, Property.PropertyKey, Property.PropertyValue
    FROM Element INNER JOIN Property ON Element.ElementId = Property.ElementId;

    ElementId ElementName PropertyId PropertyKey PropertyValue
    1 Element1 1 A A1
    1 Element1 2 B B2
    1 Element1 3 C C3
    1 Element1 4 D D4
    2 Element2 5 A Z1
    2 Element2 6 B Z2
    2 Element2 7 D W5
    3 Element3 8 A G1
    3 Element3 9 B H2
    3 Element3 10 C CK3


    2. The following query also selects the records and data that I am interested in but I get multiple rows as an output:

    SELECT Element.*, Property.PropertyId, Property.PropertyKey, Property.PropertyValue
    FROM Element INNER JOIN Property ON Element.ElementId = Property.ElementId
    WHERE (((Property.PropertyKey) In ("A","B","C")));
    ElementId ElementName PropertyId PropertyKey PropertyValue
    1 Element1 1 A A1
    1 Element1 2 B B2
    1 Element1 3 C C3
    2 Element2 5 A Z1
    2 Element2 6 B Z2
    3 Element3 8 A G1
    3 Element3 9 B H2
    3 Element3 10 C CK3


    3. The following query gives me what I want in the way of columns to be displayed but drops a record and its data values as one (and only) one property is not included in the set of possible values that I want displayed:

    SELECT Element.*, Property.PropertyValue, Property_1.PropertyValue, Property_2.PropertyValue
    FROM ((Element LEFT JOIN Property ON Element.ElementId = Property.ElementId) LEFT JOIN Property AS Property_1 ON Element.ElementId = Property_1.ElementId) LEFT JOIN Property AS Property_2 ON Element.ElementId = Property_2.ElementId
    WHERE (((Property.PropertyKey)="A") AND ((Property_1.PropertyKey)="B") AND ((Property_2.PropertyKey)="C"));

    ElementId ElementName Property A Property B Property C
    1 Element1 A1 B2 C3
    3 Element3 G1 H2 CK3


    You see that Element 2 is missing from the list - I would like it included, and for the query to display values for Property A and Property B, and leave blank for Property C for Element 2.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Probably because the WHERE clause uses AND operator. Only Elements 1 and 3 meet the condition of having all 3 property keys.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    opensste is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Location
    Sydney, Australia
    Posts
    3
    Thanks for the response. I have spent some time with this previously and since, trying the different logic criteria for the WHERE clause but I don't seem to have pinned it down without multiplying the results returned. I still want to get records that have "A" and "B" but no "C", leaving the Property C blank in the results. In the following example, I expect a second record displayed with ElementId = "2", ElementName = "Element2", Property A = "Z1", Property B = "Z2", and Property C is left blank for this record.

    ElementId ElementName Property A Property B Property C
    1 Element1 A1 B2 C3
    3 Element3 G1 H2 CK3

    Try it for yourself - I'm finding it quite a challenge. Any further advice would be appreciated.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Using dataset example in item 1 of post4:

    TRANSFORM First(PropertyValue) AS FirstOfPropertyValue
    SELECT ElementID, ElementName
    FROM QueryName
    GROUP BY ElementID, ElementName
    PIVOT PropertyKey;

    or

    SELECT ElementID, ElementName, Max(IIf([PropertyKey]="A",[PropertyValue],Null)) AS PropertyA, Max(IIf([PropertyKey]="B",[PropertyValue],Null)) AS PropertyB, Max(IIf([PropertyKey]="C",[PropertyValue],Null)) AS PropertyC
    FROM QueryName
    GROUP BY ElementID, ElementName;

    ElementID ElementName PropertyA PropertyB PropertyC
    1 Element1 A1 B2 C3
    2 Element2 Z1 Z2
    3 Element3 G1 H2 CK3
    I should have seen this was just crosstab a year ago but I was just not understanding what was desired for output. Oh well, maybe someone will find it useful.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-10-2015, 07:51 AM
  2. Replies: 2
    Last Post: 01-23-2015, 12:14 PM
  3. Replies: 1
    Last Post: 06-06-2013, 06:12 PM
  4. Query to exclude non matching records
    By L1882 in forum Queries
    Replies: 1
    Last Post: 04-03-2012, 08:44 AM
  5. Replies: 1
    Last Post: 10-24-2011, 08:01 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums