Link to home
Avatar of E C
E CFlag for United States of America

asked on

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.
Avatar of PatHartman
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of E C



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!
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.