Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

1 to Many Relationship - Create New table merging contents

Status
Not open for further replies.

scubadunc

IS-IT--Management
Oct 28, 2009
12
AU
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?
 
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]
 
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
 
Yes, I have copy and pasted the entire code into a new std module.
 
That was it! Now works perfectly

Thank you very much for you help
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top