Combining multiple sub-records against each row in a report (1 Viewer)

HairyArse

Registered User.
Local time
Today, 17:40
Joined
Mar 31, 2005
Messages
92
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

Registered User.
Local time
Today, 17:40
Joined
Mar 31, 2005
Messages
92
Fault3 doesn't need a type. That's not a mistake.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:40
Joined
May 7, 2009
Messages
19,169
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?
Code:
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

Registered User.
Local time
Today, 17:40
Joined
Mar 31, 2005
Messages
92
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

Registered User.
Local time
Today, 17:40
Joined
Mar 31, 2005
Messages
92
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

Registered User.
Local time
Today, 17:40
Joined
Mar 31, 2005
Messages
92
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:40
Joined
Feb 19, 2002
Messages
42,971
WRONG solution.

As arne pointed out, the underlying problem is the design flaw. You can change it sooner rather than later and the sooner you fix it the less impact the fix will have. However, to solve your immediate problem, you can use a Union query to select data from each of the five tables and get it into a single recordset. Union queries MUST have IDENTICAL column structures so for the tables that do not have a type field, you will need to include a calculated column as a place holder. You will probably also want to include an indication of which table the fault came from so the query will look something like:

Select "Type 1", as tblName, fault_ID, piece_ID, fault1_type, fault1_metres from tbl_canada_Inspection_1
Union Select "Type 2", as tblName, fault_ID, piece_ID, fault2_type, fault2_metres from tbl_canada_Inspection_2
Union "Type 3", as tblName, fault_ID, piece_ID, Null As fault3_type, fault3_metres from tbl_canada_Inspection_3
Union ...

Then create a subreport and bind it to this query.

You might want to do some studying in the database design area so you have an understanding of normalization. Having a properly normalized schema makes life so much easier in the long run. I don't know if there is even a remote possibility of a fault type 6 rearing its ugly head but if it does, think about the monumental changes required to add it whereas if your database were properly normalized, it would simply be adding a new fault type name to tblFaultCategories and then just using all your existing forms/reports/queries to add/change/view data in the same table. No structural changes would be required.
 
Last edited:

Users who are viewing this thread

Top Bottom