Counting Related Records

[Greg_Brown_(Guest)]

I am trying to create a report to calculate a rent per unit.____Rent is stored in a Property table and Unit information is stored in the Unit table.____For each Property there can be more than one Unit and what I want to be able to do is to count the number of Units (that meet specific criteria) that are related to that property. This is to enable me to divide the rent by the number of Units and get the rent per Unit.__(Sorry about the long winded explanantion)____Eg. __P= Property No__U= Unit No__N= No of Units associated with Property__Rent= Rent of property (will not always be constant)__RU = Rent per Unit____I am trying to calculate column N____ P U N Rent RU__ 1 1 1 100 100__ 2 1 2 100 50__ 2 2 2 100 50__ 3 1 3 100 33.3__ 3 2 3 100 33.3__ 3 3 3 100 33.3____Has anyone any ideas?____

[freitas]

P= Property No__ U= Unit No__ N= No of Units associated with Property__ Rent= Rent of property (will not always be constant)__ RU = Rent per Unit____ I am trying to calculate column N____ P U N Rent RU__ 1 1 1 100 100__ 2 1 2 100 50__ 2 2 2 100 50__ 3 1 3 100 33.3__ 3 2 3 100 33.3__ 3 3 3 100 33.3____Do a total by property for N and Rent first. sorting everything by Property. Now create a footer on property and divide the (Total of Rent) by the (Total of Units). ____cpu = Total of Rent / Total of Units____The footer line is where you would put the calcualted field of cpu______

[ColinDow]

Im not entirely sure of your data structure but do be aware of possible issues when calculating totals.____You mention selecting units based on criteria. If you are getting to a total number of units by counting, applying a filter will alter your results.____e.g if you have a property that has rent of 100 euros (everyones heard of the euro right ?)____That property has 4 units of which__2 are 100 sq metres __2 are 200 sq metres.____If you apply the criteria of ____show me units greater than 100 sq metres then a unit count will be 2 and therefore rent per unit will calculate to 50 euros.____However we know we have 4 units so rent per unit is more likely 25 euros (depending on how u want to allocate that rent).____I apologise if this is obvious.__

[Greg_(Guest)]

Thank you,____When i do this i only get one result per property - when in fact i was after one result per property.____I think i can probably concede this in the report though - unless there^s a simple way around the problem.____Thanks again

[Greg_Brown_(Guest)]

sorry, i meant i was after one record per unit not one record per property

[Colin_Dow_(Guest)]

You can use pre-processed totals to calculate on a line by line basis