maybe I can firstly have two separate LOAD statements - one for the holiday_budget, one for the days_taken - and then do two RESIDENT loads and join those`using a LEFT JOIN?
The question is - those two tables should be joined on the PN and the budget_type - so I have to rename the budget_type to have the same name in both tables, right?
that seems to WORK. Yippieh!
O_O - not quite:
Probably due to those JOINs - I have joined the RESIDENT loads from three tables now for
- the entire holiday_budget
- days_taken and
I can no more get the correct nr. of days - still, when I select a PN, a number of budgets are "selectable" and several numbers of days which, summed up, give me the correct value - but when I sum them up in a textbox, I get much, much more. Somewhere along the way, the records have multiplied.
...and it is logical that they would ;-) In the first table (holiday budget), there are, say, three records (for three different budgets) with three values - in the other tables (days taken and days planned), there is one record for every day and I have to count date_values.
I will do some more trying around.
now I'm almost there. I introduced one more RESIDENT load for the table where I have days_taken, summing up by PN and budget_type and counting the respective nr. of days.
I do the same with the third table where I have days_planned.
Now I have one last problem:
In the very first table, I have three records - three different budgets with different nrs of days which I have to sum up. I could do one more RESIDENT load and sum these up beforehand, but I need all three budgets to filter (through the JOIN) the second table - that one has more budget_types.
I do a RESIDENT load again for the third table and get the correct value - but when I join this to what I have, one record becomes three records (three budget_types) and I have three times the nr of days.
Nr_lines is 1 when it should be 3 - because I have 3 different budget_types in the original table.
The trouble is, in the LOAD statement I don't yet "know" there are 3 budget_types - there might be more, depending on which PN I select.
Somehow I have to reduce it somewhere along the way - after I have done all that I need the different budget_types for - to just one record per PN.
Thanks a lot!