Hi. I have 2 tables the first is product and the second is product properties (1 to many from product). There are 2 properties that I am interested in per product. Each property has a name (sname) and a value (sValue). I have a query that returns the data I want but it creates 2 rows per product (1 for each property):
SELECT p.[product reference], p.[short description], p.nStockOnHand, v.sname, u.sValue
FROM (product AS p LEFT JOIN userdefinedproperties AS u ON p.[product reference] = u.scontentID) LEFT JOIN variable AS v ON u.nVariableID = v.[nID]
WHERE ((((v.sname)='Cut Fat Quarter Stock Level')) AND (p.[product reference] NOT LIKE '*!*'))
ORDER BY p.[product reference], v.nID;
This returns (as an example):
product reference short description nStockOnHand sname sValue 1027 Deco Flowers Apple Rings 1145-G 55 Cut Fat Quarter Stock Level 12 1027 Deco Flowers Apple Rings 1145-G 55 Fat Quarters on Bolt 22 1038 Deco Flowers Pistachio Boxes 1146-G 3 Cut Fat Quarter Stock Level 1 1038 Deco Flowers Pistachio Boxes 1146-G 3 Fat Quarters on Bolt 2
I would like to create a view of the data that shows both properties for a product on the same row:
Product Reference, Short Description, nStockOnHand, sname, SValue , sname, sValue
1027 Deco Flowers Apple Rings 1145-G 55 Cut Fat Quarter Stock Level 12 Fat Quarters on Bolt 22
Please could someone help me adjust the query.
Not sure if this matters BUT: I would want to be able to go into the datasheet view and be able to change the values in the 2 properties fields and have the changes reflected in the DB (as I can now with my query)
Many thanks for you help
Tony