Multiple Error Proof User Friendly Forms based on 1 large Table
I am working for a non profit that is trying to create a database with information from the last 50 years and be ready to input new info as we move forward.The basic set up is this:
All work is based on a property >-----< investment (witha join table in between).
Some of them have sub categories, like Restoration -Full or -Partial, but all would have basic info such as Amount, Year, ProjectMgr. Then there are a couple that have some unique information that would add extra fields which would only be filled for those types of investments. Also, different people are responsible for entering information for each type AND queries need to be based on each type individually or comparing all 3.
I would like all investments to be in one table so that "total amount" could be tallied or sorting projects by year would be simple. My questions is when it comes to the forms, how can I make them user-friendly? Would data entry always have to start with the long list of all possible investment type combinations? or is there a way to create a script that would simplify the data.
For example, I add a new property, right below I have a choice of buttons for type of investment to add. so i click the "restoration" button, it opens a form with restoration already chosen as Type and the next selection is a drop down box that is now limited to "full" or "partial" and then all questions are related only to that type?
Is this even possible? If so, how or is there a better way. I am relatively new to FMP and am slightly confused by the "tables are forms are tables" concept... But even in Microsoft Access, the intial relationship is an issue b/c I don't want a combo box with a choice of 10+ investment cmbinations... am trying to reduce input error.
I hope this all makes sense and someone can help... Thanks!