Solved

Merging data into single record

Posted on 2014-10-08
9
158 Views
Last Modified: 2014-10-08
I have data that looks like this:
id   name_type   name_value
1    first                bob
1    middle          joe
1    last                smith

I want it to look like this:
id   first    middle   last
1    bob    joe          smith

I can't do it with crosstab since you can't aggregate strings.

I created a query that does this:
id   first    middle   last
1    bob    [null]      [null]
1    [null]  joe         [null]
1    [null]  [null]     smith

thinking I could use group by to coalesce them into a single record, but that didn't work.

Not sure what to do unless I try it with Domain functions,
0
Comment
Question by:shacho
9 Comments
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40367913
Can you post your code? Maybe it only needs a small change.
0
 

Author Comment

by:shacho
ID: 40367975
That could take a while.  It's probably faster to just write some SQL based on the mock table above.
0
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40367976
Please check if the following command works:
TRANSFORM First(name_value) AS name_value
 SELECT ID
 FROM YourTableName
 GROUP BY ID
 PIVOT name_type

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:shacho
ID: 40368001
Before I try the transform, is there a way to do it based on the intermediate data I created (with the nulls mixed in)?  That would be easier to implement.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40368011
I suggest you to drop that intermediate query since will only make things harder.
Anyway I can only give you an assertive answer if you post here the query that returns that result.
0
 
LVL 57
ID: 40368034
Have a look at the following EE article on DConcat(), a custom function you can use to flatten and concatenate  data:

http://www.experts-exchange.com/Database/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Jim.
0
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40368064
Select Distinct id, STUFF((Select ' '+name_value From tablename p1 Where p2.id=P1.id
For XML PATH('')),1,1,'') as [First Middle Last] from tablename p2
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40368113
0
 

Author Comment

by:shacho
ID: 40368118
Thanks all for your comments.

Vitor - You were right.  Your SQL can be adapted perfectly for this.  "First" was the missing piece!

Thanks,

Mike
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What's wrong with CopyFromRecordset 6 33
Modal form 11 29
Access Crosstab Query with Multiple Values 4 32
MS Access 2010 Close Form  Event - Stop Form Closing 4 27
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question