Specify Bound Column in a Report

My Access Table:
I have created a Table called 'Jobs'
One of the fields in this Table is called 'Company' and I wanted to limit the choices to a list, so in Design view I select that field, and in the Lookup tab I set the Display Control to a Combo Box, the Row Source Type is a Query, and in the Row Source setting, I have the actual query:

SELECT [Companies].[ID], [Companies].[CompanyName] FROM Companies ORDER BY [CompanyName]; 

Open in new window


In the drop-down list, I set the column count to 2, but because the user does not need to see the first column, I set the width of column 1 to "0" and the width of column 2 to "1.1".  So when the user clicks on the drop-down list all they see is the Company Name, but the Bound Column is column 1 (the ID)


My Access Form:
Next, I created a Form (also called 'Jobs') and the Record Source for the Form is the Jobs table described above.
On the Form, the 'Company' field is a drop-down list and sure enough, its values are populated from the same query and the drop-down list acts and behaves exactly like it does if you were to view the above Table in datasheet view.
OK, so far so good.


My Access Query:
Next, I created a Query (called 'All Jobs') and the only table used in this Query is the 'Jobs' table mentioned above. (I also have added some calculated fields to the Query but that's probably irrelevant). Since the query is based off of the table, and the table uses a lookup for the Company field, I notice that when I view the Query results in Datasheet View, that drop-down list appears here as well. Since this is a Select query, I kinda expected that the query result would just show me the value, not a value inside of a drop-down list. (What's a drop-down list doing in a SELECT Query anyway? That's kinda weird.). In any case the whole point of my creating this Query is so that I could include some calculated fields and then use this Query as the data source for my report.

ok so finally ... on to my problem (and more questions) ...


My Access Report:
Next I created a Report (called 'All Jobs') and the data source for this report is coming from the Query above. The report appears to be working perfectly and everything I want to display on the report has been added. I notice that when I add the 'Company' field to the report, in Design View it's a drop-down control. (This thing won't go away). Again, seems weird that a drop-down control is appearing on the report (in Design View - when I print it or preview it on screen, of course all I see is the value, not the drop-down control itself. And that's the point - the report only needs to show me the value, so why is it happily showing me a drop-down box? Maybe it's bothering me more than it should).

The report is grouped by Company.  In the Company Footer I have some calculated fields which show things like Total Number of Jobs and Total Cost for Company A.

OK here's where I need help.
In the Company Footer, I created a text box.
In the Control Source property of this text box, I entered:
="Total for " & [Company]

Open in new window


When I view the report, all I see is  "Total for ".
The Company name does not appear here, as I would expect. No error.

Now, if I were to just drag the Company field into the footer by itself and then preview the report, the Company Name shows up just fine:    "Company A"

I am trying to concatenate a string of text + the value in the Company field.

I don't understand why
="Total for " & [Company]

Open in new window

does not work.

Does this have to do with the fact that the report is based on a query, which in turn is referencing a table which utilizes a drop-down list for that field, and there's some additional criteria I need to specify in order for Access to understand which column I am trying to concatenate? (Remember, the query that is used to populate the drop-down list returns 2 columns and it's the second column (the Company Name) that I am trying to display here. Do I need to specify Column 2 in my formula here?)


Final question:
I mentioned that the report is grouped by Company.
In Design View of the report, I click on the 'Group & Sort' button in the ribbon, and at the bottom of the screen I see:
"Group on Company | from smallest to largest | by entire value | with no totals | with title (click to add) | with a header section | with a footer section | do not keep group together on one page"

So here you can see "Group on Company from smallest to largest"
I expect the Company names to be grouped in alphabetical order. But they are not. As you can probably guess, what Access is doing is it is sorting the Company groups by Company ID, not by Company Name.

How do I get Access to group my report by Company Name, not by Company ID?

Waaaaay back at the start, when I created the Jobs table and I limited the values of the Company field to a query which returns two columns - the ID and the Company Name ... maybe I should have not even included the ID. Maybe I should have just did a query to return a single column (the Company Name) and that would have solved both of my issues here.
LVL 11
ecarboneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
combos on Tables are more trouble than they are worth.  You've only found a minor issue.  There are many more.

Start by removing the combo from the table.  Then change your select query to join the Jobs table to the Company table.  Choose the CompanyID from the Jobs table and the CompanyName from the Company table.  On your report, change the control type from combo to text and change the control source from CompanyID to CompanyName.  The form is fine because that's where you want the combo.

Having the combo show up when you open the table in DS view or in a query is a crutch for YOU.  The user should NEVER have direct access to tables or queries.  Users only interact with forms and reports.   Unless you are testing, you shouldn't be entering data directly into tables or via queries, even you should be using forms where all the validation takes place so removing the combo shouldn't be a burden to you.  Simply use a query with a join if you want both the ID and the name in the same recordset.

Recomendation - If you made the mistake of using the CompanyName name in your Jobs table, change the Jobs table to use the PK name.  The FK and PK names should be identical unless they need to be different because you have two FKs to the same PK in the same table as you might if you had billing and shipping address so you had two FKs to the State table.  This happens also in self referencing tables such as employee table because you have the EmployeeID which is the PK of the table but you also have SupervisorID which is the EmployeeID of a different record.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ecarboneAuthor Commented:
Pat,

A BIG BIG thanks for your response. Combo boxes in my queries and reports were driving me crazy because I knew they didn't belong there - they don't even make sense being in there.

They wound up in there because my report was based off of a query, and the query was based off of a single table where I used the Lookup Wizard. I now know not to do that.

I spent a few hours "undoing" all of that, as per your instructions. The end result is a query that has more than one table (using Joins) and a report that does not have combo box controls (as it should be). And once I linked everything back the proper way, I can now easily add the fields I want in the report.

Looking back, I wonder why Microsoft even allows you to do this. You end up running into a brick wall. I can see how the Lookup Wizard and Query Wizards can be helpful to beginners, but Access should then wire everything up properly behind the scenes.

This was a great learning experience. Thanks again!
0
PatHartmanCommented:
You're welcome.  There were several "features" added for A2007 that experts refuse to use due to the problems they cause once you attempt to use them in queries and VBA.  Good you discovered this early.  It can be a real PITA to unwind when they're everywhere.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.