A2K Changing grouping on a report gives erroneous result

[Deano]

I have a nice report that uses sql to report employee absences.

I grab the employee and related absence data using the employee id field
which is unique.

I use the employee id as a header on the report and the absence data is in
the Detail element.
The problem
-------------
This gives me a report that appears jumbled up to the user but correct in
every respect. Now if I want to sort by the employee surname I can change
the header to the surname field but if there are two Smiths for example
their absence data becomes merged under the name of the first Smith.

What I need
-------------
I want my absence report to be grouped by each individual Surname, showing
absence data for each person, so that the report appears sorted by Surname.

Any advice about this? Since I allow duplicate surnames to be entered, when
I come to group by surname I get data grouped incorrectly. Is there
something I can do with the group header sorting to distinguish each person
e.g John Smith from Fred Smith.
thanks
Martin

[Tom van Stiphout]

On Tue, 16 Oct 2007 01:50:08 +0100, "Deano" <de***@mailinator.com>
wrote:

Eh, group by FullName?
FullName is a calculated column in your query, and the concatenation
of LastName & " " & FirstName.
If you suspect over time you may have two John Smith, then append the
PersonID.

-Tom.

>I have a nice report that uses sql to report employee absences.

I grab the employee and related absence data using the employee id field
which is unique.

I use the employee id as a header on the report and the absence data is in
the Detail element.
The problem
-------------
This gives me a report that appears jumbled up to the user but correct in
every respect. Now if I want to sort by the employee surname I can change
the header to the surname field but if there are two Smiths for example
their absence data becomes merged under the name of the first Smith.

What I need
-------------
I want my absence report to be grouped by each individual Surname, showing
absence data for each person, so that the report appears sorted by Surname.

Any advice about this? Since I allow duplicate surnames to be entered, when
I come to group by surname I get data grouped incorrectly. Is there
something I can do with the group header sorting to distinguish each person
e.g John Smith from Fred Smith.
thanks
Martin

[Deano]

"Tom van Stiphout" <no*************@cox.netwrote in message
news:td********************************@4ax.com...

On Tue, 16 Oct 2007 01:50:08 +0100, "Deano" <de***@mailinator.com>
wrote:

Eh, group by FullName?
FullName is a calculated column in your query, and the concatenation
of LastName & " " & FirstName.
If you suspect over time you may have two John Smith, then append the
PersonID.

-Tom.

Thanks Tom, yes I was thinking about that but not sure what to enter in
Field/Expression when I go into Sorting and Grouping.

=[Surname] & ", " & [Forename] seems to work just great.

Thanks!

[Bob Quintal]

"Deano" <de***@mailinator.comwrote in
news:5n************@mid.individual.net:

I have a nice report that uses sql to report employee absences.

I grab the employee and related absence data using the employee id
field which is unique.

I use the employee id as a header on the report and the absence
data is in the Detail element.
The problem
-------------
This gives me a report that appears jumbled up to the user but
correct in every respect. Now if I want to sort by the employee
surname I can change the header to the surname field but if there
are two Smiths for example their absence data becomes merged under
the name of the first Smith.

What I need
-------------
I want my absence report to be grouped by each individual Surname,
showing absence data for each person, so that the report appears
sorted by Surname.

Any advice about this? Since I allow duplicate surnames to be
entered, when I come to group by surname I get data grouped
incorrectly. Is there something I can do with the group header
sorting to distinguish each person e.g John Smith from Fred Smith.
thanks
Martin

In design view, open the report's sorting and grouping dialog.
add a second group, Firstname. and even a third group the Employee
ID.

Cut and paste the existing header to the lowest group created above.
then go back and set the Group Header tickboxes for the Higher
groups to no.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com