Date columns

[Sr Accountant]

How can I take a table that has a date column and an cost column, and create
a report that shows each date in a separate column?

Example of Current table:

Physician Date Group Edited Lines Expense (Cost)
Dr #1 8/31/09 XYZ 450 150.00
9/30/09 XYZ 1423 350.00

I would like my report to show each month across the page, as my example
below shows:

Physician Aug 2009 Sept 2009 Total Expense (adding all months together)
Dr #1 450 1423 $500.00

Is there anyway to do this, or will I have to create separate tables for
each month?

Thank you!

 

[Jerry Whittle]

Please don't create a column for each month!

The first thing that you need to do is to create a query that gets rid of
the day in the Date field. Call it something like qryPhysicianExpense. Below
is an example, buy you need to modify it with the correct table and field
names.

SELECT PhysicianExpense.Physican,
Format([Date],"mmmm yyyy") AS TheDate,
PhysicianExpense.Group,
PhysicianExpense.[Edited Lines],
PhysicianExpense.[Expense (Cost)]
FROM PhysicianExpense;

Next use this query to create a crosstab query.

TRANSFORM Sum(qryPhysicianExpense.[Expense (Cost)]) AS [SumOfExpense (Cost)]
SELECT qryPhysicianExpense.[Physican], qryPhysicianExpense.[Edited Lines],
Sum(qryPhysicianExpense.[Expense (Cost)]) AS [Total Of Expense (Cost)]
FROM qryPhysicianExpense
GROUP BY qryPhysicianExpense.[Physican], qryPhysicianExpense.[Edited Lines]
PIVOT qryPhysicianExpense.[TheDate];

The above will get you close, but not quite what you want. It will list
Edited Lines by row and the Expense (Cost) by month and totaled.

One other problem is that the Months go across in alphabetical order and not
by date. If you change the first query from

Format([Date],"mmmm yyyy") AS TheDate,

to

Format([Date],"yyyy mm") AS TheDate,

it will then sort across correctly, but look like 2009 08, 2009 09, etc.