How to Right outer join several tables in a query? (1 Viewer)

[wcoast]

Hi!

Thanks for all help so far, this forum is great

I have 3 tables: Order, Product and Names.

I want to list All orders, no matter if they hold a key to a product or not, and no matter if this product has been given a name (in the table name) or not.

To make it even more complicated, the customer can give upp to 16 namesuggestions, they are all numbered from 1-16. But If they are given a namesuggestion, i only want to present the first one (nr 1).

Relations: 1 order -> 0 or 1 product.
1 order -> 0 or up to 16 names, of which i only want to display nr 1.

Any suggestions?
I am thinking of preparing the data in a temporary table, but would be glad if i did not need to do so....

 

[Pat Hartman]

Create a query that selects the name you want. If you are using autonumbers, you can order on the autonumber and return the first name:

Select OrderId, First(OrderName) As FirstOrderName
From YourTable
Order by OrderID
Group By OrderID;
Then use this query in the main query rather than the name table.