View Single Post
Old 08-15-2019, 10:22 AM   #7
Pat Hartman
Super Moderator
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,163
Thanks: 15
Thanked 1,576 Times in 1,498 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