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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Larry" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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

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.

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?

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Larry" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>

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.