Grouping and Sorting Problems

[breeden.christopher]

I have a report that I have grouped by [Date] per week. The problem
is that it isn't grouping by seven days. What I have done in the
Group Header is DateAdd("ww",1,[Date]), but the begining date and end
date do not correlate. I get something like this:

Sep 03 - Sep 10
Sep 11 - Sep 18 + 8 days
Sep 18 - Sep 25 + 7 days
Sep 24 - Oct 01 + 6 days

Anyways the grouping isn't consistent. It goes anywhere from gouping
for 4 days to 8 days. I have records for each day of the week, but I
was thinking maybe that was part taking into my problem, so maybie
there is a way for it group my records by [date] that fall with in the
weekly inteveral starting on the date of the first record?

 

[Allen Browne]

Something else may be happening if your date/time fields have a time
component that pushes them outside a particular range, so how about grouping
on this expression:
DatePart("ww", [Date])

If you are doing this in a report, you could just choose the date field in
the Sorting And Grouping box. In the lower pane of the dialog, you can then
set the interval to Week.

(Hopefully your field is not really named Date.)

 

[breeden.christopher]

Right I am doing this in a report and I did have a field named Date,
so I changed it to [Date Opened] to rid of any ambiguity.

So now I have a report with Grouping and Sorting of the [Date Opened]
field on weekly intervals. I believe I have a record with each date
but I would really like to ensure that it does this independently
whether or not I have one in that range or not. (Based on your
response, I'd think I'm right.)

Basically I have this report that shows me how many work orders have
been opened, closed and were pending during that week. So I used the
following algorithms:

Open: =Count(*)
Close: =DCount("[Date Closed]","AFN Work Request","[Date Closed] >= #"
& [Date Opened] & "# AND [Date Closed] < #" & DateAdd("ww",1,[Date]) &
"#")
Pending: =DCount("[Date Opened]","AFN Work Request","[Date Opened] <=
#" & DateAdd("ww",1,[Date]) & "# AND ([Date Closed] Is Null OR [Date
Closed] >= #" & DateAdd("ww",1,[Date]) & "#)")

Now this really shouldn't mess with the grouping, but even after
trying what you have said the grouping is still inconsistent :\

 

[Allen Browne]

To start with, just to verify that the name change is complete, uncheck the
boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

Now you are trying to verify this with counts. Not sure I follow.

Count(*) will yield the number of records. There could be multiple records
for a date, or there might not be a record for a date, so the number will
not necessarily be 7 for a week.

Similarly the DCount() can go wrong in several places:
a) There may not be records for each day.
b) Explicitly format the literal date (in case a user's Regional Settings
are non-US.)
c) You still have the [Date] field in those expressions.

 

[breeden.christopher]

Right. I was using the [Date] feild as my assumption for the first
date of the week for that particular grouping--apparently this is not
so. I used the Count(*) function to count how many records there are
with in the group, hence, being how many work orders have been opened
during that week. So most of the time there are more than one a day,
more like 4-5. So I was thinking, I'm was trying to count how many
weeks I have per month and I've been counting 4. I'm thinking that
the Grouping and Sorting could be grouping correctly but it may be
displaying improperly. This is what I have in the Group Header:

=Format$([Date Opened],"mmmm"", ""dd",0,0) & " - " & Format$
(DateAdd("ww",1,[Date Opened]),"mmmm"", ""dd yyyy",0,0)

I'm looking to put: Firsrt Day of the Group - Last Day of the Group
(Which should also be 7 days after the first one.)

So is there something else I'm supposed to use for the first date of
the group and the last?

 

[Allen Browne]

The only safe way to ensure that there is a record for every date (even if
there is no data for the date) would be to use a table of dates that
contains a record for every date. You would need to outer join this to the
DateSerial() value of the date in your other table, and group on that.

Now the first date of the week will be the date in the first record, and the
last day of the week will be that date plus 6.