Report will not show identical lines

[Rik_StHelens]

I have a report which calculates the value of the tyre rubber remaining on a vehicle.

The report is based on the following query:

SELECT qryEfficiencyUnbound.[Vehicle Reg], qryEfficiencyUnbound.Tyre, qryEfficiencyUnbound.Pattern, qryEfficiencyUnbound.OTD, qryEfficiencyUnbound.RTD, qryEfficiencyUnbound.Cost, qryEfficiencyUnbound.[Price Per Mil], Sum([tblTyres].[cost])/Sum([otd])*Sum([rtd]) AS [Remaining Value]
FROM tblTyres INNER JOIN qryEfficiencyUnbound ON tblTyres.Pattern = qryEfficiencyUnbound.Pattern
GROUP BY qryEfficiencyUnbound.[Vehicle Reg], qryEfficiencyUnbound.Tyre, qryEfficiencyUnbound.Pattern, qryEfficiencyUnbound.OTD, qryEfficiencyUnbound.RTD, qryEfficiencyUnbound.Cost, qryEfficiencyUnbound.[Price Per Mil];


When I added the Sum([tblTyres].[cost])/Sum([otd])*Sum([rtd]) AS [Remaining Value] line to the SQL, this caused the report to stop displaying identical lines of data.

For example, two tyres fitted at the same time that are the same make/size/pattern/cost have worn equally over time, giving them the same [remaining value]. The report will only display one of the tyres, which then throws out my calculation across the whole vehicle/fleet/depot etc.

Any ideas whats happening here?

Thanks for your time!

 

[vbaInet]

Your query involves two tables, so when you're referencing a field the table name must precede it with a dot just like you did with [tblTyres].[cost]. Ensure all your fields are prefixed with their table names.

 

[Rik_StHelens]

Your query involves two tables, so when you're referencing a field the table name must precede it with a dot just like you did with [tblTyres].[cost]. Ensure all your fields are prefixed with their table names.

Have now changed the SQL as you advised so that it now looks like the following:

SELECT qryEfficiencyUnbound.[Vehicle Reg], qryEfficiencyUnbound.Tyre, qryEfficiencyUnbound.Pattern, qryEfficiencyUnbound.OTD, qryEfficiencyUnbound.RTD, qryEfficiencyUnbound.Cost, qryEfficiencyUnbound.[Price Per Mil], Sum([tblTyres].[cost])/Sum(qryEfficiencyUnbound.[otd])*Sum(qryEfficiencyUnbound.[rtd]) AS [Remaining Value]
FROM tblTyres INNER JOIN qryEfficiencyUnbound ON tblTyres.Pattern = qryEfficiencyUnbound.Pattern
GROUP BY qryEfficiencyUnbound.[Vehicle Reg], qryEfficiencyUnbound.Tyre, qryEfficiencyUnbound.Pattern, qryEfficiencyUnbound.OTD, qryEfficiencyUnbound.RTD, qryEfficiencyUnbound.Cost, qryEfficiencyUnbound.[Price Per Mil];

However, I am still getting them same problem in the report, where it does not display identical lines of information.

 

[vbaInet]

Your nested SUMs isn't helping. Also, you don't SUM on a calculated field within a query. If you want to perform that kind of summation then you should use the DSum() function:

http://www.techonthenet.com/access/functions/domain/dsum.php

Or use subqueries:

http://allenbrowne.com/subquery-01.html
http://www.techonthenet.com/oracle/subqueries.php

 

[namliam]

The identical lines are being "Grouped" and "Summed" in your query as per what you are telling it to do: Sum and Group by....

 

[Rik_StHelens]

aha!

sorted. thanks for your help!