crosstab query question

[Becky]

I am very new to this, but I would like to ask for some help. I have
struggled to get the sql code for a crosstab querie (my 1st!), but I just
cannot seem to get it right. This example is simpler and shorter than my
actual one, but I'll get the right idea from it.

I start with a querie like this;

VolID LastName FirstName Tool
123 Smith Sue Hammer
124 Jones Molly Drill
123 Smith Sue Screwdriver
126 Nadeem John Screwdriver
124 Jones Molly Drill
123 Smith Sue Hammer
126 Nadeem John Drill
123 Smith Sue Hammer
124 Jones Molly Hammer


I want to end up with the results shown like this, in order of VolID
increasing;

VolID LastName FirstName Hammer Drill
Screwdriver
123 Smith Sue 3 0
1
124 Jones Molly 1 2
0
126 Nadeem John 0 1
1

Thank you to anyone who can spell out the sql to me. I will then apply it
to my actual case.

Cheers, Becky

 

[trevorC via AccessMonster.com]

created from query wizard
change table2 to your table name.

TRANSFORM Count(Table2.ID) AS CountOfID
SELECT Table2.VolID, Table2.FirstName, Table2.LastName, Count(Table2.ID) AS
[Total Of ID]
FROM Table2
GROUP BY Table2.VolID, Table2.FirstName, Table2.LastName
PIVOT Table2.Tool;

 

[PieterLinden via AccessMonster.com]

I am very new to this, but I would like to ask for some help. I have
struggled to get the sql code for a crosstab querie (my 1st!), but I just
cannot seem to get it right. This example is simpler and shorter than my
actual one, but I'll get the right idea from it.

I start with a querie like this;

VolID LastName FirstName Tool
123 Smith Sue Hammer
124 Jones Molly Drill
123 Smith Sue Screwdriver
126 Nadeem John Screwdriver
124 Jones Molly Drill
123 Smith Sue Hammer
126 Nadeem John Drill
123 Smith Sue Hammer
124 Jones Molly Hammer


I want to end up with the results shown like this, in order of VolID
increasing;

VolID LastName FirstName Hammer Drill
Screwdriver
123 Smith Sue 3 0
1
124 Jones Molly 1 2
0
126 Nadeem John 0 1
1

Thank you to anyone who can spell out the sql to me. I will then apply it
to my actual case.

Cheers, Becky

Becky,
if you run the query wizard, you can choose Crosstab, and it walks you
through it. (Well, in 2003 it does... )

TRANSFORM Count(txt_ToolDataForXTB.VolID) AS CountOfVolID
SELECT txt_ToolDataForXTB.VolID, txt_ToolDataForXTB.FirstName,
txt_ToolDataForXTB.LastName
FROM txt_ToolDataForXTB
GROUP BY txt_ToolDataForXTB.VolID, txt_ToolDataForXTB.FirstName,
txt_ToolDataForXTB.LastName
PIVOT txt_ToolDataForXTB.Tool;

Change "txt_ToolDataForXTB" to the name of the query/table you want to base
the crosstab query on.

 

[Becky]

Trevor & Pieter - that is a big help for this newbie! Becky