Going Crazy; Group headers in report not working

[dogmelissa]

I have a bunch of reports and all of them are working fine except one...

I'm trying to find jobs by customer's last name. The query uses this as
search criteria: Like "*" & [What is the last name you are looking for?] &
"*" under the field of LastNames. When I run the query by itself, with the
criteria "bailey", I get 3 records, 2 different results: Judy Bailey & Keith
Bailey.
Problem is when I run the report for this query, I have it set to Sort by
LastNames, with a Group Header, and also sort by job#, with no header for
job#. In design mode, I have a field of LastNames (text box) under the
LastNames Header. However, when I run this report with the same criteria as
above, I get all 3 job #s, but only one heading; Keith Bailey. I can't figure
out what's gone wrong, especially since I have 2 other reports set up to do
this on different criteria, and they're working great.
Anyone have any ideas?

Thanks!
Melissa

 

[dogmelissa]

Correction.... it's not getting confused about the set up.. it's confused
because the last names of these 2 clients is the same, and it doesn't know
how to separate them. I have added a FirstNames box to the LastNames header,
but do I need to make it sort on first names, too, in order to get this to
work?
Thanks!

 

[Graham Mandeno]

Hi Melissa

Does your table have a primary key? (It should do!)

The trick it to add an extra group level - LastName at the top level and
then your primary key. Then add a group header for the PK level but not for
LastName. The result is that you sort by LastName (because it's the top
level) but you also get a new header for each record (because the primary
key has changed).

 

[dogmelissa]

Yes I have a primary key, but this method would result in a complete change
of the way my report looks. I don't want a new header for each record (some
reports have 100+ records!). I'm going to play a little bit with sorting by
first name and see how that works.
Thanks anyhow,
Melissa

Hi Melissa

Does your table have a primary key? (It should do!)

The trick it to add an extra group level - LastName at the top level and
then your primary key. Then add a group header for the PK level but not for
LastName. The result is that you sort by LastName (because it's the top
level) but you also get a new header for each record (because the primary
key has changed).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Correction.... it's not getting confused about the set up.. it's confused
because the last names of these 2 clients is the same, and it doesn't know
how to separate them. I have added a FirstNames box to the LastNames
header,
but do I need to make it sort on first names, too, in order to get this to
work?
Thanks!