Duplicate values in Crosstab

[Opal]

I am running Access 2003 and have a chart based
on a crosstab query. The problem is my
x-axis or row heading is showing duplicate
values and I can't figure out how to get rid
of them. My select query from which the
crosstab is created is as follows:

SELECT DriveAuditCheck.InputDate,
[Shift] & " Shift" AS ShiftName, DriveAuditCheck.UnsafeTally,
Unsafe.UnsafeDesc, Unsafe.UnsafeID
FROM Unsafe INNER JOIN DriveAuditCheck
ON Unsafe.UnsafeID = DriveAuditCheck.UnsafeID
ORDER BY Unsafe.UnsafeID;

And my Crosstab:

PARAMETERS [Forms]![ReportFrm]![TxtStartDate] DateTime,
[Forms]![ReportFrm]![TxtEndDate] DateTime;
TRANSFORM Sum(qryDriveCheck.UnsafeTally)
AS SumOfUnsafeTally
SELECT qryDriveCheck.UnsafeID
FROM qryDriveCheck
WHERE (((qryDriveCheck.InputDate) Between
[Forms]![ReportFrm]![TxtStartDate] And
[Forms]![ReportFrm]![TxtEndDate]))
GROUP BY qryDriveCheck.UnsafeDesc,
qryDriveCheck.InputDate, qryDriveCheck.UnsafeID
ORDER BY qryDriveCheck.UnsafeID
PIVOT qryDriveCheck.ShiftName;

Its the UnsafeID that repeats....I can't see the forest
for the trees to figure out why. Can anyone help me out?

 

[MGFoster]

I am running Access 2003 and have a chart based
on a crosstab query. The problem is my
x-axis or row heading is showing duplicate
values and I can't figure out how to get rid
of them. My select query from which the
crosstab is created is as follows:

SELECT DriveAuditCheck.InputDate,
[Shift] & " Shift" AS ShiftName, DriveAuditCheck.UnsafeTally,
Unsafe.UnsafeDesc, Unsafe.UnsafeID
FROM Unsafe INNER JOIN DriveAuditCheck
ON Unsafe.UnsafeID = DriveAuditCheck.UnsafeID
ORDER BY Unsafe.UnsafeID;

And my Crosstab:

PARAMETERS [Forms]![ReportFrm]![TxtStartDate] DateTime,
[Forms]![ReportFrm]![TxtEndDate] DateTime;
TRANSFORM Sum(qryDriveCheck.UnsafeTally)
AS SumOfUnsafeTally
SELECT qryDriveCheck.UnsafeID
FROM qryDriveCheck
WHERE (((qryDriveCheck.InputDate) Between
[Forms]![ReportFrm]![TxtStartDate] And
[Forms]![ReportFrm]![TxtEndDate]))
GROUP BY qryDriveCheck.UnsafeDesc,
qryDriveCheck.InputDate, qryDriveCheck.UnsafeID
ORDER BY qryDriveCheck.UnsafeID
PIVOT qryDriveCheck.ShiftName;

Its the UnsafeID that repeats....I can't see the forest
for the trees to figure out why. Can anyone help me out?

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

In the cross-tab qry I don't know why you have the UnsafeDesc in the
GROUP BY clause - isn't it related, 1-to-1, to the UnsafeID? Since you
don't use the UnsafeDesc in the query why not just remove it from both
queries?

Also, you don't need the ORDER BY clause in the first query 'cuz the
cross-tab query will just re-order the rows. The ORDER BY is also not
required in the cross-tab query 'cuz the GROUP BY will sort the data -
all you have to do is

GROUP BY UnsafeID, InputDate

and the rows will be sorted first by UnsafeID then by InputDate.

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/AwUBSkrUqIechKqOuFEgEQJhVACgmvVsdrL3OX5DF35e1ZOSaklwt8cAoK06
lGEWCBPvxijqDDnAhJBq/YRn
=uQpI
-----END PGP SIGNATURE-----

 

[Opal]

Thank you for your advice. I made the changes you
suggested, but I am still getting the duplicate values
:-(

 

[Opal]

NVM

I got it: Crosstab now looks like this:

PARAMETERS [Forms]![ReportFrm]![TxtStartDate] DateTime,
[Forms]![ReportFrm]![TxtEndDate] DateTime;
TRANSFORM Sum(qryDriveCheck.UnsafeTally)
AS SumOfUnsafeTally
SELECT qryDriveCheck.UnsafeID
FROM qryDriveCheck
WHERE (((qryDriveCheck.InputDate)
Between [Forms]![ReportFrm]![TxtStartDate]
And [Forms]![ReportFrm]![TxtEndDate]))
GROUP BY qryDriveCheck.UnsafeID
PIVOT qryDriveCheck.ShiftName;

Thanks for getting me thinking