Basic reporting question

[Optimus_1]

Hello



I have set up a report in the attached DB and I am trying to produce a report that only returns the 'operatives' that have used over 100 points per day.

Note: this could be an accumulatiuon of two or three lines of transaction for the same day etc..

many thanks - any further explaination please ask.

thanks

[alansidman]

Your first issue is that you don't have a normalized database. You have repeating fields in your main table. Correct this and it will save you lots of issues and make your queries easier to manage.

Here is a white paper on data base relationships.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

In the meantime, make your query an aggregate query. Here is the SQL from your query for making it an aggregate.

SELECT tblPersonalDetails.Name, tblOperatingTool.Description AS Tool, tblMain.[Week Commencing], tblMain.Mon, Sum([Points p/hour]*[Mon]) AS MonPts, tblMain.Tues, Sum([Points p/hour]*[Tues]) AS TuesPts, tblMain.Weds, Sum([Points p/hour]*[Weds]) AS WedsPts, tblMain.Thurs, Sum([Points p/hour]*[Thurs]) AS ThursPts, tblMain.Fri, Sum([Points p/hour]*[Fri]) AS FriPts, tblMain.Sat, Sum([Points p/hour]*[Sat]) AS SatPts, tblMain.Sun, Sum([Points p/hour]*[Sun]) AS SunPts
FROM tblPersonalDetails INNER JOIN (tblMain INNER JOIN tblOperatingTool ON tblMain.[Tools Input 1] = tblOperatingTool.Description) ON tblPersonalDetails.OperatorID = tblMain.OperatorID
GROUP BY tblPersonalDetails.Name, tblOperatingTool.Description, tblMain.[Week Commencing], tblMain.Mon, tblMain.Tues, tblMain.Weds, tblMain.Thurs, tblMain.Fri, tblMain.Sat, tblMain.Sun
ORDER BY tblPersonalDetails.Name, tblMain.[Week Commencing];

It will be cleaner looking if you normalize this and then aggregate by date.