DatePart question

[Guest]

Hello all,

I am trying to produce the following example results based on hire_date and
Record_Date:

Hire_Date Record_Date Week_Num
1/01/06 1/04/06 1 '1/1 through 1/7 should all
show week #1
1/01/06 1/12/06 2
1/01/06 1/18/06 3
1/01/06 2/01/06 5

I have been playing with:
WeekNumberatePart("ww",[Record_Date])
But unable to show results showing one row per week.
Example: if I have 10 records all dated 1/1 through 1/7, and 12 records
dated 1/8 through 1/14, I want one row to show Week_Num 1 and another row to
show Week_Num 2.

Hope that makes sense! Thank you much!
Renee

 

[Jeff Boyce]

Renee

Are you saying you want to have the count for each "week number"?

If so, one way to do this is to first create your query to get the week
number, then convert it to a Totals query, GroupBy weeknumber and Count a
field that would hold unique values (say, an ID field).

Regards

Jeff Boyce
<Office/Access MVP>