A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

CrossTab Challenge



 
 
Thread Tools Display Modes
  #1  
Old November 27th, 2006, 05:32 PM posted to microsoft.public.access.queries
MJatAflac
external usenet poster
 
Posts: 15
Default CrossTab Challenge

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
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office
  #2  
Old November 27th, 2006, 06:07 PM posted to microsoft.public.access.queries
Jeff C
external usenet poster
 
Posts: 392
Default CrossTab Challenge

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


"MJatAflac" wrote:

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
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

  #3  
Old November 27th, 2006, 06:49 PM posted to microsoft.public.access.queries
MJatAflac
external usenet poster
 
Posts: 15
Default CrossTab Challenge

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


"Jeff C" wrote:

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


"MJatAflac" wrote:

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
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

  #4  
Old November 27th, 2006, 07:18 PM posted to microsoft.public.access.queries
Jeff C
external usenet poster
 
Posts: 392
Default CrossTab Challenge

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


"MJatAflac" wrote:

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


"Jeff C" wrote:

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


"MJatAflac" wrote:

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
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

  #5  
Old November 27th, 2006, 07:26 PM posted to microsoft.public.access.queries
MJatAflac
external usenet poster
 
Posts: 15
Default CrossTab Challenge

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


"Jeff C" wrote:

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


"MJatAflac" wrote:

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


"Jeff C" wrote:

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


"MJatAflac" wrote:

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
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

  #6  
Old November 27th, 2006, 07:53 PM posted to microsoft.public.access.queries
Jeff C
external usenet poster
 
Posts: 392
Default CrossTab Challenge

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


"MJatAflac" wrote:

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


"Jeff C" wrote:

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


"MJatAflac" wrote:

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


"Jeff C" wrote:

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


"MJatAflac" wrote:

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
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.