need help with SQL Query.Maybe a SubQuery

[Garry]

Hi

I have 3 tables in MSAccess.
1.Customers
2.PurchaseOrders
3.PurchaseOrdersItems

If there is more than 1 OrdersItem on the same order then the Company name
and other data comes up.
ie.
1001 ,Company Name , Date , SalesPerson , OrderItem1
1001 ,Company Name , Date , SalesPerson , OrderItem2
1001 ,Company Name , Date , SalesPerson , OrderItem3

What I want is just one line

1001 ,Company Name , Date , SalesPerson , OrderItems (Not to be listed in
the grid)

What I want is no matter how many ordereditems there are on a Purchase Order
only one company name comes up.

Here is the SQL code that I have already.This produces multiple ordereditems
as in example one.

SELECT PurchaseOrders.OrderNum, Customers.Name, PurchaseOrders.DateOrdered,
PurchaseOrders.SalesPerson, PurchaseOrders.DateRecieved,
PurchaseOrders.OrderComplete, PurchaseOrders.Notes,
PurchaseOrdersItems.Make, PurchaseOrdersItems.Model,
PurchaseOrdersItems.Description
FROM (Customers INNER JOIN PurchaseOrders ON Customers.AccountNum =
PurchaseOrders.CustomerNo) INNER JOIN PurchaseOrdersItems ON
PurchaseOrders.OrderNum = PurchaseOrdersItems.OrderNum
WHERE (((Customers.Name) Like "tnp*"));

Just to let you know the WHERE clause will be changed depending on what is
being searched for.
ie.
WHERE (((PurchaseOrdersItems.Make) Like "Sony*"));
or
WHERE (((PurchaseOrdersItems.Model) Like "UVW-1800*"));

I tried to alter the relationships between tables but it said 'The SQL
statement couldn't be executed because it contains ambiguous outer joins.To
force one of the joins to be performed first,create a seperate query that
performs the first join and then include that query in your SQL statement.

I know how to do basic SQL queries but this sounds more than I know at
present.

Can anyone help.

Thanks in Advance


Garry