Report is not showing the Sum and Avg from the Query

[np3il]

I have created many Queries in Access 2010. When I create the report of the query, some fields are asking me to enter data when the data is Sum and Avg by the query.

what am I doing wrong?

 

[dhookom]

Please don't expect us to all download your file. Can you provide the Record Source SQL Statement of your report?

How about the Control Source of the text boxes?

Also, tell us what section of the report your text boxes are in. Are they in a [red]Page[/red] section rather than Group or Report section?

Duane
Hook'D on Access
MS Access MVP

 

[np3il]

1. Created the Query and it is running fine. Created with Query Design.
2. After creating the Report for the query, some fields are asking to enter data, when that data is gathered by the query.
2a. The report was created by using the Report Wizard from the Access 2010.
2b. The file that was attached is just the report after it is run.

3. The fields in the report that are requesting entry are Sum(s) that is gathered from the query.
3a. Example ...
Field: GrossProfit
Table: DriverData
Total: Sum

In the Query the number are fine, but in the Report Access is requesting a value and does not accept the Query Total: Sum.
Thanks

 

[dhookom]

Please go to the design view of the report and provide the requested information. I don't see any SQL statements, report sections, or control sources in your posting as I expected.



Duane
Hook'D on Access
MS Access MVP

 

[np3il]

The Report was created from the Query.
The query was created using Query Wizard. I did not use any SQL statement.

Report Sections
Detail -----
SumOfGrossProfit

-------
Should I add additional Criteria in the Query Wizard?
Should I replace the Report Detail of the SumOfGrossProfit to =Sum([Table!GrossProfit])? or something similar ...

-------
Teh SQL statement should be: Example ...
SELECT GrossProfit
FROM Table
WHERE PayRollPeriod=5 and ID="Sumirized by ID and Totaled"
-------
Out put should be: Example ...
ID GrossProfit PayRollPeriod
1 11111 5
2 2222 5

--------
I hope this explain it better.

Thanks

...

 

[dhookom]

Every query has a SQL view. I would like you to find it and copy it to post into a reply here.

Every text box has a Control Source property. I would like you to find these for significant text boxes and post into a reply here.

Duane
Hook'D on Access
MS Access MVP

 

[np3il]

SQL View:
SELECT Drivers.DriverID, [Driver-Data].DriverName, Drivers.Shift, Drivers.LocationID, Count(Drivers.NickName) AS CountOfNickName, Sum([Driver-Data].GrossProfit) AS SumOfGrossProfit, Avg([Driver-Data].GrossProfit) AS AvgOfGrossProfit, Sum([Driver-Data].[Shift Time]) AS [SumOfShift Time], Avg([Driver-Data].[Shift Time]) AS [AvgOfShift Time], Sum([Driver-Data].Mileage) AS SumOfMileage, Avg([Driver-Data].Mileage) AS AvgOfMileage, Sum([Driver-Data].Stops) AS SumOfStops, Avg([Driver-Data].Stops) AS AvgOfStops, Sum([Driver-Data].[Gallons Delivered]) AS [SumOfGallons Delivered], Avg([Driver-Data].[Gallons Delivered]) AS [AvgOfGallons Delivered], [SumOfGrossProfit]/[SumOfShift Time] AS GP_H, [SumOfGrossProfit]/[SumOfMileage] AS GP_M, [SumOfGallons Delivered]/[SumOfShift Time] AS GAL_H, [Driver-Data].[PayRoll Period]
FROM Drivers INNER JOIN [Driver-Data] ON Drivers.NickName = [Driver-Data].DriverName
GROUP BY Drivers.DriverID, [Driver-Data].DriverName, Drivers.Shift, Drivers.LocationID, [SumOfGrossProfit]/[SumOfShift Time], [SumOfGrossProfit]/[SumOfMileage], [SumOfGallons Delivered]/[SumOfShift Time], [Driver-Data].[PayRoll Period]
HAVING ((([Driver-Data].[PayRoll Period])=[Enter PayRoll Period: ]))
ORDER BY [Driver-Data].DriverName;

Text Boxes:
Control Source: SumOfGrossProfit

 

[dhookom]

I would not perform the division in the query. Do it in the report. Keep your report's record source to:

SELECT Drivers.DriverID, [Driver-Data].DriverName, Drivers.Shift, Drivers.LocationID, 
Count(Drivers.NickName) AS CountOfNickName, Sum([Driver-Data].GrossProfit) AS SumOfGrossProfit, 
Avg([Driver-Data].GrossProfit) AS AvgOfGrossProfit, Sum([Driver-Data].[Shift Time]) AS [SumOfShift Time],
Avg([Driver-Data].[Shift Time]) AS [AvgOfShift Time], Sum([Driver-Data].Mileage) AS SumOfMileage, 
Avg([Driver-Data].Mileage) AS AvgOfMileage, Sum([Driver-Data].Stops) AS SumOfStops, 
Avg([Driver-Data].Stops) AS AvgOfStops, Sum([Driver-Data].[Gallons Delivered]) AS [SumOfGallons Delivered],
Avg([Driver-Data].[Gallons Delivered]) AS [AvgOfGallons Delivered], [Driver-Data].[PayRoll Period]
FROM Drivers INNER JOIN [Driver-Data] ON Drivers.NickName = [Driver-Data].DriverName
WHERE [Driver-Data].[PayRoll Period]=[Enter PayRoll Period: ]
GROUP BY Drivers.DriverID, [Driver-Data].DriverName, Drivers.Shift, 
Drivers.LocationID, [Driver-Data].[PayRoll Period]
ORDER BY [Driver-Data].DriverName;

If you want to put the division in the query, don't use the alias. Repeat the Sum() like:

Sum([Driver-Data].GrossProfit)/Sum([Driver-Data].Mileage) AS GP_M

Duane
Hook'D on Access
MS Access MVP

 

[np3il]

The query is orking fine.

Where in the Report can I modify the SQL statement?

 

[dhookom]

If the query was working fine, I don't think you would be here asking questions. Your car might work fine until you put more people in it or needed to drive through rain. Consider implementing what I have suggested.

You modify the SQL where you found it. It should be the report's Record Source property.

Duane
Hook'D on Access
MS Access MVP

 

[np3il]

I implemented your suggestion and re-created the report.

It worked ...

Thnaks !!!