Lookup table help in Access 2007

Factual Questions
1

I’m pulling my hair out here as I am right at the end of a project and I’m stuck like a chump on a small thing.

I created a lookup table for one column of data call LETTERS. We will say that in the lookup table ID=1 DATA=A, ID=2 DATA=B, ID=3 DATA=C for this question.

The actual drop down box in my main table shows “A, B, C” as expected and when I run a report using the report wizard and don’t break the report into sorted Grouping Levels the report prints “A, B, C” as expected.

But as soon as I choose to add Grouping Levels by LETTERS all of the sudden it shows “1,2,3”, the ID numbers of the letters instead of the actual LETTERS on the report. I can’t figure out why and I’m fucking furious because it’s not clear why it would do this.

Can someone help me out here? I’m sitting in Afghanistan trying to figure this POS out and I want to be done.

2

I tried setting up a database to match yours, added a grouping level to the reports, and used the “Add Existing Fields” button (I’m in Access 2007) to add the LETTERS field to the report. It showed the letter values, not the ID.

Then I tried adding an unbound field to the report, then setting the Control Source to LETTERS. This time it showed the ID. I’m guessing this is how you set up the field?

Looking at the property differences, the field that shows the letters has a Row Source of:

SELECT [Table1].[ID], [Table1].[LETTERS] FROM Table1;

And the bound column = 1. This means the ID is used to bind the column, and the LETTERS column is displayed.

Give this a try and see if it fixes your issue.

3

Ok. I can try to do that when I get back to the computer tomorrow. I just went along through the Report Wizard and picked all of the simple options. I did it repeatedly and it did the same thing every time.

Thanks for the help. Is the report designer as painful to use as it looks?

4

More painful than a wizard, to be sure. But obviously more powerful too.

If you have the field on the report showing the right data, you should be able to just copy and paste the Control Source property to your grouped field. Otherwise paste in what I have above, changing the appropriate field and table names. If neither of those work, then you’re stuck digging into how those properties work, and that can be painful if you haven’t done it before.

Good luck.