SQL Cross-Tab Issue

  • Thread starter Thread starter ExcelMan
  • Start date Start date
E

ExcelMan

I am joining 2 crosstab queries to create a recordsource for a report.
Each query has a ProjectNum field and an unknown number of other fields
representing the months of data retrieved from a table. The data in
these other fields are the total transaction amounts for each month.

My problem is this: Since I don't know how many fields are in each
crosstab, the only way I know to join these queries is by using
crosstab1.*, crosstab2.* in the Select clause.

This retrieves all the fields I want, but also retrieves the ProjectNum
field twice since it is in both crosstabs.

Is there anyway, using queries, to get all the fields in the two
crosstabs, but only one of the ProjectNum fields?


P.S. I have simplified the real question I am facing, I actually need
to eliminate quite a few fields, but if there is an answer to the above
question I can use it to solve the more involved set of issues.


Thanks for your help.
 
Can you tell us why the number of months will change? Would it be possible
to change your specification to create a report of 6 months or 12 months or
whatever?

There are also methods for creating crosstabs with multiple values.
 
The number of months changes because I don't know in advance of doing
the crosstab queries how many months of data I will retrieve. This
can't be controlled, it is the nature of the data.
 
If you don't know how many columns/months how are you binding your report?

If you can't limit the months, my next choice would be to create more than
one value in the single crosstab result.
 
Back
Top