Microsoft Access- Create a Data Bank - 1st Part

concept address database general architecture: • enterprise windows domain environment • local database in Access 2010 - 32bit or 64bit version English • tables on a central SharePoint 2010 server • user the current NT-user as database user - simple table in the database wether a user is allowed to use it or not • use of the hungarian notation • clear documentation in the code and tables / so that a 3rd person can follow the procedures and ideas • track record who added, changed or deleted a record • track record of all changes (old value, new value) User Procedures Add, change, delete companies Add company: 1) check wether already existing (along name, postal code, street / give a message, if a similar entry exists 2) add the record 3) check mandatory entries 4) start procedure to add 1) sales person 2) categories 3) flags 4) contacts Add, change, delete contacts in companies New category or flag and assign category or flag to companies 1) define category or flag 2) filter per company cats and flags 3) add the new campaign flag to the filtered and selected company group 4) filter companies that HAVE the new cat or flag 5) filter companies that DONT HAVE the new cat or flag 6) always give the option to select and unselect contacts and add or remove the new cat or flag New category or flag and assign category or flag to contacts 1) define category or flag 2) filter per company cats and flags 3) add the new campaign flag to the filtered and selected contact group 4) filter per contact cats and flags 5) add the new campaign flag to the filtered and selected contact group 6) search for company name 7) filter companies that HAVE the new campaign flag 8) filter companies that DONT HAVE the new flag 9) always give the option to select and unselect contacts and add or remove the new flag Export AND flag This procedure 1) select a list of companies or contacts 2) export this list to EXCEL 3) flag those companies or contacts wit a NEW flag (with a customisable name) in general Log all changes in all records 1) date 2) name of user 3) field name 4) old value 5) new value Tables: The central point of the database is the company table. Every information stored in the database connects somehow to a company. The needed database fields are like this PLUS “Country” Category (industrial, commercial etc (1toMany)) Flag (doNotContact, inEndCustomerPromotionList etc 1toMany)) Contacts in Companies (1toMany) Category (DecisionMaker, Technical, Operator) (1toMany) Flag (Campaigns,...) (1toMany) Connected to this company we need several 1toMany connections Equipment (1toMany) We need a flag list per company – with many flags per company. Those flags are supposed to be in groups. We need connections to Sales people – 1toMany. With one MAIN contact per company! As important as the companies are the contacts in the company. Of course many in one company! This contacts needs FLAGs as well. eG.: “Main contact” “decision maker” “do not contact” and so on. Those should be in groups as well (whats missing in the table ex. below – please add) The usability is crucial. I would prefer interface similar to this concept: We can search in filter with some free text and some selectors (e.g. for the flags) – and this in various combinations. Adding and removing from flags should be in dialogues like this: Language will be English – those examples are partly in German – sorry ? Project is divided into 4 parts: 1- Implementing all Company related parts (Deadline: 02.01.2013) 2- Implementing all Contacts related parts (Deadline: 11.01.2013) 3- Implementing all Sales Person related parts (Deadline: 25.01.2013) 4- Implementing other parts (like flags, category, export and etc...) (Deadline: 08.02.2013) This bid only relates to the 1st part of the project!