Can't get the proper display of a field in my report.

[Sue]

I have 2 tables, both using autonumbers for their primary key. The first
table is for contacts (i.e. last name, first name, etc.). The second table
is for businesses (business name, etc.) I have a field in my contacts table
that has a number format so it can be used as a foreign key for the business
table. I then set up the relationship between them & enforced referential
integrity. When I run a query, I see the name of the business (after setting
up a combo box) - no problem. When I run a report based on that query, a
number is displayed (not the business name).

Suggestions, please?

Thanks so much.

 

[Allen Browne]

So you have 2 tables like this:

Business table:
BusinessID AutoNumber primary key
BusinessName Text
...
Contact table:
ContactID AutoNumber primary key
BusinessID Number relates to Business.BusinessID
LastName Text
...

That should work, provide you made the reationship from Business.BusinessID
to Contact.BusinessID, and not to the AutoNumber in the Contact table.)

 

[Marshall Barton]

I have 2 tables, both using autonumbers for their primary key. The first
table is for contacts (i.e. last name, first name, etc.). The second table
is for businesses (business name, etc.) I have a field in my contacts table
that has a number format so it can be used as a foreign key for the business
table. I then set up the relationship between them & enforced referential
integrity. When I run a query, I see the name of the business (after setting
up a combo box) - no problem. When I run a report based on that query, a
number is displayed (not the business name).

I suspect that you have used a dreaded lookup field for the
business names. If so, you need to get rid of the combo box
and Join the lookup table in the query too.

Don't mess with the report untill you have the query
producing the correct data without the combo box.

 

[Sue]

Hi Marshall - thanks for responding.
I do have a lookup field in the form that I use to input data. There's no
lookup field in the table or query.
Can you please explain to me why lookup fields are to be dreaded and when it
is appropriate/inappropriate to use them?
Thanks so much for your time.

 

[Marshall Barton]

Lookup **fields** in a table are very confusing because what
you see is not what you get.

Lookup **fields** in a query are ok as long as you
understand what you are doing, but this has very little
utility.

Lookup **controls** (combo/list boexes) on a form are a
standard way to get a foreign key to another table (the
number you are seeing).

Back to your original problem. Since you said that you do
not have a lookup field (combo box) in your tables, then I
think the problem is in your query. See Allen's reply and
if you still need help, post a Copy/Paste of of your query's
SQL view.

 

[Sue]

Yep - still reading a number (the primary key), not the text for the
business:

SELECT tblProtocol.Protocol_IDName, tblContactType.ContactType,
tblContacts.LastName, tblContacts.FirstName, tblContacts.BusinessName,
tblContacts.BusinessPhone, tblContacts.BusinessEmail,
tblContacts.BusinessExtension, tblContacts.BusinessFax,
tblContacts.BusinessCell, tblContacts.BusinessPager, tblContacts.Notes
FROM tblProtocol INNER JOIN (tblContactType INNER JOIN (tblBusinessName
INNER JOIN tblContacts ON tblBusinessName.Business_ID =
tblContacts.BusinessName) ON tblContactType.ContactTypeID =
tblContacts.ContactType) ON tblProtocol.Protocol_ID = tblContacts.Protocol
ORDER BY tblProtocol.Protocol_IDName, tblContactType.ContactType,
tblContacts.LastName, tblContacts.FirstName;


Any help you can provide would be greatly appreciated.

You guys are great.

Lookup **fields** in a table are very confusing because what
you see is not what you get.

Lookup **fields** in a query are ok as long as you
understand what you are doing, but this has very little
utility.

Lookup **controls** (combo/list boexes) on a form are a
standard way to get a foreign key to another table (the
number you are seeing).

Back to your original problem. Since you said that you do
not have a lookup field (combo box) in your tables, then I
think the problem is in your query. See Allen's reply and
if you still need help, post a Copy/Paste of of your query's
SQL view.
--
Marsh
MVP [MS Access]

I do have a lookup field in the form that I use to input data. There's no
lookup field in the table or query.
Can you please explain to me why lookup fields are to be dreaded and when
it
is appropriate/inappropriate to use them?

"Marshall Barton" wrote

 

[Marshall Barton]

I don't see how the query can display the business name
since it is not included as a field. What you have will
work fine in a form with a combo box bound to the
tblContacts.BusinessName field. But you need a slightly
different query for a report (because the report is not
using a combo box).

Try Copy/Pasting the query to a new query and changing the
field tblContacts.BusinessName
to tblBusinessName.BusinessName
or whatever the field in tblBusinessName is called.