CrossTab Challenge

[MJatAflac]

I have an interesting challenge with a couple of CrossTab Queries.

I have a crosstab that gives Project Resource and then Forecasted Hours by
week.
I have a crosstab that gives Project Resource and the Actual Hours by week.

I need a query that combines the two so that I end up with the following:

Project Resource Week1 Forecast Week1 Actual Week 1 Variance Week2 Forecast
etc...

The problem is that the user wants to be able to select the date range for
the report so I'll never know what the dates will be. I can restrict them to
a six week range and I know the dates will always be in the past. But other
than that It's anyone's guess.

I'm thinking that somehow I need to translate the week into 1, 2, 3, 4, 5,
and 6 but I have no clue how to do this.

Any ideas?

Thanks,

m

 

[Jeff C]

Try backing up and looking at your queries before you create your crosstab.

Your date can be the formatted date field format([datefield],"ww") which
will always translate into your column heading

Your Resource is yoru row heading and the hours are your value so, create a
union query of your two or three or four queries that have all your data and
then, place your union query in the query builder and build your crosstab out
of that.

 

[MJatAflac]

I can't really do it that way because I have three values not one and a
crosstab only allows for one. So I have project as a row heading, resource as
a row heading and then the week or week number as you indicated as the column
heading but I need to have three values the forecast the actual and the
variance.

Hope that made sense.
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Try backing up and looking at your queries before you create your crosstab.

Your date can be the formatted date field format([datefield],"ww") which
will always translate into your column heading

Your Resource is yoru row heading and the hours are your value so, create a
union query of your two or three or four queries that have all your data and
then, place your union query in the query builder and build your crosstab out
of that.
--
Jeff C
Live Well .. Be Happy In All You Do

I have an interesting challenge with a couple of CrossTab Queries.

I have a crosstab that gives Project Resource and then Forecasted Hours by
week.
I have a crosstab that gives Project Resource and the Actual Hours by week.

I need a query that combines the two so that I end up with the following:

Project Resource Week1 Forecast Week1 Actual Week 1 Variance Week2 Forecast
etc...

The problem is that the user wants to be able to select the date range for
the report so I'll never know what the dates will be. I can restrict them to
a six week range and I know the dates will always be in the past. But other
than that It's anyone's guess.

I'm thinking that somehow I need to translate the week into 1, 2, 3, 4, 5,
and 6 but I have no clue how to do this.

Any ideas?

Thanks,

m

 

[Jeff C]

OK now, follow me , in all three queries you have a row heading whose value
is common for all three and you have your date as your column header for all
three. Then you have your third value field. No matter how you arrive at
your value in each of the the queries, as long as it's field name is the same
in all three queries you can build your union query. Remember as long as the
field names are the same you can build a union query out of however many
queries you have. You can also be creative when you build the field for your
row heading. Just name the field something like:

Query 1 - Row:[Project Resource]&" "&"Actual Hours"
Query 2 - Row:[Project Resource]&" "&"Forecast Hours"
Query 2 - Row:[Project Resource]&" "&"Varience"

Once you build your Union Query you will have only three fields, Row, Date,
Value. Place the union query in a new query making it a crosstab.
--
Jeff C
Live Well .. Be Happy In All You Do

I can't really do it that way because I have three values not one and a
crosstab only allows for one. So I have project as a row heading, resource as
a row heading and then the week or week number as you indicated as the column
heading but I need to have three values the forecast the actual and the
variance.

Hope that made sense.
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Try backing up and looking at your queries before you create your crosstab.

Your date can be the formatted date field format([datefield],"ww") which
will always translate into your column heading

Your Resource is yoru row heading and the hours are your value so, create a
union query of your two or three or four queries that have all your data and
then, place your union query in the query builder and build your crosstab out
of that.
--
Jeff C
Live Well .. Be Happy In All You Do

I have an interesting challenge with a couple of CrossTab Queries.

I have a crosstab that gives Project Resource and then Forecasted Hours by
week.
I have a crosstab that gives Project Resource and the Actual Hours by week.

I need a query that combines the two so that I end up with the following:

Project Resource Week1 Forecast Week1 Actual Week 1 Variance Week2 Forecast
etc...

The problem is that the user wants to be able to select the date range for
the report so I'll never know what the dates will be. I can restrict them to
a six week range and I know the dates will always be in the past. But other
than that It's anyone's guess.

I'm thinking that somehow I need to translate the week into 1, 2, 3, 4, 5,
and 6 but I have no clue how to do this.

Any ideas?

Thanks,

m

 

[MJatAflac]

OK I see what you're saying but won't that give me data that looks like this?

Project1 Resource1 Actual
Project1 Resource1 Forecast
Project1 Resource1 Variance
Project2 Resource2 Actual
Project2 Resource2 Forecast
Project2 Resource2 Variance

When what I'm after is:

Project1 Resource1 Week1Actual Week1 Forecast Week1 Variance Week2 Actual...

My dilemna is that I have to be able to match not only project and resource
but also week when I won't know ahead of time what week(s) will be selected.

Thanks,

m
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

OK now, follow me , in all three queries you have a row heading whose value
is common for all three and you have your date as your column header for all
three. Then you have your third value field. No matter how you arrive at
your value in each of the the queries, as long as it's field name is the same
in all three queries you can build your union query. Remember as long as the
field names are the same you can build a union query out of however many
queries you have. You can also be creative when you build the field for your
row heading. Just name the field something like:

Query 1 - Row:[Project Resource]&" "&"Actual Hours"
Query 2 - Row:[Project Resource]&" "&"Forecast Hours"
Query 2 - Row:[Project Resource]&" "&"Varience"

Once you build your Union Query you will have only three fields, Row, Date,
Value. Place the union query in a new query making it a crosstab.
--
Jeff C
Live Well .. Be Happy In All You Do

I can't really do it that way because I have three values not one and a
crosstab only allows for one. So I have project as a row heading, resource as
a row heading and then the week or week number as you indicated as the column
heading but I need to have three values the forecast the actual and the
variance.

Hope that made sense.
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Try backing up and looking at your queries before you create your crosstab.

Your date can be the formatted date field format([datefield],"ww") which
will always translate into your column heading

Your Resource is yoru row heading and the hours are your value so, create a
union query of your two or three or four queries that have all your data and
then, place your union query in the query builder and build your crosstab out
of that.
--
Jeff C
Live Well .. Be Happy In All You Do


:

I have an interesting challenge with a couple of CrossTab Queries.

I have a crosstab that gives Project Resource and then Forecasted Hours by
week.
I have a crosstab that gives Project Resource and the Actual Hours by week.

I need a query that combines the two so that I end up with the following:

Project Resource Week1 Forecast Week1 Actual Week 1 Variance Week2 Forecast
etc...

The problem is that the user wants to be able to select the date range for
the report so I'll never know what the dates will be. I can restrict them to
a six week range and I know the dates will always be in the past. But other
than that It's anyone's guess.

I'm thinking that somehow I need to translate the week into 1, 2, 3, 4, 5,
and 6 but I have no clue how to do this.

Any ideas?

Thanks,

m

 

[Jeff C]

I forgot you needed to allow for date parameters. You can use the colum
headings property of the crosstab to build a field for each week of the year.
Build a report to display the data the way you want. Build an unbound form
with a combo box for the user to select the date period they are loooking for
and reference this in the criteria for the date field that you have in your
union query.

Maybe one of the MVPs in here can offer a solution to place the selected
data into an unbound text box in the report. I am sure it can be done.




Jeff C
Live Well .. Be Happy In All You Do

OK I see what you're saying but won't that give me data that looks like this?

Project1 Resource1 Actual
Project1 Resource1 Forecast
Project1 Resource1 Variance
Project2 Resource2 Actual
Project2 Resource2 Forecast
Project2 Resource2 Variance

When what I'm after is:

Project1 Resource1 Week1Actual Week1 Forecast Week1 Variance Week2 Actual...

My dilemna is that I have to be able to match not only project and resource
but also week when I won't know ahead of time what week(s) will be selected.

Thanks,

m
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

OK now, follow me , in all three queries you have a row heading whose value
is common for all three and you have your date as your column header for all
three. Then you have your third value field. No matter how you arrive at
your value in each of the the queries, as long as it's field name is the same
in all three queries you can build your union query. Remember as long as the
field names are the same you can build a union query out of however many
queries you have. You can also be creative when you build the field for your
row heading. Just name the field something like:

Query 1 - Row:[Project Resource]&" "&"Actual Hours"
Query 2 - Row:[Project Resource]&" "&"Forecast Hours"
Query 2 - Row:[Project Resource]&" "&"Varience"

Once you build your Union Query you will have only three fields, Row, Date,
Value. Place the union query in a new query making it a crosstab.
--
Jeff C
Live Well .. Be Happy In All You Do

I can't really do it that way because I have three values not one and a
crosstab only allows for one. So I have project as a row heading, resource as
a row heading and then the week or week number as you indicated as the column
heading but I need to have three values the forecast the actual and the
variance.

Hope that made sense.
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


:

Try backing up and looking at your queries before you create your crosstab.

Your date can be the formatted date field format([datefield],"ww") which
will always translate into your column heading

Your Resource is yoru row heading and the hours are your value so, create a
union query of your two or three or four queries that have all your data and
then, place your union query in the query builder and build your crosstab out
of that.
--
Jeff C
Live Well .. Be Happy In All You Do


:

I have an interesting challenge with a couple of CrossTab Queries.

I have a crosstab that gives Project Resource and then Forecasted Hours by
week.
I have a crosstab that gives Project Resource and the Actual Hours by week.

I need a query that combines the two so that I end up with the following:

Project Resource Week1 Forecast Week1 Actual Week 1 Variance Week2 Forecast
etc...

The problem is that the user wants to be able to select the date range for
the report so I'll never know what the dates will be. I can restrict them to
a six week range and I know the dates will always be in the past. But other
than that It's anyone's guess.

I'm thinking that somehow I need to translate the week into 1, 2, 3, 4, 5,
and 6 but I have no clue how to do this.

Any ideas?

Thanks,

m