Report Grouping Ruining Detail Sort

  • Thread starter Thread starter Ken M via AccessMonster.com
  • Start date Start date
K

Ken M via AccessMonster.com

I have a report of an audit trail of logins. The Query does not sort but the
records are in TIMESTAMP order.

Display fields:
[TIMESTAMP] in mm/dd/yyyy hh:nn:ss AM/PM (Default format no Format command
necessary)
[USERID]
[DOW] reformated from [TIMESTAMP] - Format([TIMESTAMP],"ddd")
[TimeRange]: Select Criteria: Format([TIMESTAMP],"Short Date")

I generate a report based on this Query. If I only have the report Page
Header and Detail with the Detail containg the [DOW] [TIMESTAMP] [USERID]
fields all records sort in Ascending order starting from 12:00 AM. However as
soon as I create a grouping TIMESTAMP - Ascending and add a Group Header that
Groups on Hour with a Group Interval of 1, the Detail Sort no longer sorts
correctly within each hourly grouping. (Grouping on Each Value does not
affect the Detail Sort)

I have tried working with the Query, the Group On (Hour,Day, Week), and Group
Interval with no luck.

Any suggestions would be helpfull.
 
What exactly are your sorting and grouping levels? Can provide something
like:

Field/Expression Other Properties
============= ===============
=DateValue([TIMESTAMP])
=....
[TIMESTAMP]
 
I finally got the report to group and sort correctly:

Here is the Query:

SELECT Format([TIMESTAMP],"ddd") AS DOW,
Format([TIMESTAMP],"Short Date") AS DT,
Format([TIMESTAMP],"hh") AS HRS,
USERNAME,
TIMESTAMP
FROM AUDIT_SESSION
WHERE TIMESTAMP Between [txt_StartDt] And [Txt_EndDt];

The Report Fields are:
------------------------------------------------------------------------------
------
HRS Header:
[DOW] [DT] [HRS]&":00"
Detail:
[TIMESTAMP] [USERNAME]
HRS Footer:

DT Footer:
------------------------------------------------------------------------------
------

In the report the Groupings are:

Group1: DT Ascending
Group Header - NO
Group Footer - YES
Group On - Each Value
Group Interval - 1

Group2: HRS Ascending
Group Header - YES
Group Footer - YES
Group On - Prefix Characters
Group Interval - 2

Group3: TIMESTAMP Ascending
Group Header and Footer - NO
Group On - Each Value
Group Interval - 1

The report now will break on each Day and Each hour and maintain the
TIMESTAMP Sort.


Duane said:
What exactly are your sorting and grouping levels? Can provide something
like:

Field/Expression Other Properties
============= ===============
=DateValue([TIMESTAMP])
=....
[TIMESTAMP]
I have a report of an audit trail of logins. The Query does not sort but
the
Click to expand...
[quoted text clipped - 23 lines]
Any suggestions would be helpfull.
Click to expand...
Click to expand...
 
You can also use expressions in the Sorting and Grouping dialog in report
design.
=Format([TIMESTAMP],"ddd")
Make sure you place "=" at the beginning of your expressions.

--
Duane Hookom
MS Access MVP
--

Ken M via AccessMonster.com said:
I finally got the report to group and sort correctly:

Here is the Query:

SELECT Format([TIMESTAMP],"ddd") AS DOW,
Format([TIMESTAMP],"Short Date") AS DT,
Format([TIMESTAMP],"hh") AS HRS,
USERNAME,
TIMESTAMP
FROM AUDIT_SESSION
WHERE TIMESTAMP Between [txt_StartDt] And [Txt_EndDt];

The Report Fields are:
------------------------------------------------------------------------------
------
HRS Header:
[DOW] [DT] [HRS]&":00"
Detail:
[TIMESTAMP]
[USERNAME]
HRS Footer:

DT Footer:
------------------------------------------------------------------------------
------

In the report the Groupings are:

Group1: DT Ascending
Group Header - NO
Group Footer - YES
Group On - Each Value
Group Interval - 1

Group2: HRS Ascending
Group Header - YES
Group Footer - YES
Group On - Prefix Characters
Group Interval - 2

Group3: TIMESTAMP Ascending
Group Header and Footer - NO
Group On - Each Value
Group Interval - 1

The report now will break on each Day and Each hour and maintain the
TIMESTAMP Sort.


Duane said:
What exactly are your sorting and grouping levels? Can provide something
like:

Field/Expression Other Properties
============= ===============
=DateValue([TIMESTAMP])
=....
[TIMESTAMP]
I have a report of an audit trail of logins. The Query does not sort but
the
Click to expand...
[quoted text clipped - 23 lines]
Any suggestions would be helpfull.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top