Database Normalization thoughts

[jphome]

I was wondering if someone could give me some pointers or provide some guidance on how to normalize an existing database.



I've set up an oil lease production database that tracks monthly/yearly values.

Essentially I have a main table with the following fields:

oilleaseID
oilleaseName
and then I have fields for every single month (i.e. Jan 2013, Feb 2013, Mar 2013) for Oil Production.

Then I've created additional tables (since I maxed out on fields in a table) for additional value types. I have additional tables for Oil Sales, Gas Production, Gas sales, Water Production, Days Produced and more) that all have fields like the main table.

I've gotten it to work fairly decently by running an Update query to add/delete/modify to update each table whenever a new oil lease is added or changed. Basically I have the database run the Update Query once a particular form is closed after editing.

How would I set up a database that is normalized to not have to run the Update Query. I'm afraid over time when I keep adding tables it will take the Query longer and longer to run, especially if there are a ton of records.

I'm also running into a problem when I go to run reports that can include all the data from all tables from all years (since I have more fields than the 255 limit). I'd love to be able to set up a report that a user could just specify date ranges too instead of a static/default only range.


Thanks for the help!

[June7]

Maxing out the field limit is a strong indication of non-normalized structure. Commend you for recognizing that and wanting to fix.

Consider:

tblLeases
LeaseID
LeaseType (Oil, Gas, Water)
etc.

tblProduction
LeaseID
ProdDate
Quantity
Amount
DaysProd

Maybe this will help http://www.rogersaccesslibrary.com/

[jphome]

Thanks for the advice. I'll play around with that and see if that can work out!