Combining multiple sub-records against each row in a report

[HairyArse]

I've built a system to log faults against products.

Each product can have 5 types of fault. Fault1, Fault2, Fault3, Fault4, Fault5.
Each product can have multiple instances of each fault type and for each fault logged, the metrage is also logged.

For instance:

Product 1 can have a Fault1 at 24 metres, at 34 metres and at 40 metres.
It could also have a Fault2 at 14 metres and at 20 metres.
It could have no Fault 3s.
It could have no Fault 4s.
It could have a Fault 5 at 1 metre.

A product can have any number of any of the five fault types.

The structure and DB layout is sound and the faults are logged and the system all works fine. I display the faults to a PHP report just fine I'm struggling to build a report in access to show these in a presentable manner.

I want to display something like this:

Product 1 | Fault1 24, Fault1 34, Fault1 40 | Fault2 14, Fault2 20 | | | Fault5 1

How can I combine the multiple rows of each fault type into one single value for each row? I can do this very easily in PHP but I'm struggling to display it correctly in an Access Report.

My table structure is as follows:

 

[HairyArse]

Fault3 doesn't need a type. That's not a mistake.

 

[arnelgp]

you can have 1 faults table instead of 5.
just add one column and input the 1,2 3,4 or 5.

any way, what does 24 means in Fault1 24? is it the count?

select T2.product.piece_id,  
(select count("1") from fault1Table As T1 where T1.piece_id=T2.piece_id) As Fault1, 
(select count("1") from fault2Table As T2 where T1.piece_id=T2.piece_id) As Fault2, 
...
...
From productTable As T2;

 

[HairyArse]

It's too late to change the structure of the tables. Faults continue to be logged and I have a whole separate PHP/MYSQL/HTML data input system setup for use on our tablets.

Fault1 means a fault within category type 1.
A Category type 1 fault can be a knot, a slub, a spot or a stain.
Category type 5 can be a stop mark, a mispick, a seam, a splice.

These fault types are pre-determined by the customer.

The 24 indicates the metrage.

So a product might have the following type 1 faults:
A knot at 24 metres, A slub at 30 Metres, Another Knot at 34 Metres.

Ultimately I want the report to look like this:

 

[HairyArse]

As the three fault1 types are individual records in the fault1 table, the difficulty comes in combining all of these into a single string to display against each piece.

In PHP/MYSQL I just run an separate query in each 'cell', but I don't know how I'm able to do this in an Access report short of creating a subreport in each cell.

 

[HairyArse]

OK I've come up with a solution. Might not be the most elegant but will help me get this job.

I've added 5 new fields to the products table - fault1_combined, fault2_combined etc.

Then I've run separate queries against each record in the pieces table, concatenated the values and then inserted them into the new fields.

My access report then becomes quite simple.