ComboBox returns wrong field from query when added to form

[Guest]

I am having a problem pulling data into a combobox from a query. Im sure this
issue has been delt with in other threads but i'm new to this game so I need
it dummed down a bit. Here is my scenario. I am running Access 2000

I have a table containing stock items, this table with contain uniques items
identified by a Autonumber Part_Number, i have a second table called
inventory which holds info on items currently in stock, each row is uniquly
identified using an AutoNumber transaction_Number, there can be multiple
instances of each Part_Number in stock, when a user books a stock item onto
the system he selects the item of stock from a lookup field which returns the
unique stock item from the stock items table . I have a third table called
Orders, when an order is placed the user selects what items to add to the
order from lookup fields. these fields are based on select queries which
filter entries on the Inventory table by Category and Allocated yes/no.

The queries themselve work fine and return a count of the unallocated stock
of a particular category along with the stock item name. the problem arises
when i want to base a lookup field on these queries. Instead of the part name
and the count total coming back the lookup field returns the Autonumber
Part_Number and the total.

I have messed with this for 2 weeks now trying to find alternative ways of
getting the data, make table queries etc. but with no joy. anyhelp would by
greatly appreciated.

Dan

 

[Steve Schapel]

Dan,

I think your query needs to include the StockItems table, joined to the
other table on the Part_Number field. The StockItems table is
(presumably) the only table where the Part Name resides, so if you want
this value in your query, it has to be retrieved from the StockItems
table via its relationship with the Part_Number recorded in the
Inventory table.

It may be worth having a look at
http://www.mvps.org/access/lookupfields.htm which explains how using
lookup fields can contribute to confusion and civil strife.

 

[Gary Walter]

I wondered where all the "civil strife"
was coming from...

gary