Report Sorting

[Darknight850]

Hello All,

I have a question about sorting in a report. Right now I have multiple reports sorting off of my companies table that has two fields; CompaniesID, and Company Name. Well the problem I am running into is that I constantly add new companies into this table and the CompaniesID is an auto number that currently all the reports are sorting off of, what I want to be able to do is sort by Company name in a report rather then CompanyID, but every time I try it doesn’t show the company at all, shows the CompaniesID not the Name, or puts everything together under the first company and doesn’t show everything else. I have other tables that are linked off of the CompaniesID, I am using the auto number because it was recommended to me, but there has to be a way to sort from the Company Name with out having to renumber all the companies every time I have to add one.


Thank You,
Sean

[NeoPa]

There is indeed fairly full support for this in Access reports. Look at Sorting and Grouping within your report.
Let us know how you get on :)

[Darknight850]

i can only sort by CompaniesID because that is whats linked to the companies table. then in the report i have the companiesId in a combo box with a width of 0".

[NeoPa]

Well, I'm working blind as you haven't shared much information, but why can you not sort on any field that is in the query? The facility is there.

[Darknight850]

the field isn't in the query, only companiesID is. here is how my tables are set up:

tblAll:
EmployeeID - PK - Auto Number
Company- FK
Name
Job Title
Id Color - FK -
Date ID Issued
Date ID Expired
Date Started Work

tblCompanies:
CompanieID - PK - Autonumber
Company Name

tblIDtype:
IDTypeID - PK - AutoNumber
ID Type

[JKing]

Why not just create a query that includes more than one table? This would allow you to use the company name field in sorting and grouping. I added a link to an article detailing how to write SQL for multi-table queries.

SQL Joins

[NeoPa]

Light is beginning to dawn :)
I would not even have considered adding that info via a ComboBox, but now I understand what you're trying to do I would agree that JKing's solution is ideal for you.
Let us know how you get on with that.

[Darknight850]

That helped a lot thank you, sorry for such the long response time, i had been gone for awhile, but that worked great. thank you again.

[NeoPa]

No problem, I'm glad we were able to throw some light on the matter for you.
Well done JKing (Excellent choice of link BTW ;))