>Still One of the Situations i Gould Be tempted to Code in two steps:
>First the Select defaulting to a 0 Discount
>Then an update to the correct Discount Value
>

Right.... In VFP that is what I would have done as well, I would have just created a cursor read and write with the de-normalized orders and then scanned through it. In VFP you would not really "feel" the difference in speed, but you code would come out crystal clear.

Ah.... VFP.... :)

Thanks and take care

>>Right!
>>
>>Now I understand the message Access gives. It's seems that in design view, you can only left join one field, and it does tell you to "add " the other one " in your sql statement":), in fact you add it in "sql view".
>>
>>It works now.
>>
>>Thanks for this
>>
>>
>>>>This must be a "DBMS 101" classic, but I don't seem to be able to figure it out:
>>>>
>>>>I have a discount table that has a primary key, a foreign key to customer and a foreign key to the items table. I.e. "some customers have discounts on some items".
>>>>
>>>>I have an order header table that contains a foreign key to the customer and order detail that has a foreign key to the item.
>>>>
>>>>I'm trying to build a dbms query or queries (in msaccess) that would join the discount table to the de-normalized order table (I .e. a join of the order header and the order detail on the header key).
>>>
>>>
>>>You have to navigate from the order to the OrderDetail (inner join) , then outer join (there may or may not be a discount) to the discount table using the customer from the order and the item in the OrderDetail
>>>
>>>
>>>

>>>select Order.*
>>>           OrderDetail .*, 
>>>           Discount.Percent 
>>>           from Order
>>>          join OrderDetail on (OrderDetail.Order_fk = Order.pk )
>>>          left join Discount on ( Order.Customer_fk = Discount.Customer_fk ) 
>>>                                         and (OrderDetail.Item_fk = Discount.Item_fk )
>>>         where ( Order.pk = ?someOrderPK) 
>>>