Link two fields of one table to same field in another

[K4VA]

Access 2003

Table: Orders
OrderID: Autonumber
ShipState: Long Integer
OrderState: Long Integer

Table: States
StateID: Autonumber
Abbr: Text (2 characters)
Name: Text (full name of state)

Relationships:
ShipState is 1 to many with StateID
OrderState is 1 to many with StateID

Form: Orders
ShipState: Combobox
OrderState: Combobox

Using form Orders, can select state from dropdown.

I now want to create a form that shows the two states, but I want to show the
state Name rather than abbreviation. Can't seem to make that leap.

 

[KARL DEWEY]

UNTESTED UNTESTED Try this --
SELECT Orders .*, IIF([Orders].[ShipState] = [StateID], [States].[Name],
"Unknown") AS Ship_State, IIF([Orders].[OrderState] = [StateID],
[States].[Name], "Unknown") AS Order_State
FROM Orders, States
WHERE ([Orders].[ShipState] = [StateID]) OR ([Orders].[OrderState] =
[StateID]);