Cannot Sort by Company Name

[Guest]

I use Access 2000.

I have a Meetings table that is used as a source to produce various reports.
One of the field's name is MM_CoName. This field is derived from a query
based on tb1Company table. Because there are Company Names that are
identical, it was necessary to show also the Company's address, city, and
zipcode. Therefore, MM_CoName is made of the the following 5 columns:

1 2 3 4 5
CompanyID, CC_CoNAME, CC_ADDRESS1, CC_CITY, CC_ZIP

The bound column is column 1. The 1st column is not viewable (it has a
length of 0." All the other columns are viewable from a drop down combo box.
When I chose a company from the combo box, the list is in CC_CoName A to Z
alphabetic order. So far , so good. However, when I use the Meetings table
in a report, I only want the Company Name to show (none of the other columns)
and I want the report to list these companys in Company Name A to Z order.
When I produce the report, I show only the Company Name (as I desired)
however, the list is sorting by the CompanyID. Can anyone explain how I
might accomplish sorting by Compamy Name instead? Thanks in advance!

 

[Rob Parker]

To ensure that data in a report is sorted as you desire, you need to use the
Sorting/Grouping dialog in the report design to set the sort order. A
report may be sorted by the sort on its recordsource query, but you should
not rely on it; if the report's recordsource is a table, it has no sort
order at all, and will probably default to sorting on the primary key
(perhaps here it's CompanyID).

HTH,

Rob

 

[Al Campagna]

nytwodees,
Sorting in reports is controlled by the Grouping and Sorting Dialog box, not the query
behind the report.
Place whatever fields you need to get the correct sort in there.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

I do have the sorting on MM_CoName in the "Sorting and Grouping" section of
the report. As I said earlier it sorts on the CompanyID column of the
complex MM_CoName field. Solution still not found!

 

[John Vinson]

I do have the sorting on MM_CoName in the "Sorting and Grouping" section of
the report. As I said earlier it sorts on the CompanyID column of the
complex MM_CoName field. Solution still not found!

Have you fallen victim to the infuriating Lookup Wizard misfeature?

This effect will happen if you have a Lookup Field for the company on
your report; it actually contains the CompanyID, even though what you
*SEE* is the company name. So the sorting will be by what's actually
there, not what you see!

Instead, create a Query joining the Company table to your main table;
select the Company name from the Company table field; leave the
CompanyID (in both tables!) out of the query; and use the CompanyName
in the sorting and grouping.

John W. Vinson[MVP]

 

[Rob Parker]

Sorry, misunderstood your problem.

If all you want to show in the report is the company name, then you don't
need the constructed field MM_CoName in your report's recordsource; all you
need is CC_CoNAME (and, of course, CompanyID, since that (I assume) is the
key field - it's what you are using as the bound field in your combo-box,
and is therefore the value which is being stored in your underlying table).
Sort in the report by CC_CoNAME.

Or, if you do need the other data (address, city, zip) displayed in a single
field in the report, then change the definition of MM_CoName to exclude the
CompanyID field.

HTH,

Rob

 

[John Vinson]

Thank you for your reply.

I guess I have fallen victim to the infuriating Lookup Wizard misfeature!

I did what you suggested. The query as well as the report lists all the
fields but the Company Name field "appears" blank. What gives?

Please open your Query in SQL view and post it here. Does the Company
table display the company name correctly?

John W. Vinson[MVP]

 

[John Vinson]

Thanks again for your help. The SQL view is as follows:

SELECT tb1Company.CC_Name, tb3Meetings.MM_Year, tb3Meetings.MM_BoothType,
tb3Meetings.MM_ShowCompanyType, tb3Meetings.MM_ElecRequirements,
tb3Meetings.[MM_Water Requirement]
FROM tb3Meetings LEFT JOIN tb1Company ON tb3Meetings.MM_Name =
tb1Company.CC_Name
WHERE
(((tb3Meetings.MM_Year)=[Forms]![MeetingYearDialogBoxForm]![TradeShowYearInput]))
ORDER BY tb1Company.CC_Name;


Results from the query:
tb3Meetings tb1Company
Attending Company(MM_Name) Company Name(CC_Name)
Shows All Names Shows Blanks

Well, maybe you know what these fields contain in the tables, but I
don't! My suspicion is that the problem arises because you're joining
MM_Name to CC_Name. The join to tblCompany should be joining the
Primary Key field of tblCompany - whatever that is - to the
corresponding foreign key field in tb3Meetings. If either of these is
a Lookup field, just be sure you're joining the "real" table field,
not the looked up value.

John W. Vinson[MVP]