?
Solved

Denormalizing data

Posted on 2014-02-19
5
Medium Priority
?
135 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 2000 total points
ID: 39871445
You need to pass the WHERE argument:

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

Jim.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

800 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