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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|