working out average number of records.

[antihippy]

Hi,

I've got a question which seems to be quite simple but I cannot seem to get it to work,. I'd appreciate it if anyone can post any suggestions.

I have a table (SR). There are a number of columns, of which, I am only interested in CompanyName, LogDate and Requests. What I want to do is work out the average number of Requests per day between two dates. Those two dates are used to limit what comes back from the LogDate column. The LogDate should be user definable via parameter.

Here's an outline of what I think it should be:

@Start -- Start date
@End -- End Date.  These are paramters that I want to use to filter the result set.

SELECT CompanyName, count(Requests)/DateDiff(d, @start, @End) FROM SR 
WHERE LogDate BETWEEN @Start AND @End
GROUP BY CompanyName
ORDER BY CompanyName

How do you do this via the QBE in Access? For various political reasons I have to get it to work via Access. The sums seem pretty easy to me however I seem unable to get it working. When I tried the above via the QBE (I entered some SQL in the SQL View then looked at the design view to see where I wanted to go next) it kept interpreting the 'd' in the DateDiff as a parameter. This is driving me nuts!

 

[pbrodsky]

The d in datediff needs to be in quotes ("d"), the parameters should be in square brackets [start], [end]. Parameters for the query can be set in the query menu in design mode.

 

[antihippy]

Nice one! I know that the param's on the QBE have to be enclosed in '[]' but it was really the 'd' being interpreted as a parameter that was annoying me.