I am making a database for someone who owns real estate. I am not an expert. He wants each apartment building's rental income added up for twelve tables. For instance, there is one table for each month of the year for each building. He wants the total yearly rental income for the building. He also wants his expenses added up pretty much the same way. We need to add up all of the fields (for each table), then those totals need to be added up from each of the twelve tables for the grand total. Thanks in advance for help.
ummm... sorry. What you need is a complete redesign! This is a good
*spreadsheet* approach, but Access is not a spreadsheet.
Dates are DATA. Storing data (such as a month) in a Tablename is just
going to make your job MUCH more difficult, as you are finding.
Instead, you should identify each "Entity" - real-life category of
things, people or events; each entity should have its own table (just
one table for all instances of that entity). For example:
Buildings
BuildingID Autonumber
Address
City
State
Zip
<other info about the building itself>
Apartments
BuildingID Long Integer <link to Buildings>
ApartmentNo
<maybe some other info, e.g. size>
Tenants
TenantID
LastName
FirstName
<other bio data>
Rentals
BuildingID
ApartmentNo
TenantID
StartDate
Term
Rent
Payments
BuildingID
ApartmentNo
PaymentDate
PaymentAmount
The Payments table would have ALL the payments, for all buildings, for
the entire term of interest for the database. You could create a
Totals query to sum up the payments, grouping by building, or by
Year([paymentdate]), or by month, whatever is needed.
