Utilisation report - multiple queries into one report

[Gorf]

I have a three-column query that tells me how many hours I have available per week for a given resource type (e.g. welders). I have a second three-column query that tells me how many hours of work I have planned per week for a given resource type.

I'm hoping to produce a query (the source for a report) that will show resource types in rows and twelve months in 24 columns. the first column for each month will show how many hours I have available for all my resources, the second column for each month will show how many hours I have allocated.

How do I produce a query that will combine the other two queries, inserting zeroes where necessary considering that for any given week I might have allocated work to a resource that isn't available (because the inconsiderate buggers think they are entitled to holiday) or I might have a resource that has no work allocated (because I'm incompetent)?

 

[Uncle Gizmo]

Reading your post I am not getting a clear picture of your problem. There are too many questions requiring 2 many answers in one statement.

I would suggest you think about your problem and boil it down into a set of steps that you think you should take.

 

[Gorf]

Thanks for reading, Giz

The first part - I need to combine two queries (see attached) to provide combined data (see attached spreadsheet mockup - note highlighted row). This will be the "driver" for the report.

The report itself will have rows made up of "Resource type", columns made up of "MonthNum" (values 0 to 11 for a years worth of data) and the two hour totals, availability and estimate, will go side-by-side in each report cell.

 

[Uncle Gizmo]

Giving your question a little bump...

 

[vbaInet]

Two ways:

1. Two crosstab queries - one for Availability and the other for Estimated. Join both queries via ResourceType and display the fields from both queries side-by-side. You'll need to format it to return the Months alone and fix the column headers.
2. Creating a custom crosstab - group by ResourceType and use an IIF() function and repeat it 24 times (12 for Availibility and the other 12 for Estimated). Here's an example for the Availability column for January:

Jan_Avail: Min(IIF(Month([WeekCommencing])=1, [SumOfAvailabilityHours], Null))

... watch out for syntax errors of course

Option 2 should run quicker.

 

[Gorf]

Thanks, everyone, for the replies.

I still struggled to get the crosstab queries to do what I wanted. When I combined them, there were two rows (in places) for each of jobs and availability when they shared the same week commence date and resource type.

In the end, I had to do it in the report's VBA which I was trying to avoid. Build an array in OnLoad, print the array using a detail area fed by a union query,

 

[vbaInet]

If you were struggling to do it you could have simply come back and ask us.

 

[Gorf]

If you were struggling to do it you could have simply come back and ask us.

True, but I needed to get it out of the way to move onto the next part of development. I can come back to it later and replace the VBA with a query, if it's even possible.

 

[vbaInet]

Let us know when you're ready.