Formatting Query Results

[tonygg]

Hi. I am hoping for some help formatting my query results. I have tried to be as clear as I can so I hope it is understandable. Thank you in advance ....

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

[NTC]

so when you join 1 to Many the query result will always have the record count of the Many. That's the way it is. To have fields display side-by-side is a Cross Tab query. that is the topic you will want to look up. If you truly always and only have 2 records on the Many side a cross tab is very viable. The inherent problem with cross tab is when the Many side is a very large set and attempting to put fields side by side becomes unwieldy.

the cross tab is an extra step purely for the sake of displaying things

[tonygg]

Thank you so much for looking into this issue. I do only have a fixed number of fields. In fact 4. It is a breakdown of stock number into 2 sub categories and 2 explanation fields. There is a row in the table for each of the 4 categories. I am having trouble using the cross tab query. My final sql is:

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 (((p.[product reference]) Not Like '*!*') AND ((v.sname)='Cut Fat Quarter Stock Level' Or (v.sname)='Fat Quarters on Bolt' Or (v.sname)='Stock Choices' Or (v.sname)='Stock Message') )
ORDER BY p.[product reference], v.nID;

The cross tab query wizard I have created using Firstof attribute (it seemed the only type to choose):

TRANSFORM First([TONY Stock Breakdown].sValue) AS FirstOfsValue
SELECT [TONY Stock Breakdown].[short description], [TONY Stock Breakdown].[nStockOnHand], First([TONY Stock Breakdown].[product reference]) AS [FirstOfproduct reference]
FROM [TONY Stock Breakdown]
GROUP BY [TONY Stock Breakdown].[short description], [TONY Stock Breakdown].[nStockOnHand]
PIVOT [TONY Stock Breakdown].[sname];

This gives me the correct layout but I cant seem to change any of the data in the fields. I need to be able to update the fields stockinhand and the 4 sValue fields.

Any ideas?

Thanks once again

Tony

[NTC]

crosstab is not an updateable query. sorry forgot that aspect of your requirement

so now you have to do a 2nd phase which would be a Write query into a temp table - then source your form on the temp table

of course you then have to reverse write back to its actual table

a lot of hassle for that side by side display. would be a lot easier to put 2 subforms side by side.........

[tonygg]

Thank you. Not sure what side by side forms would look like. Could it look like a table where you can tab through the columns. Could you point me in the direction to see/learn how to create it? Many thanks Tony

[NTC]

actually I would stick with vanilla Access - have your Main form based upon your 1 table...have those fields
...and then make a form based upon your Many table...with its fields
- and then insert the Many form into the Main form as a sub form...


that's really the right way to do it....everything else is convoluted....

[tonygg]

Hi. Thank you for the advice. I have managed to create a cross tab query to show the data in one row. I put it in a split form and used sub forms to enter the updated data. All good so far. However to allow me to know which rows to update I have put conditional formatting on the cross tab query. It is a simple field 1 - field 2 <> 0 then highlight field 1. The problem is that the conditional formatting is not always showing. You can open the form and it will show the hight light on some rows but not others. You can close and reopen immediately and it shows on other fields. It is not that it shows on wrong fields. The issue is sometimes it does show on rows when it should. I tried f9 refresh but this doesn't fix it. I was hoping you may have a suggestion as I am at a loss. There are 1000s of rows so the conditional formatting is vital. Many thanks tony