resource loading per month

ok solved it by myself. posting here so someone trying to do the same thing will find it.



under access, set query tables ACT and RES to link (8->1) on ACTID (result: qr_res).



also create a table with all dates (daily) in range (not very elegant) (result: tb_dates).



then do a crosstab transpose with the result tables with

dates as row heading,

ResID as column heading,

BQperDay: Sum([BudgetQuantity]/([ef]-[es]+1))

dates criteria >=[qr_res].[es] And <=[qr_res].[ef]



the resulting SQL should be something like this:



TRANSFORM Sum([BudgetQuantity]/([ef]-[es]+1)) AS BQ1

SELECT tb_dates.dates

FROM qr_res, tb_dates

WHERE (((qr_res.ResID) Like "mhxc*") AND ((tb_dates.dates)>=[qr_res].[es] And (tb_dates.dates)<=[qr_res].[ef]))

GROUP BY tb_dates.dates, tb_dates.dates

PIVOT qr_res.ResID;



beware, you will probably need to do a filter on a ResID or other to limit the run as it takes like forever. :/



hope for the best, plan for the worst.

Göksu

there is an example of a similar graph in the CD that is using Ra.



I need to use ODBC.



Also Ra example is for one RES or ALL but no example for selection. How do we define the selection?