Results 1 to 7 of 7
  1. #1
    Traceyq is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2018
    Posts
    2

    Smile Help - New to Access, Initial Design Query on best way to add multiple people in the same company

    I have been asked to create an Access Database for Customer Relationship Management. We currently have an Excel spreadsheet with 2000 contacts on, but this is not fit for purpose as we need to be able to see and manage all communications with customers, and current they are in a comment box.



    I am totally new to Access and have been training from You Tube, so this is all new and quite daunting.

    I require a database that stores, Company name, contact (there may be multiple of these for each company, each needing their own memo/communication log), industry, address, contact numbers, email and a memo contact log.

    What is the best way to have all contacts from the same company to show together?
    Also is there a way to get the database to send an alert?, eg for when to contact someone

    Thanks in advance.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This is a very broad question.

    You may find it helpful to look at the northwind sample database that ships with most versions of access, as that has some good basic design ideas.

    You will want a number of tables - again google for examples before diving in. The absolute basics will be tables for Customers, CustContacts, ContactLog.
    But you could get a lot more esoteric and detailed with the whole thing.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    tCompany table:
    CoID (auto)
    CoName
    addr
    etc...

    and tContacts table w fields:
    ContactID (autonum)
    CoID (long)
    Name
    Position
    etc

    this allows multiple contacts per company

    tEmails:
    EmailID (auto)
    ContactID (long)
    Email
    EmailType (work, home)

    tPhones:
    PhoneID (auto)
    ContactID
    PHone
    phoneType (cell, work,home)

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need a separate table for each entity type - customers, contacts, memos, industry etc

    Addresses - depends on the customer, if they can have multiple addresses then they need tier own table as well. Same for contact numbers etc if a contact can have multiple phone number types e.g. office, mobile then they can go in the contacts table, but if they have multiple office numbers, they go in a separate table.

    each table should have a primary key and where a child table is linked to a parent (e.g. contact is a child of company) you also need a 'family' or 'foreign' key in the child table which maps to the parent primary key as a relationship

    What is the best way to have all contacts from the same company to show together?
    you should be asking this question once you have defined all your tables and relationships. but in principle you would have a main form (company) with a subform (contacts)

    Also is there a way to get the database to send an alert?, eg for when to contact someone
    yes - to early to say how this is achieved (other than you will use a query and vba) but perfectly possible.

    don't think of access as being a large excel, it is nothing like. Also remember that tables are for data storage, nothing else, do not get into the habit of looking at your tables as a 'finished' view. Use forms/reports via queries.

    you may well find there is a contact management template available in Access which will perhaps give you some ideas as to how things might work, but I would avoid trying to modify them to meet your needs. You will end up spending more time trying to 'fix' it whilst not really understanding what you are doing or why. Better to build from scratch and use the templates to see the principles applied

  5. #5
    Traceyq is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2018
    Posts
    2
    Thanks, I really am like a fish out of water. New job, with this request and never used Access before. Its becoming clearer.
    Quote Originally Posted by Ajax View Post
    you need a separate table for each entity type - customers, contacts, memos, industry etc

    Addresses - depends on the customer, if they can have multiple addresses then they need tier own table as well. Same for contact numbers etc if a contact can have multiple phone number types e.g. office, mobile then they can go in the contacts table, but if they have multiple office numbers, they go in a separate table.

    each table should have a primary key and where a child table is linked to a parent (e.g. contact is a child of company) you also need a 'family' or 'foreign' key in the child table which maps to the parent primary key as a relationship

    you should be asking this question once you have defined all your tables and relationships. but in principle you would have a main form (company) with a subform (contacts)

    yes - to early to say how this is achieved (other than you will use a query and vba) but perfectly possible.

    don't think of access as being a large excel, it is nothing like. Also remember that tables are for data storage, nothing else, do not get into the habit of looking at your tables as a 'finished' view. Use forms/reports via queries.

    you may well find there is a contact management template available in Access which will perhaps give you some ideas as to how things might work, but I would avoid trying to modify them to meet your needs. You will end up spending more time trying to 'fix' it whilst not really understanding what you are doing or why. Better to build from scratch and use the templates to see the principles applied

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree with Ajax about designing your own but there are indeed various contacts templates available with Access 2010 that may be worth looking at for ideas.
    However, I would recommend not using split forms or navigation forms.
    Whilst at first they will work well, if you need to modify them later you may find them difficult to adapt
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with most of the advice given. However, I think it is a little early to ask "initial query design...". If you create a list of the things that your CRM must do (outputs), you will experience some of the details you will need to investigate. Bottom line here is --what exactly do you/your company need as a Customer Relationship Management application? Will you need access to info from internet?

    Templates may be a good start, but not all organizations have same needs --you may have to adapt any template to your requirements.

    You say create an Access Database for Customer Relationship Management which could include gathering and analyzing requirements; review of possible/affordable "off the shelf CRM package"; design/test a prototype for review/confirmation...

    Here is a link to some Database Planning and Design articles.
    Interesting project. Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 12-12-2017, 06:41 PM
  2. Initial Design Help
    By Mattyn in forum Database Design
    Replies: 3
    Last Post: 07-05-2016, 04:10 PM
  3. Replies: 1
    Last Post: 08-06-2014, 02:22 PM
  4. Initial Design and Querying
    By TheBrigg in forum Access
    Replies: 2
    Last Post: 12-06-2011, 08:12 AM
  5. Need help on initial design
    By allykid in forum Database Design
    Replies: 2
    Last Post: 11-09-2010, 01:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums