|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-15 : 10:05:43
|
| TJ writes "I am building a query using two tables, one containing customers and the other containing orders. The tables are inner-joined via the CUSTOMER_ID field in both tables. There are multiple records for every customer in the orders table. The query returns one record for each customer as it should, but what I need to do is take the data from a field in each customers associated order data and combine it into one field. Basically I need to do the same thing as the sum function does, only instead of totaling numbers from multiple records i need to combine the text from the same fields in multiple records. If you need clarification jsut shoot me an email. I will include below the SQL i already have.SELECT customer.NAME, customer.COMPANY, customer.ADDRESS_1, customer.ADDRESS_2, customer.CITY, customer.STATE, customer.ZIP, customer.COUNTRY, customer.PHONE, customer.FAX, IIf([SHIP_NAME]="",[NAME],[SHIP_NAME]) AS SHIP_NAME_1, IIf([SHIP_COMPANY]="",[COMPANY],[SHIP_COMPANY]) AS SHIP_COMPANY_A, IIf([SHIP_ADDRESS_1]="",[ADDRESS_1],[SHIP_ADDRESS_1]) AS SHIP_ADDRESS_1_A, IIf([SHIP_ADDRESS_2]="",[ADDRESS_2],[SHIP_ADDRESS_2]) AS SHIP_ADDRESS_2_A, IIf([SHIP_CITY]="",[CITY],[SHIP_CITY]) AS SHIP_CITY_A, IIf([SHIP_STATE]="",[STATE],[SHIP_STATE]) AS SHIP_STATE_A, IIf([SHIP_ZIP]="",[ZIP],[SHIP_ZIP]) AS SHIP_ZIP_A, Sum(orders.WEIGHT) AS SumOfWEIGHT, customer.CUSTOMER_ID, orders.DESCRIPTIONFROM customer INNER JOIN orders ON customer.CUSTOMER_ID = orders.ORDER_IDWHERE (((customer.CUSTOMER_ID)<230000 And (customer.CUSTOMER_ID)>220000) AND ((orders.PRODUCT_ID)="MER401" Or (orders.PRODUCT_ID)="MRAF01" Or (orders.PRODUCT_ID)="MRAF02" Or (orders.PRODUCT_ID)="MSAR01" Or (orders.PRODUCT_ID)="MSARPRO01" Or (orders.PRODUCT_ID)="MZAP01" Or (orders.PRODUCT_ID)="MZAP05"))GROUP BY customer.NAME, customer.COMPANY, customer.ADDRESS_1, customer.ADDRESS_2, customer.CITY, customer.STATE, customer.ZIP, customer.COUNTRY, customer.PHONE, customer.FAX, customer.CUSTOMER_ID, orders.DESCRIPTION, customer.SHIP_NAME, customer.SHIP_COMPANY, customer.SHIP_ADDRESS_1, customer.SHIP_ADDRESS_2, customer.SHIP_CITY, customer.SHIP_STATE, customer.SHIP_ZIP, customer.SHIP_TELEPHONE;" |
|
Combining Text from multiple fields in multiple records