Report Values Differ from Query Values

[Hillary]

I have a report created from the wizzard which is based on a query. When I run the query the results are how I want them. When I run the report the 4 formula field results are not what I am expecting.

The query is based on 2 tables. In order to get the query to display the results I want, I had to change the order of the fields (left to right) inside the body of the query. This is how I have the query set up...

Field/Total/Sort

Dist_No/Max/Ascending (If this field is not the left most field in the body of the query, the query doesn't return expected results)
Cust_Order_ID/Group By/Ascending
Line_No/Group By/Ascending
Sales, Cost, Gross Profit and Percent(all formulas)/(Total was set to First but when I saved the query, Access changed Sort to Expression and added First() around the each of the formulas)/<no sort>

I have tried to create the report several times through the wizzard. I have mimicked the sort order from the query in the wizzard and I have tried with no sort order but the report is not returning the First() values in Sales, Cost, Gross Profit and Percent.

Does anyone know how I can get the report to return the values exactly as the query has them?

Thanks,

Hillary

 

[dalenz]

Hi Hillary,

I would think that the group by should be the left field as usually you group by this field and then select min max etc.

What is Dist_No and is it possible to group by this rather than Max?

For your formula fields did you have something like Gross Profit: (([Sales]-[Cost])/[Sales]) then select Expression rather than Group by? I have never seen Access put the fisrt() around an expression.

If the query produces the right results are you up to creating a report without the wizard as that could fix the problem.

If you need help post a sample of the data and I will give it a go.