query results to columns in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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];
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top