If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query results to columns in excel
Can anyone tell me how to structure a query or output to turn two tables into
a single row in excel: I have one table with contractID in it that has a one to many relationship with servicesID (eg many services to each contract). When I build my query I get ContractID/ServiceID 1/1 1/2 1/3 etc... (each in a column) But I need to to show in excel with the first column as the contract and each service (max 10) in the subsequent columns. 1/1/2/3 etc... Any help appreciated. |
#2
|
|||
|
|||
query results to columns in excel
Sounds like a crosstab query might do it for you. Post a sample of your data.
-- KARL DEWEY Build a little - Test a little "Ian" wrote: Can anyone tell me how to structure a query or output to turn two tables into a single row in excel: I have one table with contractID in it that has a one to many relationship with servicesID (eg many services to each contract). When I build my query I get ContractID/ServiceID 1/1 1/2 1/3 etc... (each in a column) But I need to to show in excel with the first column as the contract and each service (max 10) in the subsequent columns. 1/1/2/3 etc... Any help appreciated. |
#3
|
|||
|
|||
query results to columns in excel
Maybe you are looking for something like this crosstab query ---
TRANSFORM First(IIf([ServiceID] Is Not Null,"X","")) AS Expr2 SELECT IAN_Many.contractID FROM IAN_Many GROUP BY IAN_Many.contractID PIVOT "Service " & [ServiceID]; -- KARL DEWEY Build a little - Test a little "Ian" wrote: Can anyone tell me how to structure a query or output to turn two tables into a single row in excel: I have one table with contractID in it that has a one to many relationship with servicesID (eg many services to each contract). When I build my query I get ContractID/ServiceID 1/1 1/2 1/3 etc... (each in a column) But I need to to show in excel with the first column as the contract and each service (max 10) in the subsequent columns. 1/1/2/3 etc... Any help appreciated. |
#4
|
|||
|
|||
query results to columns in excel
YOU ROCK KARL. That took 2 seconds to use. Thank you so much. I owe you a
beer if you're in Canada. Ian. "KARL DEWEY" wrote: Maybe you are looking for something like this crosstab query --- TRANSFORM First(IIf([ServiceID] Is Not Null,"X","")) AS Expr2 SELECT IAN_Many.contractID FROM IAN_Many GROUP BY IAN_Many.contractID PIVOT "Service " & [ServiceID]; -- KARL DEWEY Build a little - Test a little "Ian" wrote: Can anyone tell me how to structure a query or output to turn two tables into a single row in excel: I have one table with contractID in it that has a one to many relationship with servicesID (eg many services to each contract). When I build my query I get ContractID/ServiceID 1/1 1/2 1/3 etc... (each in a column) But I need to to show in excel with the first column as the contract and each service (max 10) in the subsequent columns. 1/1/2/3 etc... Any help appreciated. |
Thread Tools | |
Display Modes | |
|
|