Need help with SQL query

[Garry Martin]

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*"));

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

 

[Duane Hookom]

If don't want to see multiple lines from the same Purchase Order then remove
the PurchaseOrdersItems table from the query.

 

[Garry Martin]

Hi Duane

The thing is that I when I do a search ,I want to serach for all options ie
Order No, Customer name,Date of purchase,sales person ,
Make,Model,Description .

I have tried your example but will not allow me to search for the last 3
options.

Do you have any other idea's

Thanks


Garry

 

[Duane Hookom]

Next time, you should provide all your requirements in your first post since
it saves us all some time. You can add the table back in and change the
query to a totals query. Set all the non-detail fields to "Group By" and set
the detail fields to "Where".

 

[Garry]

Hi Duane

Sorry about that ,it wasn't until I thought I had everything written down
that I then saw I missed bits.

I have tried using the Group By command but I dont seem to be able to get
it to work.

..Can you help me with changing the code below.:-

As I said, I am not very good with advanced SQL Code.

Thanks

Garry

 

[Duane Hookom]

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

 

[Garry Martin]

Thanks Duane

You really helped me out.

Just one more question.
Why can you not group by a Memo field in MSAccess.
I had to change my Notes Field to Text.


Thanks again


Garry

 

[Duane Hookom]

You can group by Left([MemoField],255). My question would be why would you
ever want to group by a field that contains more than about 30 characters. I
think this might be a performance trade-off since Memo fields are stored
different from other types of fields.