• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

Report shows record number instead of data

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
0
bruceblackman
Asked:
bruceblackman
  • 6
  • 5
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
0
 
bruceblackmanAuthor 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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bruceblackmanAuthor 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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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

0
 
bruceblackmanAuthor 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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
stand by
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
0
 
bruceblackmanAuthor Commented:
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
0
 
bruceblackmanAuthor Commented:
Job well done...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
no problem and thanks for the kudos ...

mx
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now