Showing rows of many side table in columns side by side (1 Viewer)

[deletedT]

@MajP @plog & @CJ_London helped me with a crosstab query for printing a report in this post.
I still have a problem with printing the result. To keep what I know and what I need to learn tidy, I started a new thread.
A sample database is attached if you need to have a look at the situation.

This is the structure of a crosstab query.

and this is its sql:

TRANSFORM First(tblOrderProgress.RegisteredOn) AS FirstOfRegisteredOn

SELECT tblOrders.OrderPK
    ,tblParts.Part
FROM tblParts
RIGHT JOIN (
    tblOrders LEFT JOIN tblOrderProgress ON tblOrders.OrderPK = tblOrderProgress.OrderFK
    ) ON tblParts.PartPK = tblOrders.PartFK
GROUP BY tblOrders.OrderPK
    ,tblParts.Part
PIVOT "Process " & [ProcessFK];

This is the content of tblOrderProgress:

I use above query to print a report. When I filter the query OrderFK=1, query's result is:

the query shows process1, process2 & process3. The report prints without any problem.
But when I filter the query OrderFK=3, the query's result is:

Because the source query has only Process1 field, the report doesn't open because it can't find the Control Source of two text boxes.



Now my question:
How can I make the query always show Process1, Process2, Process3 fields. No matter if all three processes are registered in tblOrderProgress or not.


Any kind of advice is a great help.
Thanks.

 

[pbaldy]

See if Allen can help:

Microsoft Access tips: Crosstab query tips

Crosstab query tips for Microsoft Access

allenbrowne.com

 

[MajP]

In Crosstabs you only get a column if values exist for that "column". Is there a more Processes for part 2? You can force blank column by specifying columns you would want to see if you know that ahead of time. If there can be up to three processes than you should be able to specify mandatory columns: "Process 1", "Process 2", "Process 3"

1. In query design view, show the Properties box (View menu.)
2. Locate the Column Headings property. (If you don't see it, you are looking at the properties of a field instead of the properties of the query.)
3. Type in all the possible values, separated by commas. Delimit text values with quotes, or date values with #.

 

[deletedT]

Million thanks.
Works perfect.
I simply don't know how to thank you.

 

[MajP]

No problem, glad it worked.