I tried that Doug but it didn't seem to work as [theMonth] value was
calculated.
Having said that I think I have found a way forward:
I was having so little success that I decided to simplify the task
somewhat.
I created a new database with a single table [tblData] and two fields
[startDate] and [endDate].
I then added four rows of data and tried to repeat the queries knowing
what to expect. (Table data shown below)
startDate endDate
01-jan-06 02-Feb-06
01-Feb-06 01-Dec-06
01-Jan-06 01-Jan-07
01-Jun-06 01-Dec-06
I first created a Union Query [qryMonths] to define the months (as shown
below):
SELECT DateAdd('d',1-Day([startDate]),[startDate]) AS dataByMonth
FROM tblData
GROUP BY DateAdd('d',1-Day([startDate]),[startDate])
UNION SELECT DateAdd('d',1-Day([endDate]),[endDate]) AS dataByMonth
FROM tblData
GROUP BY DateAdd('d',1-Day([endDate]),[endDate]);
I now tried to calculate the number of outstanding queries at each time
point in the database, knowing that the result should look as follows:
monthlyStats Outstanding
01/02/2006 3
01/03/2006 2
01/07/2006 3
01/01/2007 1
01/02/2007 0
My first attempts were unsuccessful but it was quite apparent that this
was because the date didn't seem to be registering correctly.
This led me to try adding the date using the dateserial format as shown
in the query below and it worked.
SELECT DateAdd('m',1,[dataByMonth]) AS monthlyStats,
DCount("startDate","tblData","endDate>=Dateserial(" & Year([dataByMonth])
& "," & Month([dataByMonth])+1 & ",1) and
startDate<=Dateserial(" & Year([dataByMonth]) & "," &
Month([dataByMonth])+1 & ",1)") AS Outstanding
FROM qryMonths
ORDER BY DateAdd('m',1,[dataByMonth]);
Now to try to do this in the main database. I'll let you know how I get
on.
Equally if you can think of a more graceful solution I would love to
know.
Thanks again for all your help, it is greatly appreciated.
All the best,
John
Douglas J. Steele said:
Try:
DwellAvg: CInt(Avg(DateDiff('d',[Date Raised],
DateSerial(Year([theMonth]), Month([theMonth]) + 1,1))))
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message I use two queries to find all the Raised and Closed dates as follows:
SELECT DateAdd("d",1-Day([Complete Date]),[Complete Date]) AS theMonth,
Count(tblQueryInfo.[Query Number]) AS QueriesClosed
FROM tblQueryInfo
WHERE (((tblQueryInfo.[Complete Date]) Is Not Null))
GROUP BY DateAdd("d",1-Day([Complete Date]),[Complete Date])
ORDER BY DateAdd("d",1-Day([Complete Date]),[Complete Date]);
and the other query:
SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS theMonth,
Count(tblQueryInfo.[Query Number]) AS QueriesRaised
FROM tblQueryInfo
GROUP BY DateAdd("d",1-Day([Date Raised]),[Date Raised])
ORDER BY DateAdd("d",1-Day([Date Raised]),[Date Raised]);
To ensure that I get all months even if a query was raised but not
closed (and vica versa) I then perform the following union query:
SELECT qryStatsTotalClosed.theMonth
FROM qryStatsTotalClosed
UNION SELECT qryStatsTotalRaised.theMonth
FROM qryStatsTotalRaised
ORDER BY theMonth;
As to your question below you are correct Doug. I would expect it to
credit 2 days for June, 31 for July and 2 for August.
The resulting average dwells would use 2 days for the June figure, 33
for July and 35 for August.
Hope that clarifys things somewhat and thanks again,
John
message So Date Raised is a field in tblQueryInfo?
How is theMonth defined in the query?
If you've got a query raised on 29 June, and ending 02 Aug, I assume
you want that treated as 2 days in June, 31 days in July and 2 days in
August?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message I am glad someone knows what they are doing Doug! I am having a
nightmare here...
I am trying to find the average dwell time for the queries on a
monthly basis.
To do this I have first written a guery which groups the dates into
months [theMonth] and I am then trying to use this to find the
average query dwell for each month.
Essentially I want to look at all the queries which were started and
not completed at the start of the month in question then measure how
long each one had been outstanding in days and take the average.
The data is originally from a table called [tblQueryInfo].
If you can offer any pointers I would greatly appreciate it.
message Using Year, Month and Day on a date field isn't going to help your
date format issues. [Date Raised] and DateSerial(Year([Date
Raised]),Month([Date Raised]),Day([Date Raised])) will always be the
same (and will depend on whether or not Access respects the date
format correctly). And your # delimiters aren't correct, and you're
using DAvg incorrectly (DAvg is a domain aggregate function that
will compute the average for a specific field in a table, using an
optional Where clause).
What exactly are you trying to calculate? Are Date Raised and
theMonth parameters, fields in the table or controls on a form?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message Could someone please tell me why the following section of code
gives -317 days average dwell when there is only one part, (for
info [Date Raised] is 24/11/2000 and [theMonth] is 01/11/200)?
DwellAvg: CInt(DAvg("datediff('d',Dateserial(year([Date
Raised]),month([Date Raised]),day([Date Raised])),
#" &
DateSerial(Year([theMonth]),Month(DateAdd('m',1,[theMonth])),1) &
"#)",
Could it be something to do with UK/US date formats and if so how
can I remedy this?
I know it is a problem in this part of the code as the query gives
the correct count of entries if I change the DAvg into a DCount.
Any help greatly appreciated.
Thanks,
John
and incase you think it is necesary the rest of the code follows:
"[tblQueryInfo]","
([Complete Date] is null or
(((month([Complete Date])>=" & Month([theMonth]) & " and
year([Complete Date])=" & Year([theMonth]) & ") or
year([Complete Date])>" & Year([theMonth]) & "))) and
((month([Date Raised])<=" & Month([theMonth]) & " and
year([Date Raised])=" & Year([theMonth]) & ") or
year([Date Raised])<" & Year([theMonth]) & ")"))
