How to make column data into column headers

[Aileen Baylon]

I have data from 3 different tables

• table1
• table2
• table3

I currently have a data that looks like this:
TABLE 1

Id | Name | Order
1 | Buchanan | 001
2 | Buchanan | 002
3 | Buchanan | 003
4 | Radhay| 001
5 | Radhay| 002
6 | Reyes| 001
7 | Reyes| 002
8 | Reyes| 003

TABLE 2
1 | 001| Beverage
2 | 002| Condiment
3 | 003| Dessert

TABLE 3
1 | ID001| Buchanan
2 | ID002| Radhay
3 | ID003| Reyes



I want to have some sort of crosstab query that would output my data into this:


ID | Name | Beverage | Condiment | Dessert
ID001 | Buchanan | Yes | Yes | Yes
ID002 | Radhay | Yes | Yes | No
ID003 | Reyes | Yes | Yes | Yes

Can anyone help me create a sql query for this?

[Rabbit]

Look up the syntax for transform pivot by.

[Aileen Baylon]

But what type of expression do I use to calculate summary values after the word TRANSFORM in the SQL statement?

Since I'm not actually checking for SUM, or MAX...

[ck9663]

There are a number of samples, here...

Good Luck!!!

~~ CK

[Aileen Baylon]

Thanks CK... but that's a lot of articles I need to go thru.

I just need to know what values I need to put in the
TRANSFORM
SELECT
FROM
GROUP BY
PIVOT

SQL query...

[Rabbit]

I would use a count. Then you would know how many of each item they ordered. And a count of zero or a null would mean they didn't order any.

[Aileen Baylon]

Sorry, fairly new developer here so I'm having trouble creating the SQL...

[Rabbit]

The transform would be the aggregate function, group by the row fields, and pivot the column groupings.

[Aileen Baylon]

I'm having problems joining tables. Access said SYNTAX ERROR IN JOIN OPERATION.

TRANSFORM Count(t2.col3) AS Count
SELECT t1.ID, t1.Name, count(t2.col3) AS [Total ]
FROM t1, t2, t3
WHERE t1.Order = t2.col2 AND t1.Name = t3.col3
GROUP BY t1.ID, t1.Name, t2.col3
PIVOT t2.col3

[Rabbit]

Try using an inner join instead of the cross join.