By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,444 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Querying two tables (1-to-many relationship)

P: 7
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!!
Feb 21 '07 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,324
I don't think it's possible without code.
Feb 21 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
This is the best I can do for you ...

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM OrderID
  2. SELECT CustomerID, [Name]
  3. FROM (SELECT Customers.CustomerID, Customers.Name, Orders.OrderID
  4. FROM Customers INNER JOIN Orders 
  5. ON Customers.CustomerID = Orders.CustomerID) As CustOrders
  6. GROUP BY CustomerID, [Name]
  7. PIVOT OrderID;
  8.  
Mary
Feb 22 '07 #3

P: 7
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!
Feb 22 '07 #4

NeoPa
Expert Mod 15k+
P: 31,261
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.
Feb 23 '07 #5

NeoPa
Expert Mod 15k+
P: 31,261
This (Combining Rows-Opposite of Union ) is a better link - referred to in the first, but the main stuff is in here.
Feb 23 '07 #6

100+
P: 176
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.
Feb 24 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.