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

  2. #17
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    Click image for larger version. 

Name:	relatioships ver3.PNG 
Views:	26 
Size:	66.9 KB 
ID:	38344
    hi heres the revised version - if i populate it - any suggestions i assume a form is the easiest way.

    then i can look at call logs and figure out searching etc

  3. #18
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    gthe two contact Ids would need to be linked in some way though so the integrity stays ok e

  4. #19
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    PK is the Primary key field . This is a unique identifier for that record. Most frequently they are an autonumber type field.
    FK is the Foreign Key field. This is where you store the PK of the related record from the parent table.

    So in your example above the CustomerID is Unique in the customer table, but is saved in the call logging table as a foreign key value to identify the customer by simply storing that unique ID every time the customer calls.
    Ditto the contact ID.

    Like I demonstrated you can duplicate the tables in the relationship window to show where multiple relationships exist.
    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. #20
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    cheers Minty- so being an absolute newbie with abasic understanding - how does the relationship get establised if i dont make them a relationship.

    in my new layout ive picked up the key fields.

    I removed the black line do i now set up a reatisohip by duplicaing the table- apologies if this is tiresome

  6. #21
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Simply drag the field from one table to the other. Then repeat for the duplicated table to the other related table. Right click the join line to set the type.

    Relationships are important, but don't get carried away with enforcing referential integrity at this point.
    That can follow once you have the data correctly stored.
    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 ↓↓

  7. #22
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    ok so im playing around designing forms before i get to the sexy querying stuff

    I assume this is the kind of stuff i needClick image for larger version. 

Name:	form 1.jpg 
Views:	23 
Size:	76.4 KB 
ID:	38358

    any tips for layouts etc- can i adjust the size of each box - postcode for example only needs to be 8 characters in the uk

  8. #23
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Before you get carried away with forms, make sure your data storage can do everything you want it to, and that the reports you want can be pulled from that data.
    Get a pen and paper out, or whiteboard, draw your design out and "move" some trial data around in it.

    Make absolutely sure everything you need can be achieved via pen and paper first.
    If it can't then your design isn't complete.

    If the above isn't working all the sexy forms in the world won't help.

    Forms and UI are actually the final piece of the puzzle.
    Redesigning them after you add another couple of vital fields you forgot, or worse change the whole structure, is a real pain, and creates lots of extra work.
    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 ↓↓

  9. #24
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    Very good advice-i was getting used to the design functions as well - just knocking some rough edges off me.

    so the tough bit as far as i will be concerned will be getting a result of

    1) creating a function whereby i get a phone call list- an email follow up list by date - or daily

    2) creating a to do list of actions to follow up - and/or need to be completed

    ive taken your advice on board and will sit back and try to resolve the data requirements before i plough further in!!

    any other tips advice etc will be greatly received - eg sorting by dates havent looked at that yet

  10. #25
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If your data is stored correctly, then the items you have listed above will be trivial to resolve.

    One thing in your design, try and avoid storing calculated results or data.
    So for instance if you have a follow-up date for a customer that is based around the initial or latest contact for that case, don't store it, calculate it.
    That way you don't need to try and capture every point that your last "thing" was updated to try and update the stored value.

    It's a common mistake that you see frequently with new users.

    As an idea - you could store a default follow up interval in the customer record, and use that in your queries to generate the lists you mention.
    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. #26
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    O I get it so follow up date - last contact date plus 5 days for example

  12. #27
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Yes that's the sort of thing.
    Not storing redundant information and not duplicating information, are both important in good database design.

    It's commonly known as Normalisation - have a good read if you aren't familiar.
    In fact have a good look here http://www.rogersaccesslibrary.com/forum/forum46.html
    and follow the tutorial examples. It will take a few hours in total, but they are really good.
    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 ↓↓

  13. #28
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    Hi - its been a while but i have a fucntional database that ive started to populate.

    there area couple of refinements I now find I could use.

    the relationship table above isnt quite right- what I need is to add a table that shows indivual call logs to the same customercontact ID?

    I assume I add a table like the below relationship ( id remove the comments field from the contact log and use the new table to record comments?

    if so i would need a add new comment button

    I think I may need a redesign to achieve what i want

    Click image for larger version. 

Name:	new table comments.jpg 
Views:	18 
Size:	89.7 KB 
ID:	38884

  14. #29
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Tradesman View Post
    I think I may need a redesign to achieve what i want
    Yes, a few tweaks.

    First, you have a 1-to-1 relationship between tables Comments and CallLogtblNEW. There is no reason for this. It limits the call log to one comment.
    One CallLogtblNEW (record) can have many comments and one Comment is for one CallLogtblNEW. This is a 1-to-many relationship.

    Next, in table TblCustomerContactsNew, I would have have a contact first name and a contact last name.
    I would also have a table for "Roles".


    Consider something like this
    Click image for larger version. 

Name:	Design1.png 
Views:	16 
Size:	94.9 KB 
ID:	38889


    I do not know what some of the fields are used for (especially in tblCallLogs), but the diagram above might help you to move forward.

  15. #30
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    52
    Hi Master!

    yes I rebuilt it and deleted some records that were just pointless goign forward-looks neater now and the reprots ive looked at seem to do what i want.

    one point - ive added the role( ive called product category as the guys i deal with manage different areas eg toys gifts torches etc . how do I set the customer contact so I can update more than 1 product cat for the one customer-ive set it as yes no duplicates - but cant seem to desing the for to enable me to do this - do i just keep processing the add record button.

    so when i search through my contacts where the #name it would be great to see a list of these areas ( or do i design a new screen for this to send it out and use the current form as data entry )

    Click image for larger version. 

Name:	design contact screen.jpg 
Views:	17 
Size:	105.2 KB 
ID:	38896Click image for larger version. 

Name:	design 2.jpg 
Views:	17 
Size:	72.2 KB 
ID:	38897

Page 2 of 6 FirstFirst 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