CrossTab query with 2 values per month?

[Larry]

I have data pulling together costs for travel throughout the year. I
have created a CrossTab query (not my favorite type of query) which
shows the type of travel (row header) and month (column header) with
the total cost at the interesection of each month/trip type. No
problem.

Now the user has asked that I put how many trips make up the cost in
each month for each trip, showing the amount and trip as something like
the following "$4,123 (3)" (where that is 3 trips of the current type
totalling $4,123). Now it doesn't have to be in parenthesis, that was
just their first request.

I have worked on this most of the day, and other than writing code that
would create a temporary table of the original cost crosstab, then
modify the data in that temp table with the additional information and
reporting on that, I have no ideas.

So, I'm asking if anyone has ever done anything like this and if so,
how?

Thanks,
Larry

 

[Allen Browne]

Switch your query to SQL View (View menu).

Where is says something like:
TRANSFORM Sum(Table1.Amount) AS SumOfAmount
try concatenating two calculations and the brackets like this:
TRANSFORM Format(Sum(Table1.Amount), "Currency") & "(" &
Count(Table1.ID) & ")" AS AmountWithTrips

 

[Larry]

Thanks Allen. I had tried this in the design view, but it didn't work
for some reason. Now that I did it in the SQL view, it looks pretty
much the same in the design view! I guess I did something wrong the
first time and gave up to quickly!

Thanks again.

 

[Larry]

This worked great, but now the user wants more "Give them an inch....".

They would like the number in parenthesis, to actually have it's own
column under the month.

I'm probably going to have to go a temp table route to do this, but
thought I'd see if you (or someone) might know if this is possible with
a query?

 

[Allen Browne]

Larry, argue that that is too much information in one place (hard for the
reader to meaningfully compare the values interspersed with other columns of
data), so that the 2nd (and later 3rd and 4th and ...) set of values should
in a listing of their own below the dollar values (a.k.a. a subreport with
its own crosstab query.)

I suspect that will be easier to maintain and expand for them. The
alternative would be to build a query with the months and then JOINing both
crosstabs so you can get the values side-by-side.

 

[Larry]

They are wanting export the values to Excel, so wanted the number in
two columns, but I'm going to try to convince them to go with what I've
already given them.

Joining crosstabs sounds scary, and I'm not sure it would give them
what they really want.