Querying two tables (1-to-many relationship)

[supertsik]

Hi.

I have the following problem:

I have two tables:
- Customers (CustomerID, Name)
- Orders (OrderID, CustomerID, Product)

I need to create a query/report that will have the following fields:
- CustomerID, Name, OrderID

If I do it the normal way * I will end up having one line for each order and the customer name will be repeated.
Instead, I would like to have one line per customer, and then the OrderIDs separated by commas...

Is this possible using queries?

* SELECT Customers.CustomerID, Customers.Name, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Any help would be greatly appreciated. Thanks!!

[Rabbit]

I don't think it's possible without code.

[MMcCarthy]

This is the best I can do for you ...

TRANSFORM OrderID
SELECT CustomerID, [Name]
FROM (SELECT Customers.CustomerID, Customers.Name, Orders.OrderID
FROM Customers INNER JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID) As CustOrders
GROUP BY CustomerID, [Name]
PIVOT OrderID;
 

Mary

[supertsik]

Thank you for your reply, but the crosstab query has a limited number of column headings.
Thanks very much for your help though, because I can use it elsewhere!

[NeoPa]

Have a look in here (Combining Multiple Rows of one Field into One Result). It doesn't (can't possibly) work in columns as such, but does produce a list.

[NeoPa]

This (Combining Rows-Opposite of Union ) is a better link - referred to in the first, but the main stuff is in here.

[Michael R]

This (Combining Rows-Opposite of Union ) is a better link - referred to in the first, but the main stuff is in here.

Supertsik, let us know how are things working out for you with this problem. Thanks.