1 to Many Relationship - Create New table merging contents

[scubadunc]

Hi there

I have a 1 to Many relationship between 2 tables where the primary key is a combination of the surname and address. So there is multiple people (parents, children etc) at the same address in my detail table.

person1 129kensingtonst
person2 129kensingtonst
person1 30clasibrookrd
person2 30clasibrookrd

I need to be able to output to a new table (for exporting purposes) where it merges all the information into a single record. ie.

surname address person1 person2 person3

What is the best way to do this?

 

[dhookom]

Does this faq701-4233 work for you?

Duane
Hook'D on Access
MS Access MVP

 

[scubadunc]

I have tried the code you suggested, and changed the SQL to the appropriate field/table names, however I get the following error when trying to run the query

Undefined function 'Concatenate' in expression

My SQL looks like this:

SELECT AddressComma,
Concatenate("SELECT MailNameJoint FROM sheet1
WHERE AddressComma =""" & [AddressComma] & """") as MailNameJoints
FROM [pivot]

 

[dhookom]

Did you copy the function into a standard module? Concatenate() is not a built-in function in VBA. You must create a new (or existing module) and paste in the function.

Duane
Hook'D on Access
MS Access MVP

 

[scubadunc]

Yes, I have copy and pasted the entire code into a new std module.

 

[dhookom]

Did you name the module something like "modStringFunctions"? You can't name a module the same as the function.

Duane
Hook'D on Access
MS Access MVP

 

[scubadunc]

That was it! Now works perfectly

Thank you very much for you help

 

[dhookom]

scubadunc,
Your issue was one of the reasons why many of us old-timers stick to a naming convention. I try to begin all of my module names with either "bas..." or "mod...". This saves me from duplicate procedure and module names.

Duane
Hook'D on Access
MS Access MVP