Query to get linked table data in columns not rows (1 Viewer)

[AnilBagga]

I have 3 linked tables in a Query as shown in the db enclosed.

The screen shot of the query is as below. Is it possible to. get the linked tables data in columns and rows. I would like to have one row for each record in tblFabricCodeMaster_New and the data of Tape internal code in different columns in the same row, instead of different rows

what type of query does one need. I am at a loss on what to look for in the forum

 

[theDBguy]

Take a look at crosstab queries. Not saying it's what you need, but maybe.

 

[June7]

Edit question to show how you expect the output to look. For instance, F001 has two 001 TapeIntCode values - should each show in their own column?

A CROSSTAB will be difficult because 3 fields are required. Third field can be calculated with DCount() and this will be the column header, however,
requires a unique identifier field in the query. I thought FabRecipeID would serve but including it results in rows without data in FabRecipeID because of LEFT JOIN between tblFabricCodeMaster_New and tblFabricTapeCons_New. Changing to RIGHT JOIN still results in other empty fields. Why are there records in tblFabricTapeCons_New without FabCodeID or TapeCodeID? Why does record 869 have a 0 TapeCodeID?

For an idea of what could be possible, change qryFabricTapeConstruction to:
SELECT tblFabricTapeCons_New.FabRecipeID, tblFabricCodeMaster_New.FabricCode, tblTapeCodeMaster.TapeIntCode
FROM (tblFabricCodeMaster_New RIGHT JOIN tblFabricTapeCons_New ON tblFabricCodeMaster_New.FabCodeID = tblFabricTapeCons_New.FabCodeID) LEFT JOIN tblTapeCodeMaster ON tblFabricTapeCons_New.TapeCodeID = tblTapeCodeMaster.ID;

Then build query:

TRANSFORM First(qryFabricTapeConstruction.TapeIntCode) AS FirstOfTapeIntCode
SELECT qryFabricTapeConstruction.FabricCode
FROM qryFabricTapeConstruction
GROUP BY qryFabricTapeConstruction.FabricCode
PIVOT DCount("*","qryFabricTapeConstruction","FabricCode='" & [FabricCode] & "' AND FabRecipeID<" & [FabRecipeID])+1;

Otherwise, consider this alternative http://allenbrowne.com/func-concat.html

 

[AnilBagga]

Edit question to show how you expect the output to look. For instance, F001 has two 001 TapeIntCode values - should each show in their own column?

A CROSSTAB will be difficult because 3 fields are required. Third field can be calculated with DCount() and this will be the column header, however,
requires a unique identifier field in the query. I thought FabRecipeID would serve but including it results in rows without data in FabRecipeID because of LEFT JOIN between tblFabricCodeMaster_New and tblFabricTapeCons_New. Changing to RIGHT JOIN still results in other empty fields. Why are there records in tblFabricTapeCons_New without FabCodeID or TapeCodeID? Why does record 869 have a 0 TapeCodeID?

For an idea of what could be possible, change qryFabricTapeConstruction to:
SELECT tblFabricTapeCons_New.FabRecipeID, tblFabricCodeMaster_New.FabricCode, tblTapeCodeMaster.TapeIntCode
FROM (tblFabricCodeMaster_New RIGHT JOIN tblFabricTapeCons_New ON tblFabricCodeMaster_New.FabCodeID = tblFabricTapeCons_New.FabCodeID) LEFT JOIN tblTapeCodeMaster ON tblFabricTapeCons_New.TapeCodeID = tblTapeCodeMaster.ID;

Then build query:

TRANSFORM First(qryFabricTapeConstruction.TapeIntCode) AS FirstOfTapeIntCode
SELECT qryFabricTapeConstruction.FabricCode
FROM qryFabricTapeConstruction
GROUP BY qryFabricTapeConstruction.FabricCode
PIVOT DCount("*","qryFabricTapeConstruction","FabricCode='" & [FabricCode] & "' AND FabRecipeID<" & [FabRecipeID])+1;

Otherwise, consider this alternative http://allenbrowne.com/func-concat.html

Thank you June7. This looks a bit tough for my level of Access knowledge. Will copy paste this and revert

 

[AnilBagga]

Thank you June7. This looks a bit tough for my level of Access knowledge. Will copy paste this and revert

The output desired is shown in the Excel file enclosed.