Combining multiple rows into a single row for data entry

[tonygg]

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

[June7]

I don't think any query to do what you want can be editable.

Do a query that returns the Cut property records. Do another query that returns the Fat property records. Do another query that joins the first 2.

Review http://allenbrowne.com/func-concat.html

[tonygg]

Thank you so much for looking at this for me. I have now got the views and combined them so it gives me one row. As you point out I cant update the data in the fields. Any thoughts on how I should go about making the data available to update. It needs to be in a data sheet format as it is going to be used for stock update and so there is a lot of products to update in one go. Any ideas or points would be gratefully received. Thanks Tony

[June7]

Maybe two subforms side-by-side?

[tonygg]

Thank you. Not sure how to do this. Would this look like a table where you could tab between the fields in a row? If so could you help me with how to setup such a side by side form (sorry to have to ask).

Many thanks

Tony

[June7]

It's just two subforms sitting on another form. Would have to click in/out of each.