Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2019, 02:52 AM   #1
HairyArse
Newly Registered User
 
Join Date: Mar 2005
Posts: 83
Thanks: 5
Thanked 0 Times in 0 Posts
HairyArse is on a distinguished road
Combining multiple sub-records against each row in a report

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 is offline   Reply With Quote
Old 08-15-2019, 02:53 AM   #2
HairyArse
Newly Registered User
 
Join Date: Mar 2005
Posts: 83
Thanks: 5
Thanked 0 Times in 0 Posts
HairyArse is on a distinguished road
Re: Combining multiple sub-records against each row in a report

Fault3 doesn't need a type. That's not a mistake.
HairyArse is offline   Reply With Quote
Old 08-15-2019, 03:11 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,931
Thanks: 64
Thanked 2,526 Times in 2,426 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Combining multiple sub-records against each row in a report

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;

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-15-2019, 03:21 AM   #4
HairyArse
Newly Registered User
 
Join Date: Mar 2005
Posts: 83
Thanks: 5
Thanked 0 Times in 0 Posts
HairyArse is on a distinguished road
Re: Combining multiple sub-records against each row in a report

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 is offline   Reply With Quote
Old 08-15-2019, 03:24 AM   #5
HairyArse
Newly Registered User
 
Join Date: Mar 2005
Posts: 83
Thanks: 5
Thanked 0 Times in 0 Posts
HairyArse is on a distinguished road
Re: Combining multiple sub-records against each row in a report

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 is offline   Reply With Quote
Old 08-15-2019, 05:06 AM   #6
HairyArse
Newly Registered User
 
Join Date: Mar 2005
Posts: 83
Thanks: 5
Thanked 0 Times in 0 Posts
HairyArse is on a distinguished road
Re: Combining multiple sub-records against each row in a report

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.
HairyArse is offline   Reply With Quote
Old 08-15-2019, 10:22 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,946
Thanks: 13
Thanked 1,531 Times in 1,457 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Combining multiple sub-records against each row in a report

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.


__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 08-15-2019 at 10:28 AM.
Pat Hartman is offline   Reply With Quote
Reply

Tags
multiple rows

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining Multiple Records into one field MFlood7356 Queries 1 06-30-2011 07:36 AM
Combining multiple records into one RobinR Queries 5 05-09-2010 10:54 PM
Combining Multiple Records Dumas.DED Queries 4 05-06-2010 11:35 AM
combining multiple records to 1 moernoo Tables 2 11-09-2008 01:44 AM
Combining multiple tables with multiple records biggcc Queries 3 07-17-2007 12:39 PM




All times are GMT -8. The time now is 03:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World