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