Showing rows of many side table in columns side by side.

[deletedT]

For the past few hours I've been trying to add a report to print one of our customer's requests, but I'm hitting the wall.
I hope somebody can help with a query to be used as the record source for a report.

This is the structure of a query:

and this is the result:

It shows all records of tblOrderedProgress where ProcessFk=1
Now they want another two column to be added to this query (report) that shows registered date for ProcessFK=2 And ProcessFK=3

something like this:

Part	RegisteredOn For Process 1	RegisteredOn For Process 2	RegisteredOn For Process 3
part1	2020/02/04	2020/02/04	2020/02/04
part3	2020/02/08	2020/02/04	2020/02/04
part2	2020/01/04	2020/02/04	2020/02/04
part5	2020/01/04	2020/02/04	2020/02/04

They need to have a printed list that shows all the orders and the date for 3 processes side by side.
Is it possible at all?
A sample database is attached.

Any kind of hint/advice is much appreciated.

 

[MajP]

See if this is correct

 

[MajP]

Need a crosstab query, but you need to modify query 1 first.

 

[deletedT]

See if this is correct

Thanks for trying to help. But no. the result is not what I expect.
tblOrders has 5 records. your query shows only 4 of them.
I need all records from tblOrdes that shows dates for porcess1, process2 & process3 as three column in front of them.

Sorry if I wasn't clear with my question.

 

[plog]

tblOrders has 5 records. your query shows only 4 of them.

You do not want a report of orders, you want a report of parts. tblOrders only has records for 4 parts. MajP's Query1_Crosstab is the solution you want.

I need all records from tblOrdes that shows dates

If that is the case, please show us exactly what you expect the report to look like using the data you provided.

 

[MajP]

Your example only shows 4 parts as well. Not sure I understand. Part 4 has no processes, so are you asking for an empty row?

 

[deletedT]

Sorry for the confusion.

Let me clarify the situation.

1. I need all records from tblOrdes that shows dates for porcess1, process2 & process3 as three column in front of them.
2. They want to have a report of current orders and the date for process1 to 3 for each order.

My sample database has 5 records in tblOrders. So I need the report show 5 records of tblOrders and the registered date for process1,2 & 3 for each row of tblOrders.

I was using a remote control software to access my pc while I was in train. That was why above example shows only 4 rows.
It’s 2:45 AM and I got home just now. I will post a screen shot of what I expect to have as soon as I’m back to office. (4 hours from now)

Thanks for your time

 

[deletedT]

Part	Process 1	Process 2	process 3
Part1	2020/02/07​	2020/02/04​	2020/01/31​
Part3	2020/02/08​	2020/02/08​	2020/02/08​
Part2	2020/02/03​	2020/01/04​	2020/01/31​
Part5	2020/03/03​	2020/02/04​	2020/01/31​
Part5	2020/01/03​	2020/01/04​	2020/04/01​

I couldn’t go to bed. Using a remote access, I was able to post it.
Above result is what I expect to see. Of course adding new orders to tblOrders should update this query.
This query doesn’t need to be updateable. It’ll be used as a record source for a report.

Thanks.

 

[MajP]

Need to left join on ordersProcess and include this in Xtab

 

[CJ_London]

Is this what you require?

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];

note, you can ignore the OrderPK in your report

 

[deletedT]

@MajP @CJ_London
I appreciate your help. Both are exactly what I was looking for.
During my several years of experience with Access, it's the first time I need to use a crosstab query.
The above sample was a simplified version, and in the real database I have to show much more fields.
Before asking stupid questions, I'll try to master crosstab query. I'll post back if I have more questions regarding your solutions.

Million thanks for your time and sharing your experience.

Regards.

 

[plog]

I'll try to master crosstab query

That would make you the first master of them. In general they are hacks and anything more complicated than what was solved in this thread is better developed in an Excel pivot table. The biggest limitation is that you can only convert one field's values to column headings. You can't do sub-headings or multiple fields into columns. If that's what's been requested of you, then I'd turn to Excel pivot tables.

 

[CJ_London]

n the real database I have to show much more fields good luck, as Plog says, you can only convert one fields values to column headings so if you need more, an Excel pivot may be the way to go. It can be done in Access per below but perhaps more of a faff.

you can easily create a total column (assuming you are summing) by repeating the value column and assigning it to the the row header.

If you need more than one then you can do multiple crosstabs and join them on the PK. You can also use union queries with a calculated 'column header'.

you can't do subheadings as such but that can be handled in a form or report with a bit of vba if the 'rules' can be managed. Or union to a simple 'header' query.

One thing to watch out for is if your query uses parameters, those parameters need to be declared.