resource loading per month
Forum Sponsor
Top Posters
Josephus Enot
1 posts
Julian Pegg
1 posts
Peter Nagy
2 posts
Raymund de Laza
17 posts
Syed_Asad
0 posts
Tony Greyvenstein
0 posts
Ahmed Al-Jubouri
13 posts
Umar Alvi
3 posts
Sibusiso Mahlalela
0 posts
Michael Samanyayi
3 posts
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?