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