Create the query, use report wizard to create the report the way you want it.
Create the query, use report wizard to create the report the way you want it.
The other reason it needs to be in VBA is because each cable has a type, like Controls, Power, or Instrumentation. The Cable table lets the user assign a purpose, and there's another table called CableType which equates each cable type with a code. So supposing I want to put cables 001, 002, and 003 which are for Controls, Power, and Instrumentation respectively into conduit C034 which goes from point A to point B, 1 row in my final report would look like this:
Conduit From To Contents C034 Point A Point B 001C, 002P, 003I
OK. Will there always be three, or up to three, cables for each conduit? Or is it a unknown number?
The number of cables in each conduit is variable. It needs to be determined by the number of rows in the CableRoute table that point to that particular conduit. You also might have some conduits that are empty - they are spares for later use.
Yes then you will have to use VBA to put all those records in one string like you have it above. Either you can call the function from the query or you can do it in the OnLoad of the report. You will read thru all the cable records and put them into one long string for each conduit. Create a query with all other fields you need and then this one field will be added, so that your record source will be SELECT Query1.*,'" & contents & "' AS Contents FROM Query1;