I'm trying to display a list of a cashier number and name. My report itself only displays 2 fields: Adjustments.Cashier and Cashiers.Cashier_Name. I've got it setup so that I get all the names of the ones that have screwed up, and dump them to the report. This works great, except I get tons of duplicates. I'm looking to just have one for each cashier.
I've got 3 tables:
Adjustments (holds cashier number, date, reason, amount, etc)
Cashiers (holds cashier's number and their name)
Reasons (holds the list of screw ups with an ID for my form)
Okay, my original SQL code is:
This works, but displays a lot of duplicates.
I then tried:
but this ends up giving me the nice error:
You tried to execute a query that does not include the specified expression Cashier_Name as part of the aggregate function
I know I can get rid of the error if I add all my selects to the group by, but then I still get all the dups displayed in the report. Any suggestions?
Thank you kindly,
~ David
I've got 3 tables:
Adjustments (holds cashier number, date, reason, amount, etc)
Cashiers (holds cashier's number and their name)
Reasons (holds the list of screw ups with an ID for my form)
Okay, my original SQL code is:
Code:
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;
This works, but displays a lot of duplicates.
I then tried:
Code:
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
GROUP BY Adjustments.Cashier
HAVING (((Adjustments.No_Fault)=Yes) AND ((Adjustments.Warehouse)=6))
ORDER BY Adjustments.CA_Date, Cashiers.Cashier_Name;
You tried to execute a query that does not include the specified expression Cashier_Name as part of the aggregate function
I know I can get rid of the error if I add all my selects to the group by, but then I still get all the dups displayed in the report. Any suggestions?
Thank you kindly,
~ David