Windows 7 64bit 
Access 2010 64bit
Hello,
I am looking for advice on how to construct a database that has a large number of fields per entry. First I'll try to explain roughly what my database is and why it has so many fields.
My Database will be recording quotes received by manufacturers for a complex piece of machinery.
At first about ten fileds are taken up with basic information about the quote, i.e. what project it is for, the projects geographic region, predicted delivery date, quote currency etc.
Then about another ten entries are for specific physical characteristics of the machinery.
So about 20 so far, still small potatoes (ish).
Next however there is a list of options that may be included for the quotation price, and may not. For all of the options, for each quote (entry), it needs to be recorded whether or not this option is included in the quotation price and also what the price of this option is (sometimes the manufacturer will not have an option included in the quotation price but will say how much that option costs separately).
There's 17 options, so 34 entries there.
So now about 54 entries total.
Next there is an availability warranty that the manufacturer may supply for each year of the product. This is a percentage which represents the percentage of time the machine will run. An availability warranty is supplied for each year, up to 25 years after purchase. So that's another 25 entries.
That's about 79 entries so far.
Next is price indexing. Sometimes manufacturers will "index" their supplied price against the cost of a commodity, say steel. This means that the quotation price they give is time-varying, according to this indexing. Again this information is specific to each quote. For each indexing possibility, a description of the item being indexed is needed, along with what percentage of that price is indexed (usually not the whole cost) and a description of what its being indexed against.
That's three entries per price indexing, assuming at most 4 of these gives 12 total.
Lets round down a bit here and say roughly 90 fields per entry thus far.
Next comes the reallly field-adding bit, the Maintanence and Service agreements.
With a quote, a manufacturer may supply some optional service and maintanence agreements, say one lasting five years, one lasting ten years and one lasting fifteen years.
For each of these agreements there will be two prices given for each year of the agreement, a fixed price and a variable price. It is also necessary to record the total price type for each agreement (either sum of fixed and variable of highest of fixed or variable), the variable fee maximum and at what point the variable fee starts applying (a number of hours of machine use).
So for each Maintanence and Service agreement, the total number of entries is (2*x)+3 where x is the number of years.
So for the five year agreement thats 13, for the ten year that's 23, for the fifteen year 33.
So for these three agreements that's 69, or say 70.
This gives roughly 160 fields. Additionally, in the future it may be desirable to add 20 and 25 year Maintanence and Service agreements.
Apologies for the bumper decription. The main point I need to get across is that I don't have a lot of one-to-many relationships. I have many fields per entry. Some of these might be empty for some entries, (e.g. a 15 year Maintanence and Service agreement may not be given for a particular quote) but these values need to exist for each quote (i.e. can be set to blank or similar where empty).
Additionally whatever the final set up of my database in Access, it must be possible to dump this to excel in a single columnated dataset. My current plan is to write everything in Access in the way that is best for entering the quotes, then worry about the dump to excel by writing a query (if I've used many tables).
Currently I'm trying to solve this problem by having many tables with one-to-one relationships. This isn't quite working though and I keep getting the feeling that there's a better way to write this.
Ok, now that I've explained the premise;
*Takes deep breath*
My actual question is this,
Does anyone know of a good template for a database with hundreds of fields per entry? Or does anyone have some general guidance on how to set up such a database?
I am more than happy to set things up in different way but it is a fundamental issue of the problem that there are many fields per entry, this is not just how I've set up the database (there are no one-to-many relationships).
Apologies if this thread is not clear, I'm happy to edit if so.
Many thanks in advance for any advice,
vbastrangledpython.
Reply With Quote
