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.

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.

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.

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.