We help IT Professionals succeed at work.

Microsoft Access Doubts for creating a new database

infiniti7181 asked
Hi Guru's ,
I would like to make pre-sales forecast sheet in access. As I am new in this, I am excited to do work from scratch .

Objective : To create a database, entering the data for contents such as oppurtunities (eg: customer name , customer type , region , vendor details etc , date of receive of RFP , date of submission , status - won , lost , inprogress etc) .

I have couple of questions related with this .
1. What is the best practice to add the contents for each section , in other words , is it recommended to add separate tables for each section and link them together . Eg: Vendor will be a different table , Status - Won , Lost etc will be different table or so on .
2. If I need to design as a form , how will I add a content that says " click for new opportunity " and when I click it a new form opens .
3. Lets says , I have added four vendor names in the database as raw (eg: Cisco , Aruba , HP , Juniper etc ) . Now I go to form and select as pull down menu of these vendors and see these four. If I need to add the fifth vendor, is there a possibility that I can add " Add New" and a new form appears to add vendor name

Appreciate your kind help and support.


Watch Question

1. Yes, normally repeating values should be in separate tables.
2,3. You can start from demo database Northwind. In Access 2010 you can find it from templates window:
templatesThere are answers to the most of your questions.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Status - Won , Lost etc will be different table
I agree that distinct data Entities (like Vendor, Customer, etc) should have their own table, but "Status" would not be one of those (if that's what you're alluding to).

I would advise you to work through one of the templates suggested by als. While they're not perfect, they will show you how data relates together, and how data works with forms, queries, reports etc in the Access world.

I would also advise you to get your data structure right before embarking on adding UI items like Forms and Reports. A properly built database structure will be much, much easier to use when building your Forms and Reports.
MIS Liason
Most Valuable Expert 2012
Also note that your brief outline does not give us enough info to give you any detailed advice.
So, as you look through the database samples you will notice that even a "simple" database will end up being fairly complex, especially to a beginner.
(For example a simple Contacts database can contain up to six or more tables.)
So you may wish to start with a simple database first.

Many times a project will evolve into something a lot more complex than you had anticipated.
If your experience in database design is limited, ..this may result in costly redesigns.

So you may also find it useful to research "Database design" as well, as this will not only help you with Access, ...but other db platforms as well.
For example, you need to understand the different types of Joins/relationships.
Also a firm grasp of Many-to-many relationships is also needed.

To that add an investigation of the standard naming conventions:

Finally, Know the full scope of your project before you start the design.

That's it for me...
Lets see what other Experts may post...