Access Quotation/Estimate DataBase
I work in the construction industry as a specialist sub-contractor. When I provide a client with a quotation I am compiling a list of parts and materials from merchants and suppliers and from some miscellaneous items that I might create and provide a costing to in my own small database table.
The merchants price files are available in generic formats such as CSV files for easy importation into programs such as MS Excel and MS Access.
Typically, these files are set up with several fields including a unique item number, a description, a cost price and a RRP (recommended retail price). Often the price files are sorted into major categories and sub-categories. Although not usually sorted by size many products/parts contain size information in the description fields.
The important thing to note is that the prices will fluctuate up and down and the merchants provide updated price files on a regular basis throughout the year usually on a monthly cycle. The finished Database must have the ability to update from these files and not affect the structure of the queries and tables developed below. The small tables of miscellaneous items that I create could be updated by a simple calculated field in most cases or by manual editing when needed.
I want to develop a quotation and estimate database that can streamline the selection of parts and materials from a merchants Price File and place them in a quotation form for final perusal and checking before creating the quotation/estimate for a client.
Suppliers/merchants price files contain multiple thousands of items and often you want a macro view of an entire category of items just to peruse it and get to know what the supplier has and other times you want a more granular view by looking just at specific selections.
The key things are the ability to quickly find the parts required from the merchants/suppliers inhouse price lists and add them to the quotation listing.
This would be done by:
1. Custom searches including Wildcard searches for example.
2. Custom sorts using criteria such as size and brand from the description field of an item.
3. Selection of merchant’s inbuilt fields and categories.
4. Creation of subsets or groups of parts that are relevant to a particular type of quotation.
1 to 3 above are available already in Access and it would be more a matter of providing quick one-click access to some prebuilt searches etc. via a command button. An example of that might be: “All widgets that have [1/2’’], [12mm] or  in the ‘Description’ field”.
4 above is the real power option which I am aiming towards.
As I compile a listing there will be certain selections of parts that will always go together in the actual fabrication or installation of the work. These would be built organically over time using the Database and saved for future re-use. Some of these selections would form part of bigger selections and some of those bigger selections would form more complex selections. Some selections would nearly always go together like bread and butter. A more complex selection might be bread and butter and jam on a plate. The complex one might be called a jam sandwich. Keeping the food analogy - on a particular installation I might use a peanut butter and jelly sandwich, a pot of tea and a basket of citrus fruit. I might also need an individual orange, a loaf of sliced bread and one tea bag on that same project. Also I want it itemized in the same way in the quote so that I can visualise the installation as I am preparing the quotation and lessen the chance of missing items needed for the completion of the work.
See complete text in attached file...
After some initial comments I just want to clarify that the most important feature of the database is the ability to pick parts from a price file that has been supplied by a third party and imported into the database in an intuitive and customised way.
The goal is to streamline and speed up the selection process as much as possible so the time spent on creating the quote is minimised. This can be achieved by re-usable groups of selections.
As I use the database I would save some selections with a name to create a Sub-set of parts. In a future quote I would be able to select these groups to build the quotation. The resultant pick list of parts could include some group selections and some individually picked items.
The screen layouts and maximising of line items displayed within available screen real estate will facilitate this.
The end user must have the ability to create and modify these groups of parts and sort and order them as needed.
Again it is the selection speed that is critical. Getting the right parts in front of the mouse cursor so they can be added quickly.