Tracking SF in Access

[ExcelGirl1975]

I am creating a database that will track building square feet and any changes to that SF (e.g. vacate a floor equal to 5,000 square feet in 2011). I am still in the deisgn phase of this project and I am thinking that I will have a "plan" table where there will be:

Plan ID: primary key
Building ID: Foreign key
Plan Action: Partial Exit
Plan SF: -5,000 SF
Plan Date: March 2 2011

Does this seem reasonable?

More importantly - Will I be able to create a query that will summarize all of the changes to a building, say over 5 years in terms of Square Feet. So, for instance, if a building has 50,000 square feet total and the client vacates 1 floor this year, will I be able to create a query that shows current SF in January 2012? (Current SF in 2012: 50,000 - 5,000 = 45,000 SF)

Or is it best to do the analysis in excel (based on an extract of data from Access)

 

[ExcelGirl1975]

Just to clarify, in the sample table of my question above, I included sample fields and sample inputs. I hope it's not confusing.

I'm just looking to track the baseline/starting SF in a building and changes to that building by year.

I will want to report on the building's current square feet inclusive of changes to the building's Square feet over time.

Thank you all!

 

[xenou]

I'd be tempted to do it all in Excel ... there's not really a lot of data to be stored (I presume, with tenants moving in or out relatively infrequently) and if you kept it in Excel there could be a running total right on the worksheet.

 

[ExcelGirl1975]

It's currently being tracked in Excel..Problem is, there is the desire to maintain history and an audit trail since these records will be updated annually.

In additional to the SF(and changes to SF) being tracked, financial (cost to implement changes), headcount impact, seat impact and impact by business unit.

The current excel file is huge and there are multiple versions being floated around. So, there seems to be a need for a database to store the historical data.

-------------------------------------------------------------------

Oh, and the the client wishes to create a dashboard of the SF changes (I intend to link the access db to excel for analysis and dashboard display).

 

[xenou]

How many records are you storing each year?
ξ

 

[ExcelGirl1975]

There will be about 60 buildings. In addition to tracking SF, I will be tracking financial, headcount and strategic information for each building (So many tables and records I'm thinking)

 

[xenou]

I'm in over my head on this one. You probably need to finish your design plans with a complete database design on paper. Once you get things on paper you'll find it's easier to see whether it will work or not (the disciple of designing on paper forces you to think more clearly, and gives you time to ask questions). It's of course no problem adding up the changes by date, in and of itself.

ξ