Page 1 of 6 123456 LastLast
Results 1 to 15 of 78
  1. #1
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52

    Call Log DB

    Hi - I am a dreaded excel user- but now feel the challenge of Access on me.



    I want to develop a call logging DB and contacts.

    Purpose
    To log communication - this will be phone.email.meeting > each to have a comment>each to have a follow up date>each to have a selection of product groups discussed.
    I would like to create a specific project if required

    I am to run a report showing me;

    dates that i have set to call/contact/follow up
    dates i have set for follow ups to quotes/calls/meetings

    also would it be possible to start a project ( I assume this would be along the lines of new project table)

    Key Id would be customer id and name
    project would have unique id id create eg easter1

    I was going to start using the microsoft customer

    as i want to learn access are there any decent learning repositiories - ebooks etc apart from this forum!

    thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    This would be a great learning project.
    i had one similar,

    tCallLog table
    CallID (autoNum)
    phone#
    CallerID
    EmpId. (Who answered the call)
    CallDate
    Subject
    Notes

  3. #3
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    Then link it to 2 seperate tables

    tb1 comments ( id by customer id and name - have date colom,comments,action required type and follow up date

    or would you have master table of just
    cust id
    cust name
    contact name

    and link this to a seperate table containing
    position
    office tel
    mobile
    email
    office address
    personal notes

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Personally I would have a Customer table, and a CustomerContact table. This allows multiple contacts per customer.
    So move your Contact name into the second table, and add the CompanyID as a the foreign key to tie it back to the Customer table.

    Then for your CallLog table on your Call Logging form you can select the company, and then have a cascaded drop down for any contacts that work for that company.

    CallID
    CustomerID
    ContactID
    CallDate etc etc.
    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 ↓↓

  5. #5
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    Great Ill have a go at building that and see how i get on - a bit rusty dong the linking etc and setting up cascades but thats the fun of it isnt it!

    wont get to it today ill go old school and get the pad out!

  6. #6
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    so
    Customertable

    *CustomerID
    Customer name
    Customer address
    Customer comments

    Customer contact table
    *ContactID
    Name
    Role
    mobile
    phone
    email
    area of responsibilty
    comments

    Call log table

    *CallID
    *CustomerID
    *contactID
    call date
    call type(email/call/appt)
    comments
    action required
    type action required (email/call/appt)
    action required by date
    follow up date

    Project table
    *ProjectID
    *CustomrID
    *ContactID
    Date
    Project description
    Objective
    Required by


    make any sense?

  7. #7
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Yes - remember to add the CustomerID_FK to your contacts table, otherwise you won't know who they work for!

    I would use ContactName as the field name - Name is a reserved word in access.
    Also avoid spaces or any special characters in your field names, it makes typing and debugging much easier.
    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 ↓↓

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would advise creating tables/relationships using pencil/paper, whiteboard, window,etc BEFORE jumping into physical access.


    Consider:

    Code:
    tblCustomers
    ----------------------
    *CustomerID_PK  (Autonumber)
    CustomerName
    CustomerAddress
    CustomerComments
    
    
    tblCustomerContacts
    -------------------
    *ContactID_PK   (Autonumber)
    ContactName
    Role
    mobile
    phone
    email
    AreaOfResponsibilty
    Comments
    
    
    tblCallLog
    ----------------------
    *CallID_PK    (Autonumber)
    CustomerID_FK    (Number - Long Integer)
    ContactID_FK   (Number - Long Integer)
    CallDate
    CallType          (email/call/appt)
    Comments
    ActionRequired
    TypeActionRequired     (email/call/appt)
    ActionRequiredByDate
    FollowUpDate
    
    
    tblProjects
    --------------------
    *ProjectID_PK    (Autonumber)
    CustomrID_FK    (Number - Long Integer)
    ContactID_FK    (Number - Long Integer)
    ProjectDate
    ProjectDescription
    Objective
    RequiredBy

    Note: "Date" is a reserved word and a built in function in Access and shouldn't be used for object names

  9. #9
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    Ok so ive built the tables and established a couple of relationships.

    Click image for larger version. 

Name:	relatioships ver1.PNG 
Views:	59 
Size:	37.8 KB 
ID:	38338
    i will add the project table when i have this bit right

    i assume i will add customer contacts via a form.
    i will add customer logs via a form
    i will run a query/report to find out who needs contacting by date?

    sound about right??

  10. #10
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I would have assumed that a contact worked for a company ?
    Therefore you probably should have the Company Id as a FK in the contact table ?

    Otherwise when you go to select a contact you'll have to scroll through every one, rather than being able to filter it by the customerID ?
    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 ↓↓

  11. #11
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    or should i add customer id to the contact sheet as well
    Click image for larger version. 

Name:	relatioships ver2.jpg 
Views:	58 
Size:	70.3 KB 
ID:	38339

  12. #12
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    like table 2

  13. #13
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Generally Speaking you should avoid circular relationships in your design.
    In this instance whilst it's technically correct, the better way to represent it is to add the Contacts table again and join that to the call log.

    I would also remove the NEW suffix on your table names, keep table names short descriptive and to the point, saves a load of extra typing.
    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 ↓↓

  14. #14
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    sorry could yopu draw lines on my image slightly confused

  15. #15
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Simply add the contact table to the relation window again - like this; But with prettier lines.
    Click image for larger version. 

Name:	ReallyBad_Edit.jpg 
Views:	58 
Size:	75.8 KB 
ID:	38340

    ps. My middle name is Picasso
    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 ↓↓

Page 1 of 6 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-19-2017, 11:07 PM
  2. Need help not sure what to call it :-(
    By jscalem in forum Database Design
    Replies: 8
    Last Post: 08-30-2017, 12:45 PM
  3. What do you call this?
    By mrmims in forum Access
    Replies: 4
    Last Post: 11-11-2015, 12:43 PM
  4. Call and phone
    By drunkenneo in forum Programming
    Replies: 0
    Last Post: 08-28-2013, 12:49 AM
  5. Not even sure what to call this one
    By eizquierdo in forum Queries
    Replies: 2
    Last Post: 03-14-2013, 12:08 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