Calculating percentages from counts in a crosstab query

[luvarchaeology via AccessMonster.com]

I'm brand new to acess and have run into a problem. I reviewed previous
queries on this subject but could not find a solution to my exact problem or
at least one easy enough to understand based on my limited access skills. I
have a crosstab query that returns counts of flake type by flake material. I
have pasted the SQL below to make it easier to understand. My question is how
do I create a Percentage row heading that will return percents of the total
counts. I know I can create percents in a report but I want to keep it in the
query without having to create a seperate report.

TRANSFORM Count(Flakes.Site_No) AS CountOfSite_No
SELECT Flakes.Material, Count(Flakes.Site_No) AS [Total of Site_No]
FROM Flakes
WHERE (((Flakes.Site_No) Like "05-001"))
GROUP BY Flakes.Material
PIVOT Flakes.Type;

I hope this makes sense. I'm sure there is a very easy solution but I've only
been using access for two weeks now and what I've learned I've gathered from
the help menu, so the least complicated soultion is the best. Thanks in
advance!

 

[Duane Hookom]

Can you create a query based on your crosstab query? Also, consider changing
your " Like ..." to " = ..."

Also if you know all possible Type values, you should add them in to the
column headings property.

 

[luvarchaeology via AccessMonster.com]

Thanks for responding so quickly! I guess I need to clarify, I was wondering
if there was a way to create the percentage row heading so that it appeared
right next to the Total Site_No row heading, all within this one crosstab
query. If I can't do that then how do I create a query for percents based off
the crosstab query as you suggested?

Also the type values are: Proximal, Shatter, Whole Flake, and Medial-Distal;
how do I add those to the column headings property. This is probably very
basic, but I am trying to learn my way around the program so what is the
purpose of adding all the possible type values to the column headings
porperty?

 

[luvarchaeology via AccessMonster.com]

Its baby steps, but I just figured out how to add to the columns heading
property. I'm still not understanding why though. Is it simply for
clarification?

 

[Duane Hookom]

You can try something like:

TRANSFORM Count(Flakes.Site_No) AS CountOfSite_No
SELECT Flakes.Material, Count(Flakes.Site_No) AS [Total of Site_No],
Count(Flakes.Site_No)/(Select Count(*) From Flakes Where Site_No="05-001")
As PctOfSomething
FROM Flakes
WHERE (((Flakes.Site_No) Like "05-001"))
GROUP BY Flakes.Material
PIVOT Flakes.Type IN ("Proximal", "Shatter", "Whole Flake",
"Medial-Distal");

The column headings make the crosstab easier to create reports from since
the query doesn't have to run to create the columns.

 

[luvarchaeology via AccessMonster.com]

Thank you Duane! The SQL worked perfectly and thanks for the explanation on
column headings. I'm slowly gaining an understaning of why to do things a
certain way. I am wondering now if there is a way to format the percent
results so that they appear like "12.5" rather than ".125045464", for example.

 

[luvarchaeology via AccessMonster.com]

Nevermind, I realized I only needed to open the properties option for the row
heading. Thanks though!