We help IT Professionals succeed at work.

Report shows record number instead of data

bruceblackman
on
Medium Priority
534 Views
Last Modified: 2013-11-28
Table#1 = list of companies
Table#2 = associated information
Table#2 uses table#1 as a data source (drop down / combo box...)
Query using table#2 list the company names assocatie with the data
When I build a report using the query above, I only get the record number assocaited with that company name (auto numbering ID).  

My question is how do I get the company name instead of the record number of the company?

tnx's b2
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"My question is how do I get the company name instead of the record number of the company?"

This is the problem when you use combo boxes directly in tables, which is really not a good idea.  You should remove all combos from that table itself, such that 'what you see is what you get' in any field.  As you can see (no pun) ... this is VERY confusing when it comes to queries built on tables with combos for some fields.

mx

Author

Commented:
Ok... so I have this list of companies that I don't want to have to type in very time
So I build a form and add the combo box there ?

Is there a wizard available for me to use while buiding the form for this lookup.

 I usually use the form wizard to build the form  from the table and then move the fiields around and it already has the correct code to find the lookup table and correct column.

b2
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"So I build a form and add the combo box there ?"
Yes, exactly.   Forms are for this purpose - the create a user friendly user interface.  Tables are for storing raw data and not really meant to be used by end users.  Therefore, it's prudent to avoid using that combo box feature of tables.  I have seen it lead to LOADS of confusion ... and it's very difficult to trouble shoot.

mx

Author

Commented:
Ok
I like your thought process..
I built a form with the look up table / combo box  but still have the same problem...
Now I have a query and report that both show the index number.  There must be some relationship that I am missing ?

b2
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Can you:

1) Compact & Repair (to shrink the size),
2) Zip up the MDB (to further shrink the size)
3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course?
4** And please give a clear explanation of exactly how to reproduce the problem.

mx

Author

Commented:
I removed lookup / combo feature on table structure.
I added combo / lookup feature on form.
Form adds index reference to table
Both Query and Report only show  index number .  I need it to show company name.

b2i

b2.zip
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
stand by
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Here you go ... working now.

I have to take of for a while, will explain when I get back. However, look at the query ... and you will see what I did.

Also, had to change the data type on CustomerID from Text to Number - to match SupplierID in that table.

mx
business-MX01.zip

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
DatabaseMX
I want to acknowledge your support.  I am sorry it took me so long to say thank you..  I had to travel and did not have computer access.  I assume the solution was basic for "old salts" but a revelation to a  newbie.  

b2

Author

Commented:
Job well done...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
no problem and thanks for the kudos ...

mx
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.