Multiple Lookups in one report

[hansdebadde]

I have a report i am creating useing Access 2010 and my data is on a SQL 2008 R2 backend. The main table for the report tbl_CB_or_CA has multiple lookups in it, they are:
CB_or_CA_Method_ID
CB_or_CA_Source_ID
CB_or_CA_Target_ID
These are the IDs that link to the field LookupID in another table tbl_Lookups and the field str_Item has the text for
Methods ["email", "phone call", "message"]
Sources ["manager","employee","supervisor"]
Targets ["manager","employee","supervisor"]
I added the tbl_Lookups to the record source for the report 3 times, one linking to ...Method_ID, one to...Source_ID etc.
These tables were renamed as tbl_Lookups, tbl_Lookups_1, tbl_Lookups_2 by Access. When i try to put in str_Item from each in the report, access keeps switching them back to tbl_Lookups and i get three copies of Method, rather than Method, Source and Target.

I hope this makes sense. I need help!

 

[PWise]

1)right click on each lookup table and give a alias
2)join each field on the right field

 

[dhookom]

If all the tables reside in SQL 2008, I would either use a linked view from the server or create a pass-through with all of the joins. It should be much more efficient.

Duane
Hook'D on Access
MS Access MVP

 

[hansdebadde]

Access did all the alias work for me. I made sure the alias's were the same as in the relationship window. I did join by the right field.

I am using linked tables? What is a linked view, sorry for my ignorance but what/how?

 

[dhookom]

You can create views on SQL Server that are similar to select queries. Then link to the views as you would link to a table. This moves much of the processing to the server where it belongs.

A pass-through query would probably provide more efficient data retrieval.

Duane
Hook'D on Access
MS Access MVP

 

[hansdebadde]

Awesome Duane, it worked! I learned something today!