Query with lookup fields in table

[famehrie]

I have a query which pulls its data from a table. The table contains several fields which contain lookup values. For simplicity purposes I will keep this down to a few fields.

Table1 = tblCustomerData
Field1 = IssueID - primary key
Field2 = CustomerName
Field3 = Owner - The owner is based on a lookup query of the tblOwner table below. The bound column is 1 and set to 2 columns with the widths set to 0";2" so it displays the OwnerName instead of the OwnerID.

Table2 = tblOwner
Field1 = OwnerID - primary key
Field2 = OwnerName

Query = qryOwner - based on Table1
Field1 = IssueID
Field2 = OwnerName

My problem is this. I want to use the results from the qryOwner in a report which will show the OwnerName but when I generate it it shows the OwnerID. Is there a way to display the OwnerName instead of the OwnerID. I am somewhat reluctant to modify Table1 because I have another function which uses the lookup field based on the OwnerID.

Also in reference to qryOwner. I want to allow a user of the database to search for any records for a specific owner by the OwnerName. I have used the following code below as the criteria for the OwnerName but it bases its entry on the OwnerID and not the OwnerName.
Like [Owner Name] & "*"
Does anyone have any suggestions?

Thanks
Kevin

 

[dhookom]

I would never use lookup fields

http://www.mvps.org/access/lookupfields.htm.

The only way you can get OwnerName to appear is to include the table and field with OwnerName (tblOwner.OwnerName).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

Like dhookup is indicated you must use a query with both tables included with the appropriate join to show the OwnerName:

Select A.IssueID, A.CustomerName, B.OwnerID, B.OwnerName 
FROM Table1 as A INNER JOIN tblOwner as B ON A.Owner = B.OwnerID;[code]

This query SQL should give you the data that you want.



Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[famehrie]

dhookum and scriverb,

Thanks for the advise. I quess I'll have to rethink my tables out and create some relationships. A little more work than I was hopping for but I beleive I can handle it.

Again Thanks.