Solved

Denormalizing data

Posted on 2014-02-19
5
133 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses

732 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