many to one in one row

  • Thread starter Thread starter JMosey
  • Start date Start date
J

JMosey

The tables:

condiment
CondimentID (int)
Contiment (text)
active (yes/no)

condimentsite
condimentsiteID (int)
condimentID (int)
siteID (int)

Site
SiteId (int)
Site (text)

When I do inner joins obviousy I get:
CondimentID Condiment SiteID
1 Mustard 1
1 Mustard 2
1 Mustard 3
2 Ketchup 2
3 Salt 1
3 Salt 2
4 Pepper 2
4 Pepper 3

What I'd like is this:

CondimentID Condiment SiteID SiteID SiteID
1 Mustard 1 2 3
2 Ketchup 2
3 Salt 1 2
4 Pepper 2 3

I'd also be happy if SiteId was one field with a comma delimited list
(i.e. 1,2,3)

Suggestions?
 
Create a crosstab query, where:
CondimentID is a row heading;
Condiment is a row heading;
SiteID is the Column Heading;
SiteID is the Value (use First in the Group By row.)

Note that a crosstab is not editable.
 
Back
Top