How to allocate half values of shared objects

[Dick de Jong]

We have many widgets. Each is owned by A, or by B, or jointly (& equally) by A and B. Every widget has a financial value.

How do I produce a report listing all widgets & their values, sorted by value accrueing to each of A and to B? A's assets would = sum of all 'A' widgets + 0.5 value of all 'AB' widgets. Similarly for B. How do I work that into a report or its underlying query? Many thanks for any ideas. D.

 

[shudini]

It shouldn't be too hard, but just to get a clearer picture of the data, could you either itemize your fields, what they can contain, and how you would like to display them on a report?

 

[Dick de Jong]

It shouldn't be too hard, but just to get a clearer picture of the data, could you either itemize your fields, what they can contain, and how you would like to display them on a report?

Shuie, thanks for responding. Here is more detail. Two partners, A and B, own shares on the stock market. Some are owned by one partner, others are owned jointly in equal part by both. The shares table shows the title of each share and an 'owner' field contains either 'A' or 'B' or, for the jointly-owned shares, 'AB'. A values table has the share ID, date and value, & records values at particular dates. A query with the two tables gives the value of each share for any date at which the values were recorded. The tax authority needs the total value held by each partner at the year end. So A's wealth is the sum of the values of all the 'A' shares (i.e. shares that A owns) PLUS the sum of the half-values of all the 'AB' shares. I have not been able to devise a query to do this, and will be very grateful if you can solve this. I am not a programmer (actually a tropical agriculturalist) but have a reasonable understanding of tables, queries & reports.

 

[shudini]

If i understand you correctly, your query is returning fldOwner, fldDate, fldValue. The easiest way to create a report the way you want it would be to add another two columns - OwnerA_Value, OwnerB_Value. These columns would be derived columns, so the SQL syntax would be

SELECT...,iif([fldOwner]='A',[fldValue],iif[fldOwner]='AB',[fldValue]/2,0) as OwnerA_Value, 
iif([fldOwner]='B',[fldValue],iif[fldOwner]='AB',[fldValue]/2,0) as OwnerB_Value,

This code should effectively provide you with a column that would show each Owner's share of that stock.
Once you bind this query to a report, you can add 2 Text Boxes to the report footer, one with =Sum(OwnerA_Value) as the text and the other with =Sum(OwnerB_Value).

Good Luck!

 

[Dick de Jong]

Thanks, Shuie, I will try that.