Report shows ID of field, rather than data in field

[badbox29]

I am working on a report and have found a problem. I have a field called "region". The data is geographical and is basically stuff like "southeast", "mid-atlantic", "southwest", etc. I reference this field in the report but the report shows the ID field of the table where this data exists, rather than the data in the "region" field. So, instead of "southeast", it shows "3". Here's a screenshot. Any tips as to what caused this to happen?




Chris

[RuralGuy]

I suspect you are using Lookup Fields: http://access.mvps.org/access/lookupfields.htm
Base your report on a query that joins the two tables on the Region table and then use the descriptive field instead of the ID field in the report.

[badbox29]

Thanks, RuralGuy. Not sure I follow. The way it is laid out now, I am using a lookup field. I have a query that uses three fields in a table called (Service Details). The three fields are "Last/First", "Hour Performed", and "Region". The "Region" field is a lookup field that hits a field called "Region" in a table called "Regions". How would I construct a query to do what you say?

[RuralGuy]

Add the Regions table to your query and use the description field instead of the ID field.

[badbox29]

I did this, but the resulting report was blank and showed an error where the region ID # used to be... I assume I did something wrong.

[badbox29]

If it helps, here is a copy of the database. I have to compress it to get it on here (it's over 500kb). The report in question is named "Average Hours Per Region".. It uses the only two queries in the database. The "Find Duplicates for Service Details" query identifies scholars with multiple community service records. The "Sum of hours performed by individual" query gives me a sum of all the records for each user. The report then takes that information and presents it. However, it's using the ID field of the Regions table, as you are aware.

I appreciate the time, sir. I will happily make note of whatever you say I must do to get that report squared away:-)

Volunteer Competition.zip

[RuralGuy]

Are there no test records in this db?

[RuralGuy]

Okay, I found them. Boy those Lookup Fields can really screw up a database.

[badbox29]

Phew! Yeah, I'm learning lookup fields are pretty terrible. Next step for me is learning how to not use them;-)

Thanks for looking into this!!

[RuralGuy]

Still shifting things around to make it work. Keep the faith.

[RuralGuy]

It is a long way from perfect but I made a few changes. I revised things so the [Average Hours...] report would use the [Copy of...] queries and tables. The [Copy of Service Details] has the relationship with the [Regions] table set up correctly and the [Region] field in the [Copy of Service Details] is no longer a Lookup Field. You can now see the intermediate queries return records for the [Copy of...] queries and hopefully this shows you how to set up the lookup's. Post back with questions where it does not make sense to you.

[badbox29]

It is a long way from perfect but I made a few changes. I revised things so the [Average Hours...] report would use the [Copy of...] queries and tables. The [Copy of Service Details] has the relationship with the [Regions] table set up correctly and the [Region] field in the [Copy of Service Details] is no longer a Lookup Field. You can now see the intermediate queries return records for the [Copy of...] queries and hopefully this shows you how to set up the lookup's. Post back with questions where it does not make sense to you.

That's perfect, thanks! I think I do see how this works a bit more clearly, now. The only thing I ran into is when I enter a new person. If only one entry exists for a region, it won't show up on the report. I changed "In (SELECT [Region] FROM [Copy of Service Details] As Tmp GROUP BY [Region] HAVING Count(*)>1 )" to "In (SELECT [Region] FROM [Copy of Service Details] As Tmp GROUP BY [Region] HAVING Count(*)>0 )" in the "Copy of find duplicates for Service Details" query and that seems to allow these entries to appear on the report. I don't see anything broken in this report from that change. Can you see where this change might cause any problems?

[RuralGuy]

Can you see where this change might cause any problems?

Not at this time. Good luck with the project.