Access Report ignores DISTINCROW in query

I have a report that gets the data from a query. The query itself has DISTINCTROW in the SQL. However, when I use the query as the record source for the report, it outputs data as if DINSTINCTROW was not decalred in the the query (Unique Records).

Is there some option that I forgot to add while selecting the query as the report's record source? I've tried copying and p开发者_JS百科asting the SQL code into the reports record source, but there was no difference.

Query Result: DISTINCT (same as ALL)

A       B       C       D               
5669    7625    7625    $680.00
5677    7635    7635    $1,448.75
5757    7751    7751    $562.50
5776    7773    7773    $825.00
5796    7796    7796    $53.75
5810    7814    7814    $150.00

Query Result: DISTINCTROW

A       B       C       D           
5669    7625    7625    $156.25
5677    7635    7635    $30.00
5757    7751    7751    $180.00
5776    7773    7773    $15.00
5796    7796    7796    $31.25
5810    7814    7814    $120.00

This may happen if in the report, the displayed fields are not in the Detail section but in a grouping section such as header section or footer section. If it's so, then the Report displays only grouped values, i.e. distinct values.

It should just be if the query's 'unique values' property is set to yes, or if the query's sql begins SELECT DISTINCT. Are you sure you're not doing additional joins in your report recordsource? If you create a new blank report off your query, does it only return distinct rows?

In response to your comment, if a blank report based on the query returns non-distinct rows, then the problem is with the query. Have you checked the recordcounts to see if they match? Maybe it 'looks' like the query's returning distinct rows, but it really isn't. If a blank report based on the query returned distinct rows, on the other hand, you could be sure your query was ok and there was something introducing additional rows within the report.

Also, DISTINCT and DISTINCTROW return different results- you want DISTINCT, not DISTINCTROW. Check the 'unique values' property of the query.

'unique values' and 'unique rows' are two different things, you can check the help files. You want 'unique values'.

In the query, replace DISTINCTROW with DISTINCT.

Reference: Prédicats ALL, DISTINCT, DISTINCTROW, TOP