Setting myself up for success

[stalker401]

A little background on my project i'm working on and hoping to get some help on setting it up so I won't have issues down the road. I work for an insurance agency where I get submissions from salesman and make submissions to carriers to best fit the needs of quotes so the salesperson can sell the quote. My goal for this is to create a submissions spreadsheet that will track what I've received and update the progress as I go through. I want to, down the road, be able to create forms where I can either filter and/or search through my submissions that have been completed and/or not completed. I'm also wanting to create printable reports as well possibly, so I can make sure I'm not letting things fall through the cracks.

So to start my spreadsheet I'm thinking needs the following columns:
ID
Insureds Name
ProducersName
DateReceived (can be Date Format)
NeedByDate (Needs to be Text Format as I'll use ASAP)
Effective Date (Needs to be Text Format as I'll use ASAP)
Notes
Target Premium (Can be Currancy Format)
CoveragesNeeded (see Below)
CarriersSetTo (See Below)
Status (See Below)
Attachements

Regarding CoverageNeeded; CarriersSentTo; and Status - I was going to use a drop down list from another table in access, however I may just use a text field. Any feelings on which is the best? or are there any issues a drop down list can cause in creating reports/forms that I don't know about?

The biggest issue I'm seeing with this is, a lot of the times we'll quote General Liability and property with one carrier because their price and coverage is better, and the WC with someone else. Other than in the notes, I have no real way of tracking which lines were submitted to which carriers. Like I said I can make notes in that column, but I might get very convoluted down the road.

Are there any fields I may need to add or remove to reach the goals of what I'm trying to accomplish with the future search/filter feature on forms I intend to create. or is there anything I need to do while creating the table that needs to change? I really appreciate your help. I just know if you don't start with a good base, you won't be able to do all the things later down the road.

Currently I'm going to be keeping all of this in an excel sheet until I'm able to fully grasp how I need to create this table.

 

[gemma-the-husky]

Are we talking spreadsheets or database tables?

 

[plog]

Currently I'm going to be keeping all of this in an excel sheet until I'm able to fully grasp how I need to create this table.

Excel to Access isn't that simple. Excel is just a flat sheet of data, Access is a database which requires normalization (https://en.wikipedia.org/wiki/Database_normalization). That means multiple tables, storing values properly, etc.

Database development goes like this---Tables, Reports/Queries, Forms. There's no need to worry about drop downs and subforms and what font to use on which form--that's all the last step of a database. For now you need to focus on setting up your tables properly. Check the link above about normalization.

I didn't really follow what all your foot notes, but I do see that you are going to need at least 2 tables. The CarriersSentTo becomes a new table because many carriers can align to one record in what is your main table. You don't just jam a bunch of data into one field--instead you add multiple records to accomodate more data. Again, read about normalization.