Thursday Count

[Gibberish]

I have tried several Data functions to determine how to count the
total number of "Thursday's" in a given month. For instance:

If I choose to find out how many "Thursday's" are in the month of
September in any given year, how can I find this within a query by not
writing a
complex code in VB?

 

[Guest]

Gibberish,

Bear with me on this. Whenever I want to do anything with ranges of dates
or ranges of numbers, I create a table with the numbers 0 through 9, then a
query from that that contains the numbers 0 through 999 (larger if I think
I'll need it). With this table and query, I can accomplish what you are
looking for relatively simply.

1. I have a table (tbl_Numbers) that contains a single long integer field
(lngNumber) and contains the values 0 through 9

2. The query (qry_Numbers) looks like:
SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber as
lngValues
FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones

3. Then, to do what you want, you need to do something like the following.
This query has a subquery that develops the list of dates for the year and
month the user inputs. The outer query limits the list to those where the
Weekday( ) function returns a value that matches the user provided value, and
the Count () gives you a total count of the days in the month that fall on a
specific weekday.

SELECT Count(dtDay) as PerMonth
FROM (
SELECT DateSerial([Enter year],[Enter month],1)+[lng_Value] AS dtDay
FROM qry_Numbers
WHERE DateSerial([Enter year],[Enter month],1)+[lng_Value]<DateSerial([Enter
year],[Enter month]+1,1)) as MonthDays
WHERE weekday(dtDay,1) = [What day of the week (numeric starting on Sunday)]

If I was going to do this for use in an application, I'd have a text box for
the year, and combo boxes for the month and day of the week on a form. I
would then change the references in the query below to these controls, and
would have another control that displays the number by using:

=DLOOKUP("PerMonth", "qryCount_Of_Weekday_in_Month")

HTH
Dale

 

[Gibberish]

Gibberish,

Bear with me on this. Whenever I want to do anything with ranges of dates
or ranges of numbers, I create a table with the numbers 0 through 9, then a
query from that that contains the numbers 0 through 999 (larger if I think
I'll need it). With this table and query, I can accomplish what you are
looking for relatively simply.

1. I have a table (tbl_Numbers) that contains a single long integer field
(lngNumber) and contains the values 0 through 9

2. The query (qry_Numbers) looks like:
SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber as
lngValues
FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones

3. Then, to do what you want, you need to do something like the following.
This query has a subquery that develops the list of dates for the year and
month the user inputs. The outer query limits the list to those where the
Weekday( ) function returns a value that matches the user provided value, and
the Count () gives you a total count of the days in the month that fall on a
specific weekday.

SELECT Count(dtDay) as PerMonth
FROM (
SELECT DateSerial([Enter year],[Enter month],1)+[lng_Value] AS dtDay
FROM qry_Numbers
WHERE DateSerial([Enter year],[Enter month],1)+[lng_Value]<DateSerial([Enter
year],[Enter month]+1,1)) as MonthDays
WHERE weekday(dtDay,1) = [What day of the week (numeric starting on Sunday)]

If I was going to do this for use in an application, I'd have a text box for
the year, and combo boxes for the month and day of the week on a form. I
would then change the references in the query below to these controls, and
would have another control that displays the number by using:

=DLOOKUP("PerMonth", "qryCount_Of_Weekday_in_Month")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I have tried several Data functions to determine how to count the
total number of "Thursday's" in a given month. For instance:

If I choose to find out how many "Thursday's" are in the month of
September in any given year, how can I find this within a query by not
writing a
complex code in VB?- Hide quoted text -

- Show quoted text -

*********************************************************************************************************************************************
Thanks Dale..... I tried the following, but the only success I am
having is getting through your step #2.
Step 3 is not giving me any real meaningful prompt and no matter what
values I enter, I am only getting a return of zero.

I am prompted for the following with step 3's subquery:
- Enter Year
- Enter month
- lng_Value
- Enter
- What day of the week (numeric starting on Sunday)

Any additional info?

Gibberish
**********************************************************************************************************************************************

 

[Dale Fye]

You should only be asked for [Enter Year], [Enter Month], and [What day of
the week (numeric starting on Sunday)]

Try copying your SQL and posting it. With the line wraps, you may have
misread some of my SQL string.

Dale

Gibberish,

Bear with me on this. Whenever I want to do anything with ranges of
dates
or ranges of numbers, I create a table with the numbers 0 through 9, then
a
query from that that contains the numbers 0 through 999 (larger if I
think
I'll need it). With this table and query, I can accomplish what you are
looking for relatively simply.

1. I have a table (tbl_Numbers) that contains a single long integer field
(lngNumber) and contains the values 0 through 9

2. The query (qry_Numbers) looks like:
SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber as
lngValues
FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones

3. Then, to do what you want, you need to do something like the
following.
This query has a subquery that develops the list of dates for the year
and
month the user inputs. The outer query limits the list to those where
the
Weekday( ) function returns a value that matches the user provided value,
and
the Count () gives you a total count of the days in the month that fall
on a
specific weekday.

SELECT Count(dtDay) as PerMonth
FROM (
SELECT DateSerial([Enter year],[Enter month],1)+[lng_Value] AS dtDay
FROM qry_Numbers
WHERE DateSerial([Enter year],[Enter
month],1)+[lng_Value]<DateSerial([Enter
year],[Enter month]+1,1)) as MonthDays
WHERE weekday(dtDay,1) = [What day of the week (numeric starting on
Sunday)]

If I was going to do this for use in an application, I'd have a text box
for
the year, and combo boxes for the month and day of the week on a form. I
would then change the references in the query below to these controls,
and
would have another control that displays the number by using:

=DLOOKUP("PerMonth", "qryCount_Of_Weekday_in_Month")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I have tried several Data functions to determine how to count the
total number of "Thursday's" in a given month. For instance:

If I choose to find out how many "Thursday's" are in the month of
September in any given year, how can I find this within a query by not
writing a
complex code in VB?- Hide quoted text -

- Show quoted text -

*********************************************************************************************************************************************
Thanks Dale..... I tried the following, but the only success I am
having is getting through your step #2.
Step 3 is not giving me any real meaningful prompt and no matter what
values I enter, I am only getting a return of zero.

I am prompted for the following with step 3's subquery:
- Enter Year
- Enter month
- lng_Value
- Enter
- What day of the week (numeric starting on Sunday)

Any additional info?

Gibberish
**********************************************************************************************************************************************

 

[Michael Gramelspacher]

I have tried several Data functions to determine how to count the
total number of "Thursday's" in a given month. For instance:

If I choose to find out how many "Thursday's" are in the month of
September in any given year, how can I find this within a query by not
writing a
complex code in VB?

Something to consider:
If you were to have a Calendar table:

Sub MakeCalendar()
Dim d As Date
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

' Code per Ken Sheridan

Set db = DBEngine(0)(0)

strSQL = "CREATE TABLE Calendar " & _
"(calendar_date DATETIME NOT NULL PRIMARY KEY, " & _
"weekday_nbr SMALLINT, " & _
"weekday_name VARCHAR(10));"
db.Execute strSQL, dbFailOnError

Set rst = db.OpenRecordset("Calendar", dbOpenTable, dbAppendOnly)
For d = #1/1/2007# To #12/31/2007#
rst.AddNew
rst("calendar_date").Value = d
rst("weekday_nbr").Value = DatePart("w", d)
rst("weekday_name").Value = WeekdayName(Weekday(d, vbSunday))
rst.Update
Next d
End Sub

SELECT FORMAT(c.calendar_date,"mm-yyyy") AS [Month and Year],
COUNT(*) AS Thursdays
FROM Calendar AS c
WHERE c.calendar_date BETWEEN [Enter start date:]
AND [Enter end date:]
AND c.weekday_name = "Thursday"
GROUP BY FORMAT(c.calendar_date,"mm-yyyy");