query to total for the month

[kdoran]

Ok I have been running a query off the date (for weekly, monthly, yearly checks) for an entry to find out if techs are doing there calibration checks for a certain area.

What I am finding is that if you calibrate a piece of equipment twice in one month it shows up twice and causes the total to be over the actual number of checks.

What I am asking help on is this;

Lets say there are 50 motors that have to be checked monthly. When the tech enters the found data there is a date field and notes field (I can have more if needed). Is there a better way to query that they did the checks for the month other than the date? I am open to any ideas.

There are also checks that have to be done weekly and yearly etc..

Thanks in advance,

Kelly

 

[lespaul]

What's the query you're using now that shows the duplicates? What is the data that is returned using this query? What results do you want to see from the query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Golom]

Far as I know, DateTime fields are really the only way to identify calendar positions ... although you could store things like the month number or year number in a numeric field.

Your issue seems to be that you want to count the number of calibrations done in a month but you also want to count each motor only once even if more than one calibration was done on it. Is that right?

Select Count(*) As [Calibrations]

From

(Select DISTINCT MotorID From tblCalibrations
 Where [CalibrationDate] BETWEEN [Start Date] AND [End Date])

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]