How to count a specific day of the week in a date range

[zjzastava]

Here is a little background first. I have a report with rows labeled 9:00 - 10:00 am, 10:00 - 11:00 am, thru 12:00 -1:00am and the columns are Mon, Tue, and so on. In addition we have a total for each row and column. The report is generated from a date range that is entered into a form from the user.

The report is using Dlookup to reference the query's for the particular location of the report (Mon at 9am). Here is an example of one of the query's I am using.

SELECT COUNT(*) AS NumberOfPics
FROM tblOldAbes
WHERE ((([tblOldAbes].[Day]="Wednesday") AND (([tblOldAbes].[Time])="16") AND (([tblOldAbes].[FullDate]) BETWEEN [forms].[frmDateRangeOA].[txtDateStartOA] AND [forms].[frmDateRangeOA].[txtDateEndOA]));

This is working fine. The query counts all the records.

I want to take this a step further and do an average not just a count. This will be in a new and separate report. I want to be able to count the number of Saturdays in the given date range. In the table there will be multiple entries on any Saturday. I only want to count 1 Saturday for a given date. for example the date range entered would be 2/1/2011 - 2/28/2011 Has 4 Saturdays in that range. Then we want to take that number (4 from the previous example) and use that to find the average.

I am having trouble wrapping my mind around the solution to count the Number of Saturdays in the given date range.

If I have not given enough info or you have any questions let me know. Any suggestions would be greatly appreciated.

Thank You
Zac Z

 

[dhookom]

Old Abes" sounds like EC, WI. Both my kids graduated from Memorial (1999 and 2002).

I'm not sure what value you want to average. Are you looking for the average number of records for Saturdays during that date range?

If I am correct, you might try:

SELECT DateValue(FullDate) as TheDate, 
 Format(FullDate,"dddd") as DayName, 
 Count(*) as NumOf
FROM tblOldAbes
WHERE WeekDay(FullDate) = 7 AND
 FullDate BETWEEN [forms].[frmDateRangeOA].[txtDateStartOA]
  AND [forms].[frmDateRangeOA].[txtDateEndOA]
GROUP BY DateValue(FullDate),Format(FullDate,"dddd") ;

This will return the number of records for each Saturday. You can then use this totals query in another totals query like:

SELECT DayName, Avg(NumOf) as RecCount
FROM [qgrpYourTotalsQueryName]
GROUP BY DayName;

Let us know if I have assumed wrong...


Duane
Hook'D on Access
MS Access MVP

 

[zjzastava]

That is pretty good Duane. I went to school in Eau Claire at UWEC. I am still in WI. Sorry for the long wait between replies.

The first step I am trying to do is divide the results of my query by a number entered in by the user.

The query in my first post works fine a returns the number I need. I want to take that a step further and divide result from that by a number entered by the user from a form.

If I am not clear on something please let me know.
Thank You
Zac Z

 

[dhookom]

I'm not sure of the context of your questions in this thread but the quick answer is:

SELECT NumberOfPics/[forms].[frmDateRangeOA].[txtDenominator] as Result
FROM qselFirstQueryName;

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Select sum(NumberOfPics)/count(fulldate)
from (
SELECT COUNT(*) AS NumberOfPics,tblOldAbes.[FullDate]
FROM tblOldAbes
WHERE ((([tblOldAbes].[Day]="Wednesday")
AND (([tblOldAbes].[Time])="16")
AND (([tblOldAbes].[FullDate]) BETWEEN [forms].[frmDateRangeOA].[txtDateStartOA] AND [forms].[frmDateRangeOA].[txtDateEndOA]))
group by tblOldAbes.[FullDate] )dt

 

[zjzastava]

Thank You that seems to work with the small test run. What I am really trying to do is have the denominator be automatically generated without having the user enter in the number.

Is there a way for me to count the number of Saturdays in a given date range. The table I am using will have multiple entries for any given Saturday. I do not want to count all the entries for a Saturday, just count the day itself. In case I am not clear I will try to give an example.

Using the date range of 2/4/2011 - 2/13/2011. In this date range there are 2 Saturdays. That is what I want to count. In my table I have 20 to 50 entries on each Saturday. All I want to get out of the date range is the number of Saturdays (2).

Then that number will replace the [forms].[frmDateRangeOA].[txtDenominator] from dhookoms quick answer.

I hope I have been clear.

Thank You
Zac Z

 

[PWise]

have you tried my sugestion

 

[zjzastava]

Pwise, I did try your suggestion just has you have it written and received a syntax error in the from clause. I am a novice with access as you may have figured out and was unable to get it to work. any suggestions would be greatly appreciated.