Function that relates to multiple fields

[rpboll]

I am trying to develop a query for a poorly developed database as
follows:

ProjName Area1 Area2 Area3 ... Area14 Area1Pct Area2Pct Area3Pct ...
Area14Pct Cost
Unicorn T T
30 70 $876
Alpha T 100
$543
Zulu T T
40 60 $927

The "Ts" are True/False and if there more than one for a project the
Cost is shared via the Area Percentages. So Area 2 & 3 are involved
with project Unicorn and share 30% and 70% of the $876 cost
respectively.

The function return should display a result like included in the rows
above.

ProjName Areas Cost
Unicorn 2, 3 2($262.8), 3($613.2)
Alpha 1 1($543)
Zulu 2, 3 2($370.8), 3(556.2)

Thanks for any help with this one.

RBollinger

 

[Allen Browne]

Not worth the effort.

By the time you have gone to all the trouble of generating calculated fields
by manually checking each one of looping through them programmatically, they
will have added extra fields, or changed the content, and your effort will
have been wasted.

It would be a better use of your time to redesign the table so it is
normalized, and thus queryable.

 

[rpboll]

Unfortunately this is not an option for me. That's why I said that it
is poor design to start with. This is a production table that I have
to work with.

I was thinking that two functions -- one that puts everything into one
field. Like:
Function1:
expr1:
IIf([AREA1]=True,1,IIf([AREA2]=True,2,IIf([AREA3]=True,3,IIf([AREA4]=True,4,""))))
etc...
But there are 14 areas and it blows up as being too complex. I was
thinking of doing it as a vba case statement but need help.

Function2 would need to do the same as function1 (i.e. bring everything
into one column) but deal with the percentage tables.

Any help with the functions greatly appreciated!

 

[Allen Browne]

Open the VBA window, and see help on Select Case if you want to go that way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Unfortunately this is not an option for me. That's why I said that it
is poor design to start with. This is a production table that I have
to work with.

I was thinking that two functions -- one that puts everything into one
field. Like:
Function1:
expr1:
IIf([AREA1]=True,1,IIf([AREA2]=True,2,IIf([AREA3]=True,3,IIf([AREA4]=True,4,""))))
etc...
But there are 14 areas and it blows up as being too complex. I was
thinking of doing it as a vba case statement but need help.

Function2 would need to do the same as function1 (i.e. bring everything
into one column) but deal with the percentage tables.

Any help with the functions greatly appreciated!

Not worth the effort.

By the time you have gone to all the trouble of generating calculated
fields
by manually checking each one of looping through them programmatically,
they
will have added extra fields, or changed the content, and your effort
will
have been wasted.

It would be a better use of your time to redesign the table so it is
normalized, and thus queryable.