Calculations Across Multiple Records

[silva]

I built a database for rental purposes and I'd like to adapt it in order to
help calculate per centages based upon square footage for real estate tax
purposes. I have a central table which contains the basic information for
each unit such as:

[unit]
[building]
[city_zip]
[tenant_name]

I want to add a field to store the square footage of each unit, but I am
unsure as to how I would go about using that information to calculate the per
centage owed by each tenant and store the information. I could easily do i in
Excel or something and do a copy-and-paste, or import, or data entry, or
whatever, but I'd like to keep it all done within the database to reduce the
opportunity for clerical errors.

So assuming I added fields like [square_foot] and [per_cent] to store the
square footage and per cent of the building (respectively), how would I go
about setting things up to do the calculations? I don't need super-specific
details, just some help figuring out how to do a computation across multiple
records and store the results.

 

[Steve Schapel]

Silva,

In a database, you would not normally store the percentage figure, as this
is a derived value from the existing data. Therefore it can easily be
retrieved, whenever you need it for your operational purposes, by a query.
Something like this...

Probably the simplest way to create such a query, would be to first make a
Totals Query, which returns the total square footage per tenant:
SELECT [tenant_name], Sum([square_foot] AS TenantArea
FROM YourTable
GROUP BY [tenant_name]

Then make another query to give the total square footage:
SELECT Sum([square_foot] AS TotalArea
FROM YourTable

Then make a third query that includes both of the above queries:
SELECT [tenant_name], [TenantArea]/[TotalArea] As TenantPercent
FROM YourFirstQuery, YourSecondQuery

 

[Steve Schapel]

Silva,

I see a typo in my earlier reply, with a missing ) should be
Sum([square_foot])

 

[Jeff Boyce]

Not only is a percentage a derived (?derivable) value as Steve points out,
percentages are calculated as some portion of a base amount. Unless the
base amount is exactly the same for every percentage, you risk attempting to
"do math" on the percentages, which makes about as much sense as calculating
the average 'gender' (Male = 1; Female = 2; Undecided = 3).

Regards

Jeff Boyce
Microsoft Office/Access MVP
(and recovering statistician)

 

[silva]

Ok, I think maybe I addressed this wrong. I mostly want to use this to
generate reports for items based upon the square footage of the rental space,
in which everything is based on the per centage of the building that the
space occupies. In some of these instances, though, there is a portion of the
building that is not rented as it is used for another part of our business,
but certain costs involved take that space into consideration as well. Now
I'm sure I could incorporate those queries into a report (and that seems to
be a better solution to what I'm doing than I was thinking), but how would I
modify those queries to include the non-rented square footage? I assume the
second query is the one I'd modify, but I haven't done much more than simple
queries with no real calculations involved.

Silva,

In a database, you would not normally store the percentage figure, as this
is a derived value from the existing data. Therefore it can easily be
retrieved, whenever you need it for your operational purposes, by a query.
Something like this...

Probably the simplest way to create such a query, would be to first make a
Totals Query, which returns the total square footage per tenant:
SELECT [tenant_name], Sum([square_foot] AS TenantArea
FROM YourTable
GROUP BY [tenant_name]

Then make another query to give the total square footage:
SELECT Sum([square_foot] AS TotalArea
FROM YourTable

Then make a third query that includes both of the above queries:
SELECT [tenant_name], [TenantArea]/[TotalArea] As TenantPercent
FROM YourFirstQuery, YourSecondQuery

--
Steve Schapel, Microsoft Access MVP

I built a database for rental purposes and I'd like to adapt it in order
to
help calculate per centages based upon square footage for real estate tax
purposes. I have a central table which contains the basic information for
each unit such as:

[unit]
[building]
[city_zip]
[tenant_name]

I want to add a field to store the square footage of each unit, but I am
unsure as to how I would go about using that information to calculate the
per
centage owed by each tenant and store the information. I could easily do i
in
Excel or something and do a copy-and-paste, or import, or data entry, or
whatever, but I'd like to keep it all done within the database to reduce
the
opportunity for clerical errors.

So assuming I added fields like [square_foot] and [per_cent] to store the
square footage and per cent of the building (respectively), how would I go
about setting things up to do the calculations? I don't need
super-specific
details, just some help figuring out how to do a computation across
multiple
records and store the results.

 

[Clifford Bass]

Hi Silva,

If you are going to have historical data, you will need to use several
tables. Also, you should not use the name as the identification of the
renter (or is that owner?). What happens when you have two John Smiths? And
you will need to deal with unoccupied units. Suggested structure as a start:

tblBuildings
BuildingID (primary key)
BuildingName
BuildingSquareFootage (if wanted, total including public areas, and other
non-tenant-owned parts)

tblUnits
BuildingID (primary key, along with next field)
UnitNumber
UnitSquareFootage

tblRenters
RenterID (primary key)
RenterLastName
RenterFirstName

tblRentals
BuildingID (primary key, along with next two fields)
UnitNumber
RenterID
StartDate
EndDate (null when the person is still renting)

This of course does not deal with recording historical changes to the
buildings such as the addition of new units or the changing of the sizes of
existing units. I will not address those issues, which greatly complicate
things.

Following queries not tested. To calculate the current building total
rental square footage you might do something like:

qryBuildingTotalUnitSquareFootages

select BuildingID, UnitNumber, Sum(UnitSquareFootage) As
BuildingTotalUnitSquareFootage
from tblUnits
group by BuildingID, UnitNumber;

To calculate the total square footage for each building's renter
(renters could have multiple units, this should include the unrented units as
a single item):

qryBuildingRenterTotalSquareFootages

select tblUnits.BuildingID, tblRenters.RenterID, RenterLastName,
RenterFirstName, Sum(UnitSquareFootage) as RenterTotalSquareFootage
from (tblUnits left join tblRentals on tblRentals.BuildingID =
tblUnits.BuildingID and tblRentals.UnitNumber = tblUnits.UnitNumber and
tblRentals.EndDate is null) left join tblRenters on tblRenters.RenterID =
tblRentals.RenterID
group by tblUnits.BuildingID, tblRenters.RenterID, RenterLastName,
RenterFirstName;

Now put it together:

select A.BuildingID, RenterID, RenterLastName, RenterFirstName,
RenterTotalSquareFootage, Round(RenterTotalSquareFootage * 100 /
BuildingTotalUnitSquareFootage, 2) as RenterPercentOwned
from qryBuildingRenterTotalSquareFootages as A inner join
qryBuildingTotalUnitSquareFootages as B on B.BuildingID = A.BuildingID

Again, those queries not tested, but gives as a starting point. You
could also do something similar to get total ownership across all buildings
if you have tenants that rent in more than one building.

Good luck,

Clifford Bass

I built a database for rental purposes and I'd like to adapt it in order to
help calculate per centages based upon square footage for real estate tax
purposes. I have a central table which contains the basic information for
each unit such as:

[unit]
[building]
[city_zip]
[tenant_name]

I want to add a field to store the square footage of each unit, but I am
unsure as to how I would go about using that information to calculate the per
centage owed by each tenant and store the information. I could easily do i in
Excel or something and do a copy-and-paste, or import, or data entry, or
whatever, but I'd like to keep it all done within the database to reduce the
opportunity for clerical errors.

So assuming I added fields like [square_foot] and [per_cent] to store the
square footage and per cent of the building (respectively), how would I go
about setting things up to do the calculations? I don't need super-specific
details, just some help figuring out how to do a computation across multiple
records and store the results.

 

[Clifford Bass]

Hi Silva,

Correction to one query:

qryBuildingTotalUnitSquareFootages

select BuildingID, Sum(UnitSquareFootage) As BuildingTotalUnitSquareFootage
from tblUnits
group by BuildingID;

Clifford Bass

 

[Clifford Bass]

Hi Silva,

Another correction:

tblRentals
BuildingID (primary key, along with next THREE fields)
UnitNumber
RenterID
StartDate
EndDate (null when the person is still renting)

Or:

tblRentals
RentalID (primary key)
BuildingID
UnitNumber
RenterID
StartDate
EndDate (null when the person is still renting)

Clifford Bass

 

[silva]

To clear things up, I am not using [tenant_name] as a primary key, as we do
have tenants with multiple spaces. The actual address is used as the key, as
the vast majority of the rental units are storefront commercial spaces, and a
small handful are suites in one building. [unit] is the actual primary key.
Also, considering that I have data going back to January of 2006, I am
certainly not interested in rebuilding everything in order to accomodate the
retention of data, so using queries to make the calculations in fine. But no
one addressed my concern about the fact that there is taxable square footage
in these buildings that is not rented space, and therefor would not be in the
calculations. How would I go about including those values in the queries?

Hi Silva,

If you are going to have historical data, you will need to use several
tables. Also, you should not use the name as the identification of the
renter (or is that owner?). What happens when you have two John Smiths? And
you will need to deal with unoccupied units. Suggested structure as a start:

tblBuildings
BuildingID (primary key)
BuildingName
BuildingSquareFootage (if wanted, total including public areas, and other
non-tenant-owned parts)

tblUnits
BuildingID (primary key, along with next field)
UnitNumber
UnitSquareFootage

tblRenters
RenterID (primary key)
RenterLastName
RenterFirstName

tblRentals
BuildingID (primary key, along with next two fields)
UnitNumber
RenterID
StartDate
EndDate (null when the person is still renting)

This of course does not deal with recording historical changes to the
buildings such as the addition of new units or the changing of the sizes of
existing units. I will not address those issues, which greatly complicate
things.

Following queries not tested. To calculate the current building total
rental square footage you might do something like:

qryBuildingTotalUnitSquareFootages

select BuildingID, UnitNumber, Sum(UnitSquareFootage) As
BuildingTotalUnitSquareFootage
from tblUnits
group by BuildingID, UnitNumber;

To calculate the total square footage for each building's renter
(renters could have multiple units, this should include the unrented units as
a single item):

qryBuildingRenterTotalSquareFootages

select tblUnits.BuildingID, tblRenters.RenterID, RenterLastName,
RenterFirstName, Sum(UnitSquareFootage) as RenterTotalSquareFootage
from (tblUnits left join tblRentals on tblRentals.BuildingID =
tblUnits.BuildingID and tblRentals.UnitNumber = tblUnits.UnitNumber and
tblRentals.EndDate is null) left join tblRenters on tblRenters.RenterID =
tblRentals.RenterID
group by tblUnits.BuildingID, tblRenters.RenterID, RenterLastName,
RenterFirstName;

Now put it together:

select A.BuildingID, RenterID, RenterLastName, RenterFirstName,
RenterTotalSquareFootage, Round(RenterTotalSquareFootage * 100 /
BuildingTotalUnitSquareFootage, 2) as RenterPercentOwned
from qryBuildingRenterTotalSquareFootages as A inner join
qryBuildingTotalUnitSquareFootages as B on B.BuildingID = A.BuildingID

Again, those queries not tested, but gives as a starting point. You
could also do something similar to get total ownership across all buildings
if you have tenants that rent in more than one building.

Good luck,

Clifford Bass

I built a database for rental purposes and I'd like to adapt it in order to
help calculate per centages based upon square footage for real estate tax
purposes. I have a central table which contains the basic information for
each unit such as:

[unit]
[building]
[city_zip]
[tenant_name]

I want to add a field to store the square footage of each unit, but I am
unsure as to how I would go about using that information to calculate the per
centage owed by each tenant and store the information. I could easily do i in
Excel or something and do a copy-and-paste, or import, or data entry, or
whatever, but I'd like to keep it all done within the database to reduce the
opportunity for clerical errors.

So assuming I added fields like [square_foot] and [per_cent] to store the
square footage and per cent of the building (respectively), how would I go
about setting things up to do the calculations? I don't need super-specific
details, just some help figuring out how to do a computation across multiple
records and store the results.

 

[Clifford Bass]

Hi Silva,

Thanks for clearing that up. Usually when one hears "units",
"buildings" and "tenants", one thinks along the lines of apartment rental
type situations. That you are using them in a different, less common
fashion, led to an erroneous understanding of your situation.

If you only have 3 1/2 years of data, that is exceedingly little. But
even you had twenty years of data, it is no reason not to make any
adjustments. If you are only storing the current configuration, what happens
two years from now when a former tenant comes back to you and says their
accountant needs data from January, 2009 in order to deal with some tax
issue, and you do not have the ability to pull the information that was
current at that time?

Be that as it may, and you can certainly ignore my suggestions, did
Ken's solution not give what you needed? If so, how was it inaccurate? It
may help for you to post the specific tables and columns involved if you are
having trouble adapting his query to your actual table(s) and columns.
Include how you uniquely identify your tenants. And maybe post some example
data and the expected results from that example data. That would help us get
a better grasp on your system.

Sincerely,

Clifford Bass

 

[silva]

You missed entirely what I was getting at. I have multiple tables with unique
keys. Of the two primary tables, one only has info on the tenant and the
unit, the other contains all the transaction data. There is no issue at all
with referential integrity or relationships.

You also missed what I was getting at with the square footage. The query
takes into account space that is listed in database, what is recorded in the
[square_footage] field. And yes, that does take into account space that is
not currently being rented. It does not take into account space in the
building that is not saved in a database record. How would I set up the query
to include the square footage not entered into any database records?

You are missing the point about the keys. We are not saying that tenant_name
is the primary key of Units (or whatever the table of units is called), but
the fact that you have the tenant_name as a column in that table implies that
it is a foreign key referencing the primary key of a Tenants table, whose
primary key is therefore a name. As explained names are not suitable as a
keys because they can legitimately be duplicated.

If you don't have a Tenants table then you should have, as without one you
have no means of enforcing referential integrity and the data in Units is
wide open to inconsistencies in the tenant_name column.

The correct model would be a Tenants table with a numeric tenantID primary
key and a non-key tenant_name column (along with columns representing any
other attributes of tenants). This is refenced by a numeric foreign key
tenantID column in Units in place of its existing tenant_name column. A
relationship is created between Tenants and Units on tenantID and referential
integrity enforced. Data entry for the tenantID column in units would be via
a combo box with tenantID as its value but showing the tenant names by hiding
the first column of the combo box's RowSource in the usual way; the combo box
wizard can set this up if you are unsure how to hand-craft it.

As regards the unrented units the query I posted does take account of these.
It will compute the percentage square footage rented by each client as a
ratio of the total square footage of the building. If the rows in Units for
unrented units have Nulls (or some consistent vale) in the tenant_name column
a row per building will be returned with the percentage square footage
unrented as a ratio of the total square footage of the building and a Null
(or the consistent value) tenant_name column. If it returns Null you can
convert this to return any string expression you want by use of the Nz
function.

If the model is corrected by the introduction of tenantID columns, which is
easily done with an append query to insert rows into Tenants, and an update
query to update the new tenantID column in Units, then to represent unrented
units you can include a row in Tenants with a tenantID value of zero, and, if
you wish, a value such as 'Not rented' in the tenant_name column. Any
unrented units would then have a value of zero in the tenantID column in the
Units table.

Ken Sheridan
Stafford, England

To clear things up, I am not using [tenant_name] as a primary key, as we do
have tenants with multiple spaces. The actual address is used as the key, as
the vast majority of the rental units are storefront commercial spaces, and a
small handful are suites in one building. [unit] is the actual primary key.
Also, considering that I have data going back to January of 2006, I am
certainly not interested in rebuilding everything in order to accomodate the
retention of data, so using queries to make the calculations in fine. But no
one addressed my concern about the fact that there is taxable square footage
in these buildings that is not rented space, and therefor would not be in the
calculations. How would I go about including those values in the queries?

Hi Silva,

[quoted text clipped - 94 lines]

details, just some help figuring out how to do a computation across multiple
records and store the results.

 

[silva]

The reason I only have data going back to 2006 is that's as far back as was
relevant when I created the database in late 2007/early 2008. All older data
is currently printed and filed. As it turns out it's a rare incident that I
even need to look back that far. When someone moves out the records are
printed out and filed. If they aren't a current tenant I don't keep the data
in the database for longer than is necessary. But this is not a discussion on
how I organize my data.

The problem I'm having mostly is the fact that I don't understand SQL very
well. Add to that I'm trying to understand how to incorporate the necessary
data that isn't saved in the database, as it has to relevance to the rest of
what's being stored in there. I just don't understand how to incorporate area
information for building sections that aren't rented out, so therefor will
not have a record in the database with which to store the square footage.

Hi Silva,

Thanks for clearing that up. Usually when one hears "units",
"buildings" and "tenants", one thinks along the lines of apartment rental
type situations. That you are using them in a different, less common
fashion, led to an erroneous understanding of your situation.

If you only have 3 1/2 years of data, that is exceedingly little. But
even you had twenty years of data, it is no reason not to make any
adjustments. If you are only storing the current configuration, what happens
two years from now when a former tenant comes back to you and says their
accountant needs data from January, 2009 in order to deal with some tax
issue, and you do not have the ability to pull the information that was
current at that time?

Be that as it may, and you can certainly ignore my suggestions, did
Ken's solution not give what you needed? If so, how was it inaccurate? It
may help for you to post the specific tables and columns involved if you are
having trouble adapting his query to your actual table(s) and columns.
Include how you uniquely identify your tenants. And maybe post some example
data and the expected results from that example data. That would help us get
a better grasp on your system.

Sincerely,

Clifford Bass

To clear things up, I am not using [tenant_name] as a primary key, as we do
have tenants with multiple spaces. The actual address is used as the key, as
the vast majority of the rental units are storefront commercial spaces, and a
small handful are suites in one building. [unit] is the actual primary key.
Also, considering that I have data going back to January of 2006, I am
certainly not interested in rebuilding everything in order to accomodate the
retention of data, so using queries to make the calculations in fine. But no
one addressed my concern about the fact that there is taxable square footage
in these buildings that is not rented space, and therefor would not be in the
calculations. How would I go about including those values in the queries?

 

[silva]

Oh yes, one other thing. Part of the reason that I don't have more than 3 1/2
years of data is that my predecessors kept horrible records of this stuff and
the data prior to 2006 is unreliable and incomplete, supposing it was even
kept. That was as far back as I could go and find complete data, and it still
has generated thousands of transaction records already.

Hi Silva,

Thanks for clearing that up. Usually when one hears "units",
"buildings" and "tenants", one thinks along the lines of apartment rental
type situations. That you are using them in a different, less common
fashion, led to an erroneous understanding of your situation.

If you only have 3 1/2 years of data, that is exceedingly little. But
even you had twenty years of data, it is no reason not to make any
adjustments. If you are only storing the current configuration, what happens
two years from now when a former tenant comes back to you and says their
accountant needs data from January, 2009 in order to deal with some tax
issue, and you do not have the ability to pull the information that was
current at that time?

Be that as it may, and you can certainly ignore my suggestions, did
Ken's solution not give what you needed? If so, how was it inaccurate? It
may help for you to post the specific tables and columns involved if you are
having trouble adapting his query to your actual table(s) and columns.
Include how you uniquely identify your tenants. And maybe post some example
data and the expected results from that example data. That would help us get
a better grasp on your system.

Sincerely,

Clifford Bass

To clear things up, I am not using [tenant_name] as a primary key, as we do
have tenants with multiple spaces. The actual address is used as the key, as
the vast majority of the rental units are storefront commercial spaces, and a
small handful are suites in one building. [unit] is the actual primary key.
Also, considering that I have data going back to January of 2006, I am
certainly not interested in rebuilding everything in order to accomodate the
retention of data, so using queries to make the calculations in fine. But no
one addressed my concern about the fact that there is taxable square footage
in these buildings that is not rented space, and therefor would not be in the
calculations. How would I go about including those values in the queries?

 

[Clifford Bass]

Hi Silva,

Understood about the information prior to 2006. My question related to
something that might happen in the future, say in 2012, relating to 2009
data. That you have it somewhere, even if on paper, is good.

For the building sections that are not rented out could you include
them anyway, say with either a "blank" tenant, or one or more dummy tenants,
say named something like "Vacant" and/or "Non-rental Space"? Often it is
useful to make your data such that all situations fit into your paradigm.
Having rows for the vacant and non-rental space does that because they just
become more items of the same type of thing. Then you can get the total
square footages of the buildings, of the rented spaces, of the vacant spaces
and of the non-rental spaces all at once. And then you should be able to get
the percentages you need to do you tax calculations. Alternatively, for the
total building squarefootage and the non-rental space, you could just store
them in a separate buildings table for each building.

Often I find it easiest to break the items down into multiple queries
when doing summing and percentages of totals. A simplified example:

qryBuildingSquarefootages

select BuildingID, Sum(Squarefootage) as BuildingSquareFootage
from tblUnits
group by BuildingID;

qryRenterSquareFootages

select BuildingID, RenterID, Sum(Squarefootage) as RenterSquareFootage
from tblRentals
group by BuildingID, RenterID;

qryRenterSummaries

select A.BuildingID, A.RenterID, A.RenterSquareFootage,
A.RenterSquareFootage * 100 / B.BuildingSquarefootage as RenterPercent
from qryRenterSquareFootages as A inner join qryBuildingSquarefootages as B
on B.BuildingID = A.BuildingID;

If you have tenants named "Vacant" and "Non-Rental Space", you can just
look at their rows to get the percent not being rented and the percent of
non-rental space.

To understand what is going on with the SQL, for each query do this:
Adjust the query to fit your tables and columns, open up a new query and
cancel the Add Table dialog. Change to SQL View and paste the modified SQL
into the window. Change back to Design view to see what it looks like in the
query designer.

Also, it may make sense to take a step back away from Access, pick up a
book on SQL and do some studying of SQL and its concepts. SQL for Dummies
may be a good place to start.

Hope that helps,

Clifford Bass