Is it possible to have this report from this structure?

[cap.zadi]

Hey

Can some help out to generate this report "Attached BCSR file" from the data structure "attached Rows into Columns1 file".

the code for query is here.

SELECT tblBodyComp.TrnNo, "B" & [BodyNo] AS [Body No], tblBodyComp.CompDate, tblBodyComp.ProductGroup, tblBodyComp.ProdDate, tblBodyComp.Purpose, tblBodyComp.ApprovedBy, tblBodyComp.Note, tblBodyCompR.RawMaterial, tblBodyCompR.[Composition%]
FROM tblBodyComp INNER JOIN tblBodyCompR ON tblBodyComp.TrnNo = tblBodyCompR.TrnNo;

the productgroup and aprrovedby are multiple values fields from table level combo box lookup fields.


thanks a lot.

zee

[June7]

See these
https://www.accessforums.net/reports...html#post99062
http://allenbrowne.com/func-concat.html

I expect will require several queries to get the desired output. Provide data and will take a look.

[cap.zadi]

Hello Sir, how are you.

can you look at this thread and i am sure you can add some of your expertise.


https://www.accessforums.net/queries...ata-21056.html

thanks

[June7]

I have examined your data and the report sample you show probably cannot be done by queries alone. Will require code as demonstrated by the example in http://forums.aspfree.com/microsoft-...ry-322123.html

This series of queries will get some of it.

Query1
SELECT tblBodyComp.TrnNo, "B" & [BodyNo] AS [Body No], tblBodyComp.CompDate, tblBodyComp.ProductGroup, tblBodyComp.ProdDate, tblBodyComp.Purpose, tblBodyComp.ApprovedBy, tblBodyComp.Note, tblBodyCompR.RawMaterial, tblBodyCompR.[Composition%]
FROM tblBodyComp RIGHT JOIN tblBodyCompR ON tblBodyComp.TrnNo = tblBodyCompR.TrnNo;

Query!_Crosstab
TRANSFORM Sum(Query1.[Composition%]) AS [SumOfComposition%]
SELECT Query1.RawMaterial
FROM Query1
GROUP BY Query1.RawMaterial
PIVOT Query1.[Body No];

Query2
SELECT tblItem.ShortName, Query1_Crosstab.*
FROM Query1_Crosstab RIGHT JOIN tblItem ON Query1_Crosstab.RawMaterial = tblItem.ItemID;

[cap.zadi]

Hello Sir,

Thanks for your time.

what do you think about this?

TRANSFORM Sum(Query1.[Composition%]) AS [SumOfComposition%]
SELECT Query1.CompDate, Query1.[Body No]
FROM Query1
GROUP BY Query1.CompDate, Query1.[Body No]
PIVOT Query1.RawMaterial;

which is give more details as the date of composition but when we RawMaterials as column heading so only code is appearing instead of shortname?

- how to use multiple fields in a cross tab query?

thanks