Solved

Denormalizing data

Posted on 2014-02-19
5
134 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 58
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 58

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

728 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