Crosstab query help

[Opal]

I have been researching this all afternoon, and
I just can't seem to find the solution. I know
what I need but I just can't get from point A to
point B.

I am running access 2003. I created the following
2 queries to count the records in my table:


SELECT DISTINCT StartAudit.ObserverName, StartAudit.StartDate,
StartAudit.EndDate
FROM StartAudit
GROUP BY StartAudit.ObserverName, StartAudit.StartDate,
StartAudit.EndDate
HAVING (((StartAudit.StartDate)=[Forms]![HoldingInfo]!
[TxtReportStart]) AND ((StartAudit.EndDate)=[Forms]![HoldingInfo]!
[TxtReportEnd]));


SELECT Count(*) AS [Audits Completed], 8-Count(*) AS [Not Completed]
FROM qryStartDistinct;


This will generate the following results:


[Audits Completed] [Not Completed]
6 2
(Point A)


I need now to turn this data into a pie chart with a
query that will give results like:


Value1 Value2
Audits completed 6
Not Completed 2
(Point B)


How can I get to point B??? I have been trying
to create a crosstab but I don't have enough data
and I am stumped.

 

[MGFoster]

I have been researching this all afternoon, and
I just can't seem to find the solution. I know
what I need but I just can't get from point A to
point B.

I am running access 2003. I created the following
2 queries to count the records in my table:


SELECT DISTINCT StartAudit.ObserverName, StartAudit.StartDate,
StartAudit.EndDate
FROM StartAudit
GROUP BY StartAudit.ObserverName, StartAudit.StartDate,
StartAudit.EndDate
HAVING (((StartAudit.StartDate)=[Forms]![HoldingInfo]!
[TxtReportStart]) AND ((StartAudit.EndDate)=[Forms]![HoldingInfo]!
[TxtReportEnd]));


SELECT Count(*) AS [Audits Completed], 8-Count(*) AS [Not Completed]
FROM qryStartDistinct;


This will generate the following results:


[Audits Completed] [Not Completed]
6 2
(Point A)


I need now to turn this data into a pie chart with a
query that will give results like:


Value1 Value2
Audits completed 6
Not Completed 2
(Point B)


How can I get to point B??? I have been trying
to create a crosstab but I don't have enough data
and I am stumped.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, the first query doesn't need the GROUP BY if you already have the
DISTINCT designation in the SELECT clause. So the correct query would
look like this:

PARAMETERS [Forms]![HoldingInfo]![TxtReportStart] Date,
[Forms]![HoldingInfo]![TxtReportEnd] Date;
SELECT DISTINCT StartAudit.ObserverName, StartAudit.StartDate,
StartAudit.EndDate
FROM StartAudit
WHERE (((StartAudit.StartDate)=[Forms]![HoldingInfo]!
[TxtReportStart]) AND ((StartAudit.EndDate)=[Forms]![HoldingInfo]!
[TxtReportEnd]));

I added the PARAMETERS clause, 'cuz, sometimes, queries don't correctly
interpret dates from form references - they have to be explicitly
defined.

A cross-tab query needs at least three (3) columns (fields) in the data
source. Your COUNT() query only has 2 columns. If you're creating a
report, where the chart will be shown, you can put the columns into two
(2) TextBoxes, as you've shown in your example. You don't have to use
just the query as the display object.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmjCeoechKqOuFEgEQLW3wCfZMWSH3XlRvHZSe7IAIx8AQQxUVcAnjxG
3mTOrshxt1WOxLCi0qI7j8F2
=NSso
-----END PGP SIGNATURE-----