Converting Date range to monthly report

I need to create a resource available database.  The tables I have are the resources items, the user info, and various other information tables.

I have crosstab Query that will show me the information almost how I want it.  The user table has a start date and end date info.  The issue I have is I've only gotten the crosstab query to show the start date or end date.  What I want is for it to show me the months the resource is used.

Data is entered as
Resource:   Item 1
User:  Joe
Start date:   2/1/08
End date: 4/1/08

Resource: Item 2
User: Amy
Start date: 1/1/08
End date:  5/1/08

Crosstab shows like this
                Jan     Feb     Mar     Apr     May     Jun     Jul     Aug     Sep     Oct     Nov     Dec
Item 1                 Joe
Item 2     Amy


Want it to look like this:
                Jan     Feb    Mar    Apr    May
Item 1                 Joe    Joe     Joe
Item 2      Amy    Amy   Amy   Amy  Amy

I thought if I could create a table from the information entered that would list all of the months the resource is used, the crosstab query would work.  It is creating that table I am having an issue with, and is that the best route.

I do not need to edit the data as I want it to look, will require the user to go back and change the start/end dates.

Thanks,
Shane
turbow75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
You need a small Nums table containing the integer field Num with values 0 to 12.  Then use that table in you crosstab as a Cartesian Join.  Post the SQL from your crosstab and I'll show you what to do.  All start and end dates are alway firsts of the month?  What about different years?
0
turbow75Author Commented:
Here is the SQL.  The DataStartDate parameter was to just so I could change when the 12 month data was shown (will always be 1/1/XX)

Yes, most start/end dates are on the first.  There could be a case where the start date is mid-month, but the asset is still tied up for that month.

Yes, Start/End dates could also cross over years

PARAMETERS DataStartDate DateTime;
TRANSFORM First([Active Contracts].[Client Name]) AS [FirstOfClient Name]
SELECT [Active Contracts].Address
FROM [Active Contracts]
WHERE ((([Active Contracts].StartDate)>=[DataStartDate]))
GROUP BY [Active Contracts].ID, [Active Contracts].Address, [Active Contracts].ContractID
PIVOT Year([StartDate])*12+Format([StartDate],"mm")-(Year([DataStartDate])*12+Format([DataStartDate],"mm"))+1 In (1,2,3,4,5,6,7,8,9,10,11,12);
0
GRayLCommented:
PARAMETERS DataStartDate DateTime;
TRANSFORM First([Active Contracts].[Client Name]) AS [FirstOfClient Name]
SELECT [Active Contracts].Address,Year(DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15)) as ConYear
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15) BETWEEN
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY [Active Contracts].ID, [Active Contracts].Address
Year(DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15))
PIVOT Format(DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15),"mmm") IN
("Jan","Feb","Mar","Apr","May","Jun""Jul","Aug","Sep","Oct","Nov","Dec")

Give this a shot.  Remember the Nums table - integer values 0 to 11 (not 12)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

turbow75Author Commented:
I get the following error:

Syntax error (missing operator) in query expression 'Year(DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15)) as ConYear
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15) BETWEEN
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY ['.

Slightly familiar with SQL, does it matter what line they are on?  

I have the table Nums with on item Num integer 0-11
0
GRayLCommented:
It doesn't matter what line.  I missed a closing parenthesis in the Year() function:

PARAMETERS DataStartDate DateTime;
TRANSFORM First([Active Contracts].[Client Name]) AS [FirstOfClient Name]
SELECT [Active Contracts].Address,Year(DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15)) as ConYear
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15) BETWEEN
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY [Active Contracts].ID, [Active Contracts].Address
Year(DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15))
PIVOT Format(DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15),"mmm") IN
("Jan","Feb","Mar","Apr","May","Jun""Jul","Aug","Sep","Oct","Nov","Dec")
0
turbow75Author Commented:
New error

Syntax error (missing operator) in query expression '[Active Contracts].Address,Year(DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15))'.
0
GRayLCommented:
Comma after Address end of line 7 is all I can see.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
turbow75Author Commented:
Also a missing comma between Jun & Jul on the last line.  But that is what I needed.  Thanks for the help!
0
turbow75Author Commented:
Thanks again for your help, I've got a lot to learn about SQL
0
GRayLCommented:
Thanks for the catch, and the grade.  Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.