The Query That Returns Too Much
A sales manager writes a query on an Orders table to list orders placed in March 2024 with a total over 500. The query returns 40 rows, but the manager expected about 12. The criteria row contains Between #01/03/2024# And #31/03/2024# in the date column and >500 in the total column, both on the same criteria row. The manager also notices some February orders in the results. She asks whether the query logic is wrong or the data is wrong.
1. Diagnose the likely cause of February orders appearing and how to test whether the criteria or the data is at fault.
Read the worked answer
Criteria on the same row are combined with AND, which is correct here - so the logic is probably not the issue. February rows suggest a data or format problem, most likely dates stored as text or in a different regional order (e.g. month/day swapped), so 01/03/2024 is read as 3 January. To test: run a query returning only the date column for the suspect rows and inspect how the values are actually stored; check the field's data type. A quick isolation test is to query for a single known February order and see whether it matches the March criteria. If it does, the storage format is wrong, not the criteria.
2. The manager wants to be sure the total filter is applied correctly. What should she verify about the total field?
Read the worked answer
She should confirm the total field is numeric, not text. A text field sorts and compares alphabetically, so >500 could behave unexpectedly (e.g. '99' > '500' as text). She should also check whether the total is stored per line item or per order - if Orders holds line items, filtering on a line total is not the same as filtering on the order total. If order totals are needed, the query should group by order and sum the lines before applying the >500 filter (a HAVING condition rather than a WHERE on individual lines). Verifying type and grain prevents a plausible-looking but wrong result.