Duplicate values in Crosstab

  • Thread starter Thread starter Opal
  • Start date Start date
O

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?
 
Thank you for your advice. I made the changes you
suggested, but I am still getting the duplicate values
:-(
 
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 :-)
 
Back
Top