Do reports perform data processing?

[tomjermy]

Hello,

I have a master table containing data - I need to process that data so that it does the following:

I need Access to find all rows where 'date', 'operation', 'site', 'plot' are the same, then add 'value' and 'hours' together and give me the information in a report.

Can anyone help me?

Thanks
Tom Jermy

 

[bernie10]

Try this- use the following query to base your report off of.


SELECT YourTable.Date, YourTable.Operation, YourTable.Site, YourTable.Plot, [YourTable].[Value]+[YourTable].[Hours] AS [Sum]
FROM YourTable
WHERE (((YourTable.Date)=[YourTable].[Operation] AND [YourTable].[Site] AND [YourTable].[Plot]) AND ((YourTable.Operation)=[YourTable].[Date] AND [YourTable].[Site] AND [YourTable].[Plot]) AND ((YourTable.Site)=[YourTable].[Date] AND [YourTable].[Operation] AND [YourTable].[Plot]) AND ((YourTable.Plot)=[YourTable].[Date] AND [YourTable].[Operation] AND [YourTable].[Site]));

Some of the SQL experts may be able to offer a better way to do it, but this should work.

Hope this helps,
Collen

 

[traingamer]

or even

SELECT YourTable.Date, YourTable.Operation, YourTable.Site, YourTable.Plot, Sum([YourTable].[Value]+[YourTable].[Hours]) AS Total
FROM YourTable
Group by YourTable.Date, YourTable.Operation, YourTable.Site, YourTable.Plot

Or use the entire table as the record source for the report, use sorting and grouping to group as above and put the sum in the the footer for the first group.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[tomjermy]

Thanks. With a bit of tweaking, it works perfectly - the code is here:

SELECT MasterTable.OPERATIVE, MasterTable.DATE, MasterTable.OPERATION, MasterTable.SITE, MasterTable.PLOT, Sum(MasterTable.VALUE) AS Total
FROM MasterTable
GROUP BY MasterTable.OPERATIVE, MasterTable.DATE, MasterTable.OPERATION, MasterTable.SITE, MasterTable.PLOT;