transpose table query

[Souris]

I have data table like following

Emp No Activity Date A1Value A2Value A3Value A4Value
1 3/20/2008 10 15 10 15
1 3/21/2008 5 10 1 2


I would like have a report like following


Emp No:1

Activity/Date 3/20/2008 3/21/208 3/22/2008

Activity 1 10 5
Activity 2 15 10
Activity 3 10 1



Dose crosstab query help to do so?
If not, any query example i can do above report?

Your information is great appreciated,

 

[KARL DEWEY]

Use a union query to get data to look like this --
Emp No Activity Date Value Code
1 3/20/2008 10 A1
1 3/20/2008 15 A2
1 3/20/2008 10 A3
1 3/20/2008 15 A4
Then use a crosstab query.

 

[Souris]

Thanks millions,

Use a union query to get data to look like this --
Emp No Activity Date Value Code
1 3/20/2008 10 A1
1 3/20/2008 15 A2
1 3/20/2008 10 A3
1 3/20/2008 15 A4
Then use a crosstab query.

 

[Souris]

It seems that CrossTab query does not accept parameter like Activity Date
between [Start Date] and [End Date] even I put parameter in to Union query.

Can I have any parameter for Crosstab query?

Thanks again,

 

[John Spencer]

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

It seems that CrossTab query does not accept parameter like Activity Date
between [Start Date] and [End Date] even I put parameter in to Union
query.

Can I have any parameter for Crosstab query?

Thanks again,

Use a union query to get data to look like this --
Emp No Activity Date Value Code
1 3/20/2008 10 A1
1 3/20/2008 15 A2
1 3/20/2008 10 A3
1 3/20/2008 15 A4
Then use a crosstab query.

 

[Souris]

Thanks millions,

Does SQL server support Crosstab query?
If it does, can I just copy the SQL from MS access to SQL server?

Thanks again,

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

It seems that CrossTab query does not accept parameter like Activity Date
between [Start Date] and [End Date] even I put parameter in to Union
query.

Can I have any parameter for Crosstab query?

Thanks again,

Use a union query to get data to look like this --
Emp No Activity Date Value Code
1 3/20/2008 10 A1
1 3/20/2008 15 A2
1 3/20/2008 10 A3
1 3/20/2008 15 A4
Then use a crosstab query.
--
KARL DEWEY
Build a little - Test a little


:

I have data table like following

Emp No Activity Date A1Value A2Value A3Value A4Value
1 3/20/2008 10 15 10 15
1 3/21/2008 5 10 1
2


I would like have a report like following


Emp No:1

Activity/Date 3/20/2008 3/21/208 3/22/2008

Activity 1 10 5
Activity 2 15 10
Activity 3 10 1



Dose crosstab query help to do so?
If not, any query example i can do above report?

Your information is great appreciated,

 

[John Spencer]

I don't know if SQL server supports crosstab queries or not. It may depend
on the version of SQL server you are using.

I would guess that if SQL server has a version of crosstab queries, that the
syntax is different from the syntax used by Access. So, I would say that
you cannot simply copy the Access SQL statement to SQL server.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks millions,

Does SQL server support Crosstab query?
If it does, can I just copy the SQL from MS access to SQL server?

Thanks again,

First thing, with a crosstab query you MUST declare your parameters and
if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

It seems that CrossTab query does not accept parameter like Activity
Date
between [Start Date] and [End Date] even I put parameter in to Union
query.

Can I have any parameter for Crosstab query?

Thanks again,







:

Use a union query to get data to look like this --
Emp No Activity Date Value Code
1 3/20/2008 10 A1
1 3/20/2008 15 A2
1 3/20/2008 10 A3
1 3/20/2008 15 A4
Then use a crosstab query.
--
KARL DEWEY
Build a little - Test a little


:

I have data table like following

Emp No Activity Date A1Value A2Value A3Value A4Value
1 3/20/2008 10 15 10
15
1 3/21/2008 5 10 1
2


I would like have a report like following


Emp No:1

Activity/Date 3/20/2008 3/21/208 3/22/2008

Activity 1 10 5
Activity 2 15 10
Activity 3 10 1



Dose crosstab query help to do so?
If not, any query example i can do above report?

Your information is great appreciated,

 

[Souris]

Thanks millions,

I don't know if SQL server supports crosstab queries or not. It may depend
on the version of SQL server you are using.

I would guess that if SQL server has a version of crosstab queries, that the
syntax is different from the syntax used by Access. So, I would say that
you cannot simply copy the Access SQL statement to SQL server.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks millions,

Does SQL server support Crosstab query?
If it does, can I just copy the SQL from MS access to SQL server?

Thanks again,

First thing, with a crosstab query you MUST declare your parameters and
if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

It seems that CrossTab query does not accept parameter like Activity
Date
between [Start Date] and [End Date] even I put parameter in to Union
query.

Can I have any parameter for Crosstab query?

Thanks again,







:

Use a union query to get data to look like this --
Emp No Activity Date Value Code
1 3/20/2008 10 A1
1 3/20/2008 15 A2
1 3/20/2008 10 A3
1 3/20/2008 15 A4
Then use a crosstab query.
--
KARL DEWEY
Build a little - Test a little


:

I have data table like following

Emp No Activity Date A1Value A2Value A3Value A4Value
1 3/20/2008 10 15 10
15
1 3/21/2008 5 10 1
2


I would like have a report like following


Emp No:1

Activity/Date 3/20/2008 3/21/208 3/22/2008

Activity 1 10 5
Activity 2 15 10
Activity 3 10 1



Dose crosstab query help to do so?
If not, any query example i can do above report?

Your information is great appreciated,