Lookup field report trouble

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have read many of the posts in this forum regarding Lookup fields and it
seems like they may not be as good as advertised, but I would like to still
use it if possible. My problem is listed below.

I have a Customer table that includes CustNo and CustName and a second table
(Projects) with the CustNo as a foreign key. I created a Lookup field using
the Lookup wizard to show the CustName in a combo box for selecting this
CustNo. This all works fine in the table, in forms, and in queries, but I can
not generate a report that includes the customer name based on the projects
table or any queries that use this table. The report always shows the CustNo.

Am I missing something? or is there a better way of doing what I am trying
to do?


Thanks in advance for any help,

Brian
 
Hi Bott,

You were warned! Now you can pass the warning on to others in hopes of
saving them the pain.
 
Hi, Bott.

What you are using is a combo box, on a form, to "look up" values from an
existing table or query. This practice is highly recommended. The practice
that causes problems is using the Lookup Field in the Table Design view.
This produces counterintuitive results, and is eschewed by the vast majority
of developers.

What is being stored in your Projects table is simply the foreign key, also
a good practice. To print the customer name, create a query joining Projects
and Customers, linked by the Customer Number, and drag the Customer Name
field and any others you need to the grid. Then base your report on the
query.

Hope that helps.
Sprinks
 
Am I missing something? or is there a better way of doing what I am trying
to do?
Click to expand...

Yes, there is: use Access as a relational database and get rid of your
lookups. :-{)

Create a Query joining your table to your Customers table by
CustomerID. Base your report on this Query. If you have other tables,
or other lookup fields, include these tables in the Query too.

Queries are the *basis* of any productive use of Access. Among the
worst problems with the Lookup misfeature is that it - as in your
case! - makes it harder for new users to get acquainted with Queries;
Lookup Fields are handy, and they'll cover the whole gamut of what you
need to do - from A to D. If you want to do anything in the range E
through Z you'll find that they don't help anymore!

John W. Vinson[MVP]
 
Back
Top