SQL design vs VB design | | |
Dear valuable experts,
I truly hope than You can suggest for me Your ideas how to resolve
design.
I developing relative small Access VB tool, for single user use only.
Access version 2003, but db saved for compatibility as ver. 2000.
Basic structure of this tool is dependent from my house departments
structure.
Each department owes their own table, where they can write inputs
necessary and dependent for annual budget analysis and estimates
spendings. Every table each row number or "account" number is
preformated - means is strongly regulated by our finance structure and
therefore can contain some numeric (financial) values or can be empty.
Like in this sample:
Account___Jan_Feb_Mar..etc
6110______123,00_234,00_432,00_22,00...etc
6125______45,45_678,03_12,01_87,10...etc
......
First steps was, I collect together values from each single department
tables in according to each rows account number.
Then I need calculate subtotals for each account logical group
subdivisions.
Like for this accounts 6199 = 6110+6125+6130.
For this purpose I created filtered queries, where only necessary
accounts come visible, and from those I extract each months total
values via DSum command. Works just fine from my form and I free on my
decision - either show values only on my screen, or output to new empty
table and then output by report.
My current problem is - choose the optimal design for very special
cases where totals is created by summing ones accounts and then
extracting by some others:
like this f.e. 9899 = 6199+7299-7533-7720+8100
One of my ideas is, create for each of these values another temporary
variable at form or subs level, then use dlookup to get those actual
values from tables following upper mentioned formula steps.
I not sure this is good idea! Because I need repeat this for each month
respectively.
My another opinions against of use of such complicated dlookup
structure - total performance can fall down.
Another idea is to use SQL design layout. First I filter accounts in
two subquery groups - one group where only pluses, respectively those
accounts where values can be totaled. Another group - for accounts
together who can be later extracted from first group totals.
Sorry for my long letter, but is this idea clear for You?
Hope - anybody can point me ahead with pros and cons -..
Thank You for Your time in advance-
Sincerely
Ainars.
| | | | re: SQL design vs VB design
Hello again,
I see no answers to my question and for this can be at least two
reasons.
Summer holidays and very hot time around in Europe can make impact on
activity level of this n/g subscribers.
But another reason can be my yesterdays unclear formulation of my
problem.
In hope than someone with more experiences in Access VB can suggest
right way for exit out of current case.
So, in short again.
If You need extract values form multiple tables, then calculate
resulted totals and then like to store results in another temporary
table, what method do You use.
And what if - if You need ones values to sum on this, but other values
then extract from total?
Do You use pure SQL level calculations or is there idea than VB subs or
functions with Dlookup or Dsum is good enough for this purpose too?
For me it looks than more or less but both methods is complicated
enough because in correspondence to each account number there is (or
there is not) some integer values corresponding to each calendar
month..
While developing this small data collection/conversion tool I also need
keep in mind than my end user can be allowed to edit these formulas
later as necessary.
So - my general rule is "perfection in simplicity" :)
Not very easy for average programmer - and this is why I asked for Your
assistance.
Hope for any feedback in advance
Rgrds =
Ainars.
P.SI know than store back in table any aggregated or calculated
result is poor programmer methods, but as I mentioned before - this is
temporary data table.
Each time my user opens this continous form to read actual values -
there is warning popup message.
After user accept, all calculated table rows come deleted and new data
recorded in place. It takes 15- 30 sec on my Pentium4 computer.
A_M_IS wrote: Quote:
Dear valuable experts,
I truly hope than You can suggest for me Your ideas how to resolve
design.
I developing relative small Access VB tool, for single user use only.
Access version 2003, but db saved for compatibility as ver. 2000.
>
Basic structure of this tool is dependent from my house departments
structure.
Each department owes their own table, where they can write inputs
necessary and dependent for annual budget analysis and estimates
spendings. Every table each row number or "account" number is
preformated - means is strongly regulated by our finance structure and
therefore can contain some numeric (financial) values or can be empty.
Like in this sample:
Account___Jan_Feb_Mar..etc
6110______123,00_234,00_432,00_22,00...etc
6125______45,45_678,03_12,01_87,10...etc
.....
First steps was, I collect together values from each single department
tables in according to each rows account number.
>
Then I need calculate subtotals for each account logical group
subdivisions.
Like for this accounts 6199 = 6110+6125+6130.
>
For this purpose I created filtered queries, where only necessary
accounts come visible, and from those I extract each months total
values via DSum command. Works just fine from my form and I free on my
decision - either show values only on my screen, or output to new empty
table and then output by report.
>
My current problem is - choose the optimal design for very special
cases where totals is created by summing ones accounts and then
extracting by some others:
like this f.e. 9899 = 6199+7299-7533-7720+8100
>
One of my ideas is, create for each of these values another temporary
variable at form or subs level, then use dlookup to get those actual
values from tables following upper mentioned formula steps.
>
I not sure this is good idea! Because I need repeat this for each month
respectively.
My another opinions against of use of such complicated dlookup
structure - total performance can fall down.
>
Another idea is to use SQL design layout. First I filter accounts in
two subquery groups - one group where only pluses, respectively those
accounts where values can be totaled. Another group - for accounts
together who can be later extracted from first group totals.
>
Sorry for my long letter, but is this idea clear for You?
Hope - anybody can point me ahead with pros and cons -..
Thank You for Your time in advance-
Sincerely
Ainars.
| | | | re: SQL design vs VB design
Create another table with ledger codes or whatever that
you can join to, to get the sign of the sum
6199 = (+1)*6110 + (+1)*6125 + (-1)*6130 + (0)*6145
then you can sum all values in a simple query.
If the tables are compatible, use a union query to
join all the departments together.
(david)
"A_M_IS" <aimis@iname.comwrote in message
news:1152529956.853721.106650@h48g2000cwc.googlegr oups.com... Quote:
Dear valuable experts,
I truly hope than You can suggest for me Your ideas how to resolve
design.
I developing relative small Access VB tool, for single user use only.
Access version 2003, but db saved for compatibility as ver. 2000.
>
Basic structure of this tool is dependent from my house departments
structure.
Each department owes their own table, where they can write inputs
necessary and dependent for annual budget analysis and estimates
spendings. Every table each row number or "account" number is
preformated - means is strongly regulated by our finance structure and
therefore can contain some numeric (financial) values or can be empty.
Like in this sample:
Account___Jan_Feb_Mar..etc
6110______123,00_234,00_432,00_22,00...etc
6125______45,45_678,03_12,01_87,10...etc
.....
First steps was, I collect together values from each single department
tables in according to each rows account number.
>
Then I need calculate subtotals for each account logical group
subdivisions.
Like for this accounts 6199 = 6110+6125+6130.
>
For this purpose I created filtered queries, where only necessary
accounts come visible, and from those I extract each months total
values via DSum command. Works just fine from my form and I free on my
decision - either show values only on my screen, or output to new empty
table and then output by report.
>
My current problem is - choose the optimal design for very special
cases where totals is created by summing ones accounts and then
extracting by some others:
like this f.e. 9899 = 6199+7299-7533-7720+8100
>
One of my ideas is, create for each of these values another temporary
variable at form or subs level, then use dlookup to get those actual
values from tables following upper mentioned formula steps.
>
I not sure this is good idea! Because I need repeat this for each month
respectively.
My another opinions against of use of such complicated dlookup
structure - total performance can fall down.
>
Another idea is to use SQL design layout. First I filter accounts in
two subquery groups - one group where only pluses, respectively those
accounts where values can be totaled. Another group - for accounts
together who can be later extracted from first group totals.
>
Sorry for my long letter, but is this idea clear for You?
Hope - anybody can point me ahead with pros and cons -..
Thank You for Your time in advance-
Sincerely
Ainars.
>
| | | | re: SQL design vs VB design
Hello,
thank You David, for idea!
I just starting to brain out now, how to implement this multiplier
prefix in my case.
In fact - I already have another separate table with those ledger codes
and also with codes descriptions.
So, I have continuous form which is bind to new empty table.
In forms header I have command button to allow for end user load fresh
data each time. This code also tests for record count in bind table -
if any exists, then ask for delete action and load another fresh data.
Button code runs with two open recordsets - one rs with ledger codes
and another rs - empty table.
If ledger code is "regular" :) , then code just calling sub action and
collect data from eight separate department tables, and sum together if
found any inputs. Then sum is recorded to new table recordset according
to each month values.
(On this - how I understand from Your suggestion, I can simply use
Union select query. Why not? I can change my code, because I believe, I
can save on total running time. SQL union select action can run faster
than Dsum/Dlookup.)
Then, if ledger code is with **99 or even in some another cases with
very special codes - those I select out each time by filtering on the
fly, with select case code.
Then this code again runs easy until subdivisions sum.
Then I do dsum/lookup to named selection query, where only I selected
divisions of ledger codes together with month input values.
Yeah - I think I got it finally! Thank You again for Your smart advice!
Why not do implement Your idea this way?
First I can select all ledger rows with positive meaning values.
Extract values same way as I do with simplest cases and keep in
variables.
Then select seperately (give Query name with prefix "Neg") to another
ledger group rows whose values need to be extracted from first ones.
Then do my regular selected Dsum action and resulted values for each
month just multiply with (-1) and then sum again with earlier stored
values?
Even, how I think right now, is it not necessary to multiply each time
this with prefix...
(see my actual code:)
Case 8599
c1 = Nz(DSum("[m1]", "Q85Sum"), 0) 'there do lookup and store away my
regular values
c2 = Nz(DSum("[m2]", "Q85Sum"), 0)
c3 = Nz(DSum("[m3]", "Q85Sum"), 0)
....
why not can I just modify like this...
Case 8599
c1 = Nz(DSum("[m1]", "Q85Sum"), 0) - Nz(DSum("[m1]", "Q85SumNeg"), 0)
c2 = Nz(DSum("[m2]", "Q85Sum"), 0) - Nz(DSum("[m2]", "Q85SumNeg"), 0)
c3 = Nz(DSum("[m3]", "Q85Sum"), 0) - Nz(DSum("[m3]", "Q85SumNeg"), 0)
....
Is this the same with how You imagine on this? As I think this is not
absolute equal to Your idea, but I feel Myself You pointed me
positively!
What is still a bit unclear - and I see is discussed many times before
in this n/g, but only for different cases -
If developer need to agregate some values (like -sum, extract,
multiply, etc) then what method is most recommended one - VB method or
as I said there, pure SQL method?
I come to change my code now, then be back later with results.
Regards to You and to others, who started to think about my coding
question!
Ainars
david epsom dot com dot au wrote: Quote:
Create another table with ledger codes or whatever that
you can join to, to get the sign of the sum
>
6199 = (+1)*6110 + (+1)*6125 + (-1)*6130 + (0)*6145
>
then you can sum all values in a simple query.
>
If the tables are compatible, use a union query to
join all the departments together.
>
(david)
>
>
>
"A_M_IS" <aimis@iname.comwrote in message
news:1152529956.853721.106650@h48g2000cwc.googlegr oups.com... Quote:
Dear valuable experts,
I truly hope than You can suggest for me Your ideas how to resolve
design.
I developing relative small Access VB tool, for single user use only.
Access version 2003, but db saved for compatibility as ver. 2000.
Basic structure of this tool is dependent from my house departments
structure.
Each department owes their own table, where they can write inputs
necessary and dependent for annual budget analysis and estimates
spendings. Every table each row number or "account" number is
preformated - means is strongly regulated by our finance structure and
therefore can contain some numeric (financial) values or can be empty.
Like in this sample:
Account___Jan_Feb_Mar..etc
6110______123,00_234,00_432,00_22,00...etc
6125______45,45_678,03_12,01_87,10...etc
.....
First steps was, I collect together values from each single department
tables in according to each rows account number.
Then I need calculate subtotals for each account logical group
subdivisions.
Like for this accounts 6199 = 6110+6125+6130.
For this purpose I created filtered queries, where only necessary
accounts come visible, and from those I extract each months total
values via DSum command. Works just fine from my form and I free on my
decision - either show values only on my screen, or output to new empty
table and then output by report.
My current problem is - choose the optimal design for very special
cases where totals is created by summing ones accounts and then
extracting by some others:
like this f.e. 9899 = 6199+7299-7533-7720+8100
One of my ideas is, create for each of these values another temporary
variable at form or subs level, then use dlookup to get those actual
values from tables following upper mentioned formula steps.
I not sure this is good idea! Because I need repeat this for each month
respectively.
My another opinions against of use of such complicated dlookup
structure - total performance can fall down.
Another idea is to use SQL design layout. First I filter accounts in
two subquery groups - one group where only pluses, respectively those
accounts where values can be totaled. Another group - for accounts
together who can be later extracted from first group totals.
Sorry for my long letter, but is this idea clear for You?
Hope - anybody can point me ahead with pros and cons -..
Thank You for Your time in advance-
Sincerely
Ainars.
| | | | re: SQL design vs VB design
Pure SQL is faster, if you can get the logic right so that it works.
If you can't get the logic right for pure SQL, it means your tables
aren't designed right.
It is better to get your table design right, because it makes
complicated things easier.
In this newsgroup we talk about complicated things. If it was only
simple, table design and pure SQL would not matter, but we would
not talk about it here. Quote:
c3 = Nz(DSum("[m3]", "Q85Sum"), 0) - Nz(DSum("[m3]", "Q85SumNeg"), 0)
This takes twice as long, because two calls to DSum take twice
as long as one call to DSum.
If you only have simple reports and a small amount of data, it
does not matter how long it takes.
I have reports that take 15 minutes if I do not care about how fast
they are. I make them faster, and they are less than 1 second!
If they were only 3 seconds I would not care, but 15 minutes is too long.
(david)
"A_M_IS" <aimis@iname.comwrote in message
news:1152697738.847549.315710@35g2000cwc.googlegro ups.com... Quote:
Hello,
thank You David, for idea!
>
I just starting to brain out now, how to implement this multiplier
prefix in my case.
>
In fact - I already have another separate table with those ledger codes
and also with codes descriptions.
>
So, I have continuous form which is bind to new empty table.
In forms header I have command button to allow for end user load fresh
data each time. This code also tests for record count in bind table -
if any exists, then ask for delete action and load another fresh data.
>
Button code runs with two open recordsets - one rs with ledger codes
and another rs - empty table.
If ledger code is "regular" :) , then code just calling sub action and
collect data from eight separate department tables, and sum together if
found any inputs. Then sum is recorded to new table recordset according
to each month values.
(On this - how I understand from Your suggestion, I can simply use
Union select query. Why not? I can change my code, because I believe, I
can save on total running time. SQL union select action can run faster
than Dsum/Dlookup.)
>
Then, if ledger code is with **99 or even in some another cases with
very special codes - those I select out each time by filtering on the
fly, with select case code.
Then this code again runs easy until subdivisions sum.
Then I do dsum/lookup to named selection query, where only I selected
divisions of ledger codes together with month input values.
>
Yeah - I think I got it finally! Thank You again for Your smart advice!
>
Why not do implement Your idea this way?
First I can select all ledger rows with positive meaning values.
Extract values same way as I do with simplest cases and keep in
variables.
Then select seperately (give Query name with prefix "Neg") to another
ledger group rows whose values need to be extracted from first ones.
Then do my regular selected Dsum action and resulted values for each
month just multiply with (-1) and then sum again with earlier stored
values?
Even, how I think right now, is it not necessary to multiply each time
this with prefix...
(see my actual code:)
>
Case 8599
c1 = Nz(DSum("[m1]", "Q85Sum"), 0) 'there do lookup and store away my
regular values
c2 = Nz(DSum("[m2]", "Q85Sum"), 0)
c3 = Nz(DSum("[m3]", "Q85Sum"), 0)
...
>
why not can I just modify like this...
>
Case 8599
c1 = Nz(DSum("[m1]", "Q85Sum"), 0) - Nz(DSum("[m1]", "Q85SumNeg"), 0)
c2 = Nz(DSum("[m2]", "Q85Sum"), 0) - Nz(DSum("[m2]", "Q85SumNeg"), 0)
c3 = Nz(DSum("[m3]", "Q85Sum"), 0) - Nz(DSum("[m3]", "Q85SumNeg"), 0)
...
>
Is this the same with how You imagine on this? As I think this is not
absolute equal to Your idea, but I feel Myself You pointed me
positively!
What is still a bit unclear - and I see is discussed many times before
in this n/g, but only for different cases -
If developer need to agregate some values (like -sum, extract,
multiply, etc) then what method is most recommended one - VB method or
as I said there, pure SQL method?
>
I come to change my code now, then be back later with results.
Regards to You and to others, who started to think about my coding
question!
Ainars
>
>
david epsom dot com dot au wrote: Quote:
>Create another table with ledger codes or whatever that
>you can join to, to get the sign of the sum
>>
>6199 = (+1)*6110 + (+1)*6125 + (-1)*6130 + (0)*6145
>>
>then you can sum all values in a simple query.
>>
>If the tables are compatible, use a union query to
>join all the departments together.
>>
>(david)
>>
>>
>>
>"A_M_IS" <aimis@iname.comwrote in message
>news:1152529956.853721.106650@h48g2000cwc.googleg roups.com... Quote:
Dear valuable experts,
I truly hope than You can suggest for me Your ideas how to resolve
design.
I developing relative small Access VB tool, for single user use only.
Access version 2003, but db saved for compatibility as ver. 2000.
>
Basic structure of this tool is dependent from my house departments
structure.
Each department owes their own table, where they can write inputs
necessary and dependent for annual budget analysis and estimates
spendings. Every table each row number or "account" number is
preformated - means is strongly regulated by our finance structure and
therefore can contain some numeric (financial) values or can be empty.
Like in this sample:
Account___Jan_Feb_Mar..etc
6110______123,00_234,00_432,00_22,00...etc
6125______45,45_678,03_12,01_87,10...etc
.....
First steps was, I collect together values from each single department
tables in according to each rows account number.
>
Then I need calculate subtotals for each account logical group
subdivisions.
Like for this accounts 6199 = 6110+6125+6130.
>
For this purpose I created filtered queries, where only necessary
accounts come visible, and from those I extract each months total
values via DSum command. Works just fine from my form and I free on my
decision - either show values only on my screen, or output to new empty
table and then output by report.
>
My current problem is - choose the optimal design for very special
cases where totals is created by summing ones accounts and then
extracting by some others:
like this f.e. 9899 = 6199+7299-7533-7720+8100
>
One of my ideas is, create for each of these values another temporary
variable at form or subs level, then use dlookup to get those actual
values from tables following upper mentioned formula steps.
>
I not sure this is good idea! Because I need repeat this for each month
respectively.
My another opinions against of use of such complicated dlookup
structure - total performance can fall down.
>
Another idea is to use SQL design layout. First I filter accounts in
two subquery groups - one group where only pluses, respectively those
accounts where values can be totaled. Another group - for accounts
together who can be later extracted from first group totals.
>
Sorry for my long letter, but is this idea clear for You?
Hope - anybody can point me ahead with pros and cons -..
Thank You for Your time in advance-
Sincerely
Ainars.
>
>
| | | | re: SQL design vs VB design
Thanks David, for spent time on my problem!
About my tables designs - yes, I can agree absolute with You, than
design can always be better.
I will brain out once more on forthcoming days my db designs by keeping
in mind Your last sentences.
BTW - I am lucky one, because this is not very urgent programming task
for me by now.
Actually I missed little bit to finish this job with a glance at end of
June (first half year). :P
(Accouter calculated problematic fields by handheld calculator) :/
Now I have enough time to develop current tool to more advanced one,
before next quarter is over...
Current Dsum data loads takes for me about 15- 30 s. depending on PC
power. Not many new table rows is generated, about ~ 300 in total.
Load time is - how I think not very crucial, and absolute not necessary
to load data every open time.
Only when changes can be done by departments. Those changes by
departments I can track by implementing in each table "edit" triggers.
In future when system be finished - then those activities can come
frequently, I bear that in mind too.
Most difficult part is to create elegant formulas, where user can
adjust formulas on the fly where necessary, without bothering me each
time.
One way can be create another table - save there formulas as strings,
then load versions by user will.
But again that can be poor design sample, that's why I said - I will
rethink idea twice before to go ahead next week.
Thanks again to You for ideas and rgrds -
Ainars.
david epsom dot com dot au wrote: Quote:
Pure SQL is faster, if you can get the logic right so that it works.
>
If you can't get the logic right for pure SQL, it means your tables
aren't designed right.
>
It is better to get your table design right, because it makes
complicated things easier.
>
In this newsgroup we talk about complicated things. If it was only
simple, table design and pure SQL would not matter, but we would
not talk about it here.
> Quote:
c3 = Nz(DSum("[m3]", "Q85Sum"), 0) - Nz(DSum("[m3]", "Q85SumNeg"), 0)
>
This takes twice as long, because two calls to DSum take twice
as long as one call to DSum.
>
If you only have simple reports and a small amount of data, it
does not matter how long it takes.
>
I have reports that take 15 minutes if I do not care about how fast
they are. I make them faster, and they are less than 1 second!
>
If they were only 3 seconds I would not care, but 15 minutes is too long.
>
(david)
>
>
"A_M_IS" <aimis@iname.comwrote in message
news:1152697738.847549.315710@35g2000cwc.googlegro ups.com... Quote:
Hello,
thank You David, for idea!
I just starting to brain out now, how to implement this multiplier
prefix in my case.
In fact - I already have another separate table with those ledger codes
and also with codes descriptions.
So, I have continuous form which is bind to new empty table.
In forms header I have command button to allow for end user load fresh
data each time. This code also tests for record count in bind table -
if any exists, then ask for delete action and load another fresh data.
Button code runs with two open recordsets - one rs with ledger codes
and another rs - empty table.
If ledger code is "regular" :) , then code just calling sub action and
collect data from eight separate department tables, and sum together if
found any inputs. Then sum is recorded to new table recordset according
to each month values.
(On this - how I understand from Your suggestion, I can simply use
Union select query. Why not? I can change my code, because I believe, I
can save on total running time. SQL union select action can run faster
than Dsum/Dlookup.)
Then, if ledger code is with **99 or even in some another cases with
very special codes - those I select out each time by filtering on the
fly, with select case code.
Then this code again runs easy until subdivisions sum.
Then I do dsum/lookup to named selection query, where only I selected
divisions of ledger codes together with month input values.
Yeah - I think I got it finally! Thank You again for Your smart advice!
Why not do implement Your idea this way?
First I can select all ledger rows with positive meaning values.
Extract values same way as I do with simplest cases and keep in
variables.
Then select seperately (give Query name with prefix "Neg") to another
ledger group rows whose values need to be extracted from first ones.
Then do my regular selected Dsum action and resulted values for each
month just multiply with (-1) and then sum again with earlier stored
values?
Even, how I think right now, is it not necessary to multiply each time
this with prefix...
(see my actual code:)
Case 8599
c1 = Nz(DSum("[m1]", "Q85Sum"), 0) 'there do lookup and store away my
regular values
c2 = Nz(DSum("[m2]", "Q85Sum"), 0)
c3 = Nz(DSum("[m3]", "Q85Sum"), 0)
...
why not can I just modify like this...
Case 8599
c1 = Nz(DSum("[m1]", "Q85Sum"), 0) - Nz(DSum("[m1]", "Q85SumNeg"), 0)
c2 = Nz(DSum("[m2]", "Q85Sum"), 0) - Nz(DSum("[m2]", "Q85SumNeg"), 0)
c3 = Nz(DSum("[m3]", "Q85Sum"), 0) - Nz(DSum("[m3]", "Q85SumNeg"), 0)
...
Is this the same with how You imagine on this? As I think this is not
absolute equal to Your idea, but I feel Myself You pointed me
positively!
What is still a bit unclear - and I see is discussed many times before
in this n/g, but only for different cases -
If developer need to agregate some values (like -sum, extract,
multiply, etc) then what method is most recommended one - VB method or
as I said there, pure SQL method?
I come to change my code now, then be back later with results.
Regards to You and to others, who started to think about my coding
question!
Ainars
david epsom dot com dot au wrote: Quote:
Create another table with ledger codes or whatever that
you can join to, to get the sign of the sum
>
6199 = (+1)*6110 + (+1)*6125 + (-1)*6130 + (0)*6145
>
then you can sum all values in a simple query.
>
If the tables are compatible, use a union query to
join all the departments together.
>
(david)
>
>
>
"A_M_IS" <aimis@iname.comwrote in message
news:1152529956.853721.106650@h48g2000cwc.googlegr oups.com...
Dear valuable experts,
I truly hope than You can suggest for me Your ideas how to resolve
design.
I developing relative small Access VB tool, for single user use only.
Access version 2003, but db saved for compatibility as ver. 2000.
Basic structure of this tool is dependent from my house departments
structure.
Each department owes their own table, where they can write inputs
necessary and dependent for annual budget analysis and estimates
spendings. Every table each row number or "account" number is
preformated - means is strongly regulated by our finance structure and
therefore can contain some numeric (financial) values or can be empty.
Like in this sample:
Account___Jan_Feb_Mar..etc
6110______123,00_234,00_432,00_22,00...etc
6125______45,45_678,03_12,01_87,10...etc
.....
First steps was, I collect together values from each single department
tables in according to each rows account number.
Then I need calculate subtotals for each account logical group
subdivisions.
Like for this accounts 6199 = 6110+6125+6130.
For this purpose I created filtered queries, where only necessary
accounts come visible, and from those I extract each months total
values via DSum command. Works just fine from my form and I free on my
decision - either show values only on my screen, or output to new empty
table and then output by report.
My current problem is - choose the optimal design for very special
cases where totals is created by summing ones accounts and then
extracting by some others:
like this f.e. 9899 = 6199+7299-7533-7720+8100
One of my ideas is, create for each of these values another temporary
variable at form or subs level, then use dlookup to get those actual
values from tables following upper mentioned formula steps.
I not sure this is good idea! Because I need repeat this for each month
respectively.
My another opinions against of use of such complicated dlookup
structure - total performance can fall down.
Another idea is to use SQL design layout. First I filter accounts in
two subquery groups - one group where only pluses, respectively those
accounts where values can be totaled. Another group - for accounts
together who can be later extracted from first group totals.
Sorry for my long letter, but is this idea clear for You?
Hope - anybody can point me ahead with pros and cons -..
Thank You for Your time in advance-
Sincerely
Ainars.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,223 network members.
|
