Setting up a new Access Database for Property Management

[krzyjj]

Hello,

I am currently setting up a new Access Database for use in analyzing the financials of 14 different apartment complexes. I currently have this data in an excel spreadsheet but have decided that it's time to move everything over to an Access Database.

I have done some extensive research on this forum for similar ideas on how to set up the database, but i just cant come to a conclusion on how to divide up my individual tables, so i was looking for a little advice.

Let me explain what i use the data for and how i have it set up currently:

I currently have a front end dashboard in excel which pulls the property information from the database. I update the property information in the database once a week and create new records based on that date. This way we can see historic trends and how the property has performed over time. Right now, all the information is in one giant table as follows:

14 Different properties (In Excel this ranged from 1 to 14 for week 1, then 15 - 29 for week 2, etc.)
Each property has 18 Tracked Categories (In Excel this ranged from G to V).

I know this is wrong because this creates a ton of redundant information, like the date listed 14 times for each week. I also have the properties categorized by Region as well as then state, which also create redundant information.

Please see below for an example of several weeks worth of data:

https://docs.google.com/file/d/0BxGHVn-7K_UgUFB3UlRoUWY5Vzg/edit

My thought is to divide it up in tables from the top level down. Start with Region which has the various states, properties, dates listed. Then go to State, which has the properties and dates. Then Dates, which just has properties. Then finally Properties which has all the weekly attributes listed? I'm not sure if this is right and any advice is most appreciated. Thank you very much for your help and please let me know if you have any questions.

 

[SydneyGeek]

You need to be careful about how you store the data. As you have noted, data duplication should be avoided. Look at references on Normalization -- there are plenty on the Web, but a couple of pointers might help:

One table, one topic.

A primary key for each table. This uniquely identifies each row in the table. A PK can be Autonumber (my default -- no need to enter a new key for each record), Number or Text (both of which require you to enter a value).
For lookup tables like Regions or States you can make the Region or State field a Text PK. Regions only needs one field. States should have State (PK, Text) and Region. The 2 tables should be related by joining on the Region field, with referential integrity enforced.
Other lookup tables would be:
1. A large date table -- I usually have one called AllTime. Build it in Excel, and import it; fields could include atDate, atWeek, atMonth, atQuarter, atYear, atFinYear (note the at prefix -- indicates that the field comes from the AllTime table, and avoids using Day, Week, Month etc as field names -- something to be avoided, because they are reserved words [part of the language]). Take the dates out for the next 20 years or so. It's not that much data, but it makes a huge difference when you want to group and analyse later on.
2. Categories -- single field, Category (PK, Text). See below for more comment.

The fields in each table should be the same topic as the primary key (eg, Regions should not hold any of the Properties fields -- different topics).
Scan your spreadsheet for duplicated columns -- in your case, the different Categories. That indicates that you need a Categories table.
Look for duplicated rows -- the Week in your spreadsheet. A date lookup table (see above) is useful.

You need a Properties table with fields like
PropertyID (Autonumber, PK)
PropertyName
Address1
Address2
City
State (this will look up the State table but DO NOT use a Lookup field -- this should be done using a combo box (drop down list) in a form.
Zip

PropertyDetails table
ProperyDetailID (Autonumber, PK)
PropertyID (Number -- foreign key, related to Properties table)
WeekCommencing (Date -- related to AllTime table)
Category (Text -- related to Categories table)
Amount field (requires some thought. Are all categories text, all number, or a mix?) You may need 2 fields to handle the different data types).

There's likely to be more but that should get you started.

Denis

 

[krzyjj]

Hi Denis,

Thank you very much for your quick and detailed reply. I have referenced quite a bit of material regarding normalization and i understand the concept behind the process, i was just having some difficulty relating it back to my own data. What you have provided really helps to clarify how i need to break up the tables. Thank you very much for your help on this. I'll attempt to analyze my data further and apply your suggested tables.