sorting data in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In some of my reports, I am unable to get the "Client name" field to sort
alphabetically in spite of having specified this when ceating the report
through a wizard and also sorting the field in the query on which the report
is based. The report always sorts according to the client code which is
numerical, even if the client code does not appear anywhere in the query. The
"client name" field is a lookup in the original table. Can anyone help ?

Many thanks,

David S
 
Go design view and click on the Grouping and Sorting button on the toobar at
the top of the scree. Set sorting here.
 
Thanks PC but I've tried that and it's set correctly to "Client name" but
still sorts according to the client code number
 
David said:
In some of my reports, I am unable to get the "Client name" field to sort
alphabetically in spite of having specified this when ceating the report
through a wizard and also sorting the field in the query on which the report
is based. The report always sorts according to the client code which is
numerical, even if the client code does not appear anywhere in the query. The
"client name" field is a lookup in the original table.
Click to expand...


THe only reliable way to sort a report is to specify the
sorting field(s?) or expressions using the Sorting and
Grouping window (View menu).
 
Marshall Barton said:
THe only reliable way to sort a report is to specify the
sorting field(s?) or expressions using the Sorting and
Grouping window (View menu).
Click to expand...
Tbnaks but this doens't help
 
Don't use lookup fields. Make sure the "looked up" table is in your report's
record source so that you can add the "real" field with the "real" value
into your report for sorting.

I expect you might have also used the caption property of the field in the
table design to hide the real field name.
 
Thanks for the ideas. I made a mistake in my original post, the field is not
a lookup, apologies. I am using the correct field name and the queryt on
which the report is based has "Client name" first with the query results
sorted in alphabetical order, it's just the report bawed on the query sorts
according to the client code, not alphbetically. I don't always have this
problem with other reports, some sort aplhbetically, some by code. It's most
frustrating as I can't see why !
 
You are referring to sorting in a report. Several replies suggest the
sorting in the query is worthless and you need to sort in the report design.
Yet, you reply back "query results sorted in alphabetical order" and "query
sorts according to the client code".

Did you attempt to sort your report using the sorting and grouping dialog in
the report?
What are all of the Sorting and Grouping expressions/fields in the dialog?
 
Hi Duane,

The query on which the report is based has "client name" sorted
alaphabetically. When I run the report, the client name field is sorted
according to the client name numeric code which is the unique ID on the
client details form but it not a field in the query.

When I set up the report via a wizard, I specified that the client name be
sorted alphabetically. When I check the report in design view, the
sorting/grouping expression is correct, just the one which is "client name".
It must be because of the ID but this does not happen in every report which
contains "client name" as a field.

Thanks for your time.

David
 
Please stop referring to the sort order of the query since it only causes
confusion.

If you add a red text box to your report's detail section and set its
control source property to the same field used in your sorting and grouping
dialog, what appears in the report, the name or a number?

--
Duane Hookom
MS Access MVP
--

David said:
Hi Duane,

The query on which the report is based has "client name" sorted
alaphabetically. When I run the report, the client name field is sorted
according to the client name numeric code which is the unique ID on the
client details form but it not a field in the query.

When I set up the report via a wizard, I specified that the client name be
sorted alphabetically. When I check the report in design view, the
sorting/grouping expression is correct, just the one which is "client
name".
It must be because of the ID but this does not happen in every report
which
contains "client name" as a field.

Thanks for your time.

David
Click to expand...
 
I get the number

Duane Hookom said:
Please stop referring to the sort order of the query since it only causes
confusion.

If you add a red text box to your report's detail section and set its
control source property to the same field used in your sorting and grouping
dialog, what appears in the report, the name or a number?
Click to expand...
 
If you get the number then you are using the wrong field. Find the field in
your tables that is a Text data type and contains the text value you want in
your report. Add this table/field to your report's record source so that you
can use it for sorting and grouping.

It surely sounds like you are using a lookup field.
 
Hi Duane,

I'm beginning to understand the problem but not how to resolve it.

I've been confused, the client name field is a lookup on the table on which
the query is based, this is for ease of entering the data. The problem is
that I can't change the field in the query without losing the client name
data I need for the report. I have tried adding the table with the client
name as text to the query but then I don't get any data when running the
query. I also have tried removing the client ID part of the lookup in the
row source on the report but then I again lose the data on the report.

Any thoughts gratefully received.
 
Why don't you provide information about your table structures and
relationships? So your field is a lookup even though you stated "field is
not a lookup"?
 
Apologies, I am somewhat new to this and the database has quite a few tables
and umpteen queries/reports.

Relationships are as follows:

Most information on clients is in the "Clients" table.

I have another table called Power of Attorney where I record details of
powers issued to attorneys by the clients.

The "client name" field is a lookup controlled by the "client name" field in
the "Clients" table.

I have a few reports based on the Powers of Attorney table such as current
powers, powers to expire by a certain date etc.

Hope this is clear.
 
You should be able to combine the Clients table and Power Of Attorney table
in a single query. Then add the appropriate field(s) from each table into
the grid so you can display their values in the report.
 
Hi Duane,

I posted a reply last Friday but it does not seem to have come up so here it
is again.

Apologies, I'm not too experienced with this and there are quite a few table
with umpteen queries/reports. Here is a description of the relationships:

Client data is kept in the "Clients" table via a form. There is a table
where I record details of all powers of attorney issued by clients. In this
table, the client name is a lookup based on the "Client name" field in the
"Clients" table.

From the "Powers of Attorney" table, I have a report based on a query which
gives me current powers of attorney (i.e. those issued by not yet expired)
and it this this one which will not sort the client names.

From what you have said, if the filed is a lokup, it can't be sorted
alphabetically, but I don't see that there is any other option as putting in
any other control source doesn't give the client name at all.

Your thoughts would be greatly appreciated.
 
Hi Duane,

I have already tried this but just adding the "clients" table to the query
without even selecting the client name field returns no data when running the
query.
 
Can you view the SQL of your report's record source and copy it into a
reply?
Do you still have any "lookup fields" in your table designs? If so, can you
find the properties of these fields to share with us?
 
Hi Duane,

Here is the row source on the current powers report:

SELECT [Clients].[Client Code], [Clients].[Client name] FROM [Clients] ORDER
BY [Client name];

Here is the client name lookup field on the powers of attorney table:

SELECT [Clients].[Client Code], [Clients].[Client name] FROM [Clients] ORDER
BY [Client name];

I suspect the problem is the "client code" part but I can't get rid of it
without losing that data so it must be necessary.

best regards,

David

Lookup for officer's name, office being the appointed attorney on the powers
of attorney form:

SELECT [Officers].[Officer Name] FROM [Officers]
 
Back
Top