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];
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]
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]
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?)
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.