A2K Changing grouping on a report gives erroneous result

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

Re: A2K Changing grouping on a report gives erroneous result

"Deano" wrote in
news:5nig0oFig1jsU1@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

Re: A2K Changing grouping on a report gives erroneous result

On Tue, 16 Oct 2007 01:50:08 +0100, "Deano"
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
>
>

Re: A2K Changing grouping on a report gives erroneous result

"Tom van Stiphout" wrote in message
news:tdb8h3tgptph3inhrvbuvo6qcb87soq7vg@4ax.com...
> On Tue, 16 Oct 2007 01:50:08 +0100, "Deano"
> 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!