Merge two child fields as string

[JDCD201]

Hi all,
I am currently creating a database for lift inspections.
The output is a mail-merge report.
What I am trying to do is get the multiple Lift Name (child) and Floors Served (child) combined into a string so that each Lift Group (parent) inspection has only one row. This then means that each lift group inspection will be shown as one record in mail merge.

Current

Lift Group ID - Date of Inspection - Lift Name - Floors Served
34 - 14/01/14 - Lift 1 - G-1
34 - 14/01/14 - Lift 2 - 1-3
35 - 14/01/14 - Lift 3 - G-8
34 - 14/01/13 - Lift 1 - G-1
34 - 10/10/13 - Lift 2 - 1-3

Desired:

Lift Group ID - Date of Inspection - Floors Served
34 - 14/01/14 - Lift 1 G-13; Lift 2 1-3
35 - 14/01/14 - Lift 3 G-8
34 - 10/10/13 - Lift 1 G-13; Lift 2 1-3

I hope this is clear enough,
Thanks,
James

 

[pr2-eugin]

Hello, Welcome to AWF

See if Allen Browne's - Concatenate values from related records, might be of any help.

 

[JDCD201]

Thanks Paul,
I've been fiddling around with it for a while but I can't seem to get it to work. It keeps telling me that the field I select (the table) could refer to more than one listed in the FROM clause in my SQL statement... any ideas?
Thanks,
James

 

[pr2-eugin]

Show the SQL you have used.

 

[JDCD201]

Show the SQL you have used.

Floors Served: concatrelated([Lift Info Floors],[tb2lift],[Lift Group ID],[Lift Reference])

I have also tried:
Floors Served: concatrelated([tb2Lift]![Lift Info Floors],[tb2lift],[tb2Lift]![Lift Group ID])

Thanks,
James

 

[pr2-eugin]

Nope ! I want the SQL. The error it says is not shown in your last post..

Paste the SQL here !

 

[JDCD201]

Sorry paul!

SELECT tb1Lift_Group.[Lift Group ID], tb0Site.[Site Name], tb2LMA_Info_LiftGroup.[LMA Group Examined by], tb2LMA_Info_LiftGroup.[LMA Date of Examination], tb1Lift_Group.[Lift Group Reference], [tb1Lift_Group]![Group Rated Load (kg)] & "kg/" & [tb1Lift_Group]![Group Rated Load (persons)] & " persons" & " " & [tb1Lift_Group]![Unique Lift Rated Load] AS [Rated Load], [tb1Lift_Group]![Lift Group Rated Speed] & "m/s " & [tb1Lift_Group]![Unique Lift Rated Speed] AS [Rated Speed], tb1Lift_Group![Group Duty] & " " & tb1Lift_Group![Unique Lift Duty] AS Duty, [tb1Lift_Group]![Maker] & " " & [tb1Lift_Group]![Maker (Unique Lifts)] AS Maker, [tb1Lift_Group]![Controller Type] & " " & [tb1Lift_Group]![Unique Lift Controller] AS [Controller Type], [tb1Lift_Group]![Drive Type] & " " & [tb1Lift_Group]![Unique Lift Drive] AS [Drive Type], [tb1Lift_Group]![Lift Group Maintenance Visits per Annum] & " visits per year" AS [Maintenance visits per annum], [tb2LMA_Info_LiftGroup]![LMA Group Adherence to PPM Regime] & ". " & [tb2LMA_Info_LiftGroup]![LMA Group Adherence to PPM Regime Comment] AS PPM
FROM ((((tb0Site INNER JOIN tb1Lift_Group ON tb0Site.[Site ID] = tb1Lift_Group.[Site ID]) INNER JOIN tb2LMA_Info_LiftGroup ON tb1Lift_Group.[Lift Group ID] = tb2LMA_Info_LiftGroup.[Lift Group ID]) INNER JOIN tb2Lift ON tb1Lift_Group.[Lift Group ID] = tb2Lift.[Lift Group ID]) INNER JOIN tb2LMA_Info_Lift ON (tb2Lift.[Lift ID] = tb2LMA_Info_Lift.[Lift ID]) AND (tb2LMA_Info_LiftGroup.[LMA Date of Examination] = tb2LMA_Info_Lift.[LMA Date of Examination])) INNER JOIN [Lift Group Overall Score] ON tb1Lift_Group.[Lift Group ID] = [Lift Group Overall Score].[Lift Group ID]
GROUP BY tb1Lift_Group.[Lift Group ID], tb0Site.[Site Name], tb2LMA_Info_LiftGroup.[LMA Group Examined by], tb2LMA_Info_LiftGroup.[LMA Date of Examination], tb1Lift_Group.[Lift Group Reference], [tb1Lift_Group]![Group Rated Load (kg)] & "kg/" & [tb1Lift_Group]![Group Rated Load (persons)] & " persons" & " " & [tb1Lift_Group]![Unique Lift Rated Load], [tb1Lift_Group]![Lift Group Rated Speed] & "m/s " & [tb1Lift_Group]![Unique Lift Rated Speed], tb1Lift_Group![Group Duty] & " " & tb1Lift_Group![Unique Lift Duty], [tb1Lift_Group]![Maker] & " " & [tb1Lift_Group]![Maker (Unique Lifts)], [tb1Lift_Group]![Controller Type] & " " & [tb1Lift_Group]![Unique Lift Controller], [tb1Lift_Group]![Drive Type] & " " & [tb1Lift_Group]![Unique Lift Drive], [tb1Lift_Group]![Lift Group Maintenance Visits per Annum] & " visits per year", [tb2LMA_Info_LiftGroup]![LMA Group Adherence to PPM Regime] & ". " & [tb2LMA_Info_LiftGroup]![LMA Group Adherence to PPM Regime Comment], concatrelated([Lift Info Floors],[tb2Lift],[Lift Group ID],[Lift ID]);

 

[pr2-eugin]

Try this,

SELECT tb1Lift_Group.[Lift Group ID], tb0Site.[Site Name], tb2LMA_Info_LiftGroup.[LMA Group Examined by], tb2LMA_Info_LiftGroup.[LMA Date of Examination], tb1Lift_Group.[Lift Group Reference], [tb1Lift_Group].[Group Rated Load (kg)] & "kg/" & [tb1Lift_Group].[Group Rated Load (persons)] & " persons " & [tb1Lift_Group].[Unique Lift Rated Load] AS [Rated Load], [tb1Lift_Group].[Lift Group Rated Speed] & "m/s " & [tb1Lift_Group].[Unique Lift Rated Speed] AS [Rated Speed], tb1Lift_Group.[Group Duty] & " " & tb1Lift_Group.[Unique Lift Duty] AS Duty, [tb1Lift_Group].[Maker] & " " & [tb1Lift_Group].[Maker (Unique Lifts)] AS Maker, [tb1Lift_Group].[Controller Type] & " " & [tb1Lift_Group].[Unique Lift Controller] AS [Controller Type], [tb1Lift_Group].[Drive Type] & " " & [tb1Lift_Group].[Unique Lift Drive] AS [Drive Type], [tb1Lift_Group].[Lift Group Maintenance Visits per Annum] & " visits per year" AS [Maintenance visits per annum], [tb2LMA_Info_LiftGroup].[LMA Group Adherence to PPM Regime] & ". " & [tb2LMA_Info_LiftGroup].[LMA Group Adherence to PPM Regime Comment] AS PPM, concatrelated("[Lift Info Floors]", "[tb2lift]", "[Lift Group ID] = " & tb1Lift_Group.[Lift Group ID]) AS [Floors Served] 
FROM ((((tb0Site INNER JOIN tb1Lift_Group ON tb0Site.[Site ID] = tb1Lift_Group.[Site ID]) INNER JOIN tb2LMA_Info_LiftGroup ON tb1Lift_Group.[Lift Group ID] = tb2LMA_Info_LiftGroup.[Lift Group ID]) INNER JOIN tb2Lift ON tb1Lift_Group.[Lift Group ID] = tb2Lift.[Lift Group ID]) INNER JOIN tb2LMA_Info_Lift ON (tb2Lift.[Lift ID] = tb2LMA_Info_Lift.[Lift ID]) AND (tb2LMA_Info_LiftGroup.[LMA Date of Examination] = tb2LMA_Info_Lift.[LMA Date of Examination])) INNER JOIN [Lift Group Overall Score] ON tb1Lift_Group.[Lift Group ID] = [Lift Group Overall Score].[Lift Group ID]
GROUP BY tb1Lift_Group.[Lift Group ID], tb0Site.[Site Name], tb2LMA_Info_LiftGroup.[LMA Group Examined by], tb2LMA_Info_LiftGroup.[LMA Date of Examination], tb1Lift_Group.[Lift Group Reference], [tb1Lift_Group].[Group Rated Load (kg)] & "kg/" & [tb1Lift_Group].[Group Rated Load (persons)] & " persons" & " " & [tb1Lift_Group].[Unique Lift Rated Load], [tb1Lift_Group].[Lift Group Rated Speed] & "m/s " & [tb1Lift_Group].[Unique Lift Rated Speed], tb1Lift_Group![Group Duty] & " " & tb1Lift_Group![Unique Lift Duty], [tb1Lift_Group].[Maker] & " " & [tb1Lift_Group].[Maker (Unique Lifts)], [tb1Lift_Group].[Controller Type] & " " & [tb1Lift_Group].[Unique Lift Controller], [tb1Lift_Group].[Drive Type] & " " & [tb1Lift_Group].[Unique Lift Drive], [tb1Lift_Group].[Lift Group Maintenance Visits per Annum] & " visits per year", [tb2LMA_Info_LiftGroup].[LMA Group Adherence to PPM Regime] & ". " & [tb2LMA_Info_LiftGroup].[LMA Group Adherence to PPM Regime Comment], concatrelated("[Lift Info Floors]", "[tb2lift]", "[Lift Group ID] = " & tb1Lift_Group.[Lift Group ID]);

 

[JDCD201]

Thank you so much paul! That worked perfectly! Just out of curiosity how could I change it to it showed Lift Reference, Floor Served; Lift Reference 2, Floors Served

At the moment it is showing Floors Served, Floors served but it would be awesome if i could show the lift and then floors that lift serves

I've removed the other fields for the moment as I can come back to them as they are simple:

SELECT tb1Lift_Group.[Lift Group ID], tb0Site.[Site Name], tb2LMA_Info_LiftGroup.[LMA Group Examined by], tb2LMA_Info_LiftGroup.[LMA Date of Examination], tb1Lift_Group.[Lift Group Reference], concatrelated("[Lift Info Floors]","[tb2lift]","[Lift Group ID] = " & tb1Lift_Group.[Lift Group ID]) AS [Floors Served]
FROM ((((tb0Site INNER JOIN tb1Lift_Group ON tb0Site.[Site ID] = tb1Lift_Group.[Site ID]) INNER JOIN tb2LMA_Info_LiftGroup ON tb1Lift_Group.[Lift Group ID] = tb2LMA_Info_LiftGroup.[Lift Group ID]) INNER JOIN tb2Lift ON tb1Lift_Group.[Lift Group ID] = tb2Lift.[Lift Group ID]) INNER JOIN tb2LMA_Info_Lift ON (tb2LMA_Info_LiftGroup.[LMA Date of Examination] = tb2LMA_Info_Lift.[LMA Date of Examination]) AND (tb2Lift.[Lift ID] = tb2LMA_Info_Lift.[Lift ID])) INNER JOIN [Lift Group Overall Score] ON tb1Lift_Group.[Lift Group ID] = [Lift Group Overall Score].[Lift Group ID]
GROUP BY tb1Lift_Group.[Lift Group ID], tb0Site.[Site Name], tb2LMA_Info_LiftGroup.[LMA Group Examined by], tb2LMA_Info_LiftGroup.[LM Date of Examination], tb1Lift_Group.[Lift Group Reference];

 

[JDCD201]

When merging my query with word an error message pops up

Error has occured: "Undefined function 'concatrelated' in expression.

Does anybody know how to solve this?

Thanks,
James

 

[pr2-eugin]

Did you copy the CODE from Allen Browne's website (http://allenbrowne.com/func-concat.html) into a New module and named it anything other than concatRelated?

(OR)

Did you paste the code into existing module that is not named concatRelated

Did you compile the code?

 

[JDCD201]

Hi Paul, I removed the function and re-added it again just to check and saved it as ConcatRelated.

I then couldn't use it and whilst researching found that if the module and function are saved under the same name it confuses Access so it is now saved as Module 1.

The previous error message would come up when I added the concatrelated field to a query that was already being used in a mail merge.

When I try and create a new merge document the query with concatrelated fields doesn't actually come up as an option. I'm guessing because word doesn't understand the function. Do I need to add the function to word somehow like I did access? MS Excel understands it if i send the query to that.

Thanks,
James

 

[pr2-eugin]

Name the module where the CODE sits as nameMeDifferent if there is another module called concatRelated delete it.

 

[JDCD201]

Right done that Paul.

When I go to export the query to word this pops up:

ODBC Microsoft Access Driver Login Failed:
Could not find file 'C:Users\James\Desktop\.mdb'.

* I Click ok* then this pops up:

Login
Authorization: Admin
Password:

I haven't set a password or login up for this? Is there a default password?

The alternative way is I try and set up the merge from within word using the wizard and when I go to select recipients and select the database all of the tables and queries show up apart from the one with the concatrelated fields.

Sorry for being a pain Paul!
Thanks,
James

 

[pr2-eugin]

There is no file name? 'C:Users\James\Desktop\<fileNameHERE>.mdb'? You nowhere mentioned anything about merge with word. Anyway, check the file issue first, then check if the field has been declared/assigned first.