Report displaying duplicate entries (1 Viewer)

[merlin001]

Here's what I've got right now:

SELECT Adjustments.Cashier, Cashiers.Cashier_Name, Adjustments.Reason, Adjustments.CA_Date, Adjustments.Difference, Adjustments.Cashier, Adjustments.Cashier
FROM Reasons INNER JOIN (Cashiers INNER JOIN Adjustments ON Cashiers.Cashier_Number = Adjustments.Cashier) ON Reasons.Reason = Adjustments.Reason
WHERE (((Adjustments.No_Fault)=Yes) AND ((Adjustments.Warehouse)=6))
ORDER BY Adjustments.CA_Date, Cashiers.Cashier_Name;

In a nutshell: I've got 3 tables (Adjustments, Reasons, and Cashiers). In the Adjustments table, I've got the records of the cashier numbers, what they did wrong, how much, the date, etc. In the Cashiers table, I've got the cashier numbers and their names. In the Reasons table, I've got the reason id and the actual reason. The reason table is for the form, then the result is dumped into the adjustments table.

What I'm trying to do is display the cashiers who didn't screw up, but only display them once. When I run the report, I get some duplicates. From my understanding, SELECT DISTINCT won't work without grouping. I've tried selecting the hide duplicates in the report's field properties, but when I display the report, I get nice empty lines where the duplicates would be instead of automatically adjusting the report and removing the blank lines.

So, now that I've probably bored you guys to death, I could use a bit of a nudge as to where I would go to remove the duplicates, but not show the blank lines in their spot.

Thank you kindly,
~ David

 

[Velosai]

Try this:

SELECT DISTINCT Cashier_Name
FROM Reasons INNER JOIN (Cashiers INNER JOIN Adjustments ON Cashiers.Cashier_Number = Adjustments.Cashier) ON Reasons.Reason = Adjustments.Reason
WHERE No_Fault=Yes AND Warehouse=6
GROUP BY Cashier_Name
ORDER BY Cashier_Name;

 

[merlin001]

That worked great! Thanks so much!

Try this:

SELECT DISTINCT Cashier_Name
FROM Reasons INNER JOIN (Cashiers INNER JOIN Adjustments ON Cashiers.Cashier_Number = Adjustments.Cashier) ON Reasons.Reason = Adjustments.Reason
WHERE No_Fault=Yes AND Warehouse=6
GROUP BY Cashier_Name
ORDER BY Cashier_Name;