Re: Combining multiple sub-records against each row in a report
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
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.