Solved

Denormalizing data

Posted on 2014-02-19
5
131 Views
Last Modified: 2014-05-15
I have the gollowing table

TEAM      |   MEMBER

Team1    |   Member1
Team1    |   Member2
Team1    |   Member3

I would like to have one row per team as follows:

Team1  |    Member1   |    Member2   |    Member3   |

How do I do that in MS Access Query Design?
0
Comment
Question by:fitaliano
  • 3
  • 2
5 Comments
 
LVL 57
ID: 39871105
0
 
LVL 57
ID: 39871119
and just to add a bit, subqueries are the only way to do it without writing a function and that is not a very elegant way of doing this.

You also could write records out to a temp table and fill columns sequentially, but that's not a good way to do it either really.

The DConact() function in Patrick's article is the simplest method that I've seen.

Jim.
0
 

Author Comment

by:fitaliano
ID: 39871150
Thank you Jim, what syntax should I use? DConcat([MEMBER]) doesn't seem to work
0
 

Author Comment

by:fitaliano
ID: 39871273
Jim I  tried

MEMBER: DConcat("MEMBER","TEST")

and I get ALL the other members in one column, which is not what I want. I only want the members belonging to a Team

+ I need them in different columns not one.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39871445
You need to pass the WHERE argument:

MemberList:DConcat("[Member]","TEST","[Team] = '" & [Team] & "'")

Jim.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

838 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