CrossTab Query with 2 values per month

[LarryJ]

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 intersection of each month/trip type. With all 12
months showing across the top as the collumn headings (Jan, Feb, Mar,
etc.). 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
totaling $4,123). 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 based on the original 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]

Answered in m.p.access

 

[Tom Ellison]

Dear Larry:

The crosstab is a peculiar feature to Access. The SQL language is much
larger than just Access. There are other ways to do the job.

SELECT TypeOfTravel,
(SELECT SUM(Amount)
FROM Table1 T1
WHERE T1.TypeOfTravel = T.TypeOfTravel
AND TripDate BETWEEN 1/1/2005 AND 1/31/2005)
AS JanTravel,
(SELECT COUNT(*)
FROM Table1 T1
WHERE T1.TypeOfTravel = T.TypeOfTravel
AND TripDate BETWEEN 1/1/2005 AND 1/31/2005)
AS JanCount
FROM TableX T
ORDER BY TypeOfTravel

The above query gives you the first pair of columns. I had to invent table
names and column names. Perhaps you can find the columns you have that
correspond.

Table1 is the table with all your trip data. TableX is the table of all
types of travel (assuming you have one).

A query like this is probably best built using code, concatenating the 12
pairs of "subqueries". The Procedure that does this should accept
parameters like the starting month and year you want ot use.

I hope this will help. It is difficult to give specific suggestions without
details from which to start.

Tom Ellison

 

[LarryJ]

Thanks for the thought Tom. I use to do everything in SQL, back in my
Oracle days, but I didn't think about doing this in pure SQL. If the
option given by Allen in the other newsgroup doesn't work, I'll go with
this one.