Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28

    Help with a query. See attached database

    I am trying to create a query that pulls people into a report if they have EPLI sold in a specific month, or Accidental death, or cyber insurance or Occupational Acc all in a specific month. The way it works is they click the box on the Contact form next to the item they sold. Enter the premium, and the date they sold the product.

    Then on the "all products current month second version" query is should pull in the names that fit that criteria. RIght now it does pull someone into the report. But the issue is if we sell EPLI in February, they will be pulled into the query, But then if I sold a cyber insurance in january that also shows on the report. So I only need the policy name with the actual product that was sold in that particular month.

    CRM-Version-3.zip

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The CG Info table is not normalized structure. Multiple Yes/No fields for similar data is not normalized. This makes for very difficult (nearly impossible) filtering. I have tried to help posters with this and find it too frustrating. Products purchased should be in a related table.

    tblCGDetails
    ID (PK)
    ContactID (FK)
    ProductID (EPLI, Cyber, ADD, OAI)
    DateSold
    Premium
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    I agree that table needs to be normalized.
    Here is a tutorial on designing your tables and relationships that may be helpful.

    Good luck with your project.

  4. #4
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    Quote Originally Posted by orange View Post
    I agree that table needs to be normalized.
    Here is a tutorial on designing your tables and relationships that may be helpful.

    Good luck with your project.
    Thank you for your tips. Ill take a look at the document you posted and see if I can figure it out. Maybe when I make some changes you can take a look? Any quick tips on normalizing?

  5. #5
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    Quote Originally Posted by June7 View Post
    The CG Info table is not normalized structure. Multiple Yes/No fields for similar data is not normalized. This makes for very difficult (nearly impossible) filtering. I have tried to help posters with this and find it too frustrating. Products purchased should be in a related table.

    tblCGDetails
    ID (PK)
    ContactID (FK)
    ProductID (EPLI, Cyber, ADD, OAI)
    DateSold
    Premium

    can you explain these a little bit. Are those the fields you would put into a separate table?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Yes. That is related (dependent, child) table.

    ProductID field would have values EPLI, Cyber, ADD, OAI.

    I was having a problem with the link posted by orange. Here is link to that site home page http://www.rogersaccesslibrary.com/

    It is a balancing act between normalization and ease of data entry/edit. Follow the rules until you need to break them. I mean there can be too much as well as too little normalization.

    The Yes/No fields are actually redundant anyway. If there is data in the premium field for a product, then obviously the product was sold. Although don't see a premium field for each product.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    Quote Originally Posted by June7 View Post
    Yes. That is related (dependent, child) table.

    ProductID field would have values EPLI, Cyber, ADD, OAI.

    I was having a problem with the link posted by orange. Here is link to that site home page http://www.rogersaccesslibrary.com/

    It is a balancing act between normalization and ease of data entry/edit. Follow the rules until you need to break them. I mean there can be too much as well as too little normalization.

    The Yes/No fields are actually redundant anyway. If there is data in the premium field for a product, then obviously the product was sold. Although don't see a premium field for each product.
    So I am still lost on how to do this. I am a real beginner. For me to be able to create those queries I was referring to what do I need to pull out of the CG info table and put into its own table. I have a check box for products sold. For each product. Then I need to attach a date to the sale and a premium amount that was sold. Then I need to connect that to an contact in the database.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Post 2 suggests the related table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    So I create that table with those details you mentioned.

    tblCGDetails - Name of the table
    ID (PK) - This is the primary Key.
    ContactID (FK) -Foreign key.
    ProductID (EPLI, Cyber, ADD, OAI) This one I am confused about. Is this a third key. Or is the column name just product ID?
    DateSold
    Premium

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Field named ProductID (or ProductCode if you prefer). It will have values of EPLI, Cyber, ADD, OAI which means up to 4 records for each ContactID, unless you expand to more products.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    Quote Originally Posted by June7 View Post
    Field named ProductID (or ProductCode if you prefer). It will have values of EPLI, Cyber, ADD, OAI which means up to 4 records for each ContactID, unless you expand to more products.
    SO will EPLI cyber, add and OAI be a drop down box with values that come from a different table?

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    A combobox would be useful. The dropdown RowSource can be a static Value List or can be table source. The table allows more flexibility for product expansion.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    I added all the columns but the Contact ID. To add that to the table do I do a lookup and relationship?

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I NEVER create lookups in tables. I build comboboxes on forms. Regardless, create the field.

    If you want to build relationship in Relationships window, do so.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    Im still confused and hopefully this is the last question. How do I add the ContactID field to the new table as a foreign key without doing a lookup & relationship and make the records connected.

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

Similar Threads

  1. Replies: 14
    Last Post: 02-20-2014, 12:11 PM
  2. Replies: 3
    Last Post: 05-23-2012, 03:05 AM
  3. Can somebody review the code in attached database?
    By A Abbas in forum Programming
    Replies: 4
    Last Post: 01-27-2012, 04:57 AM
  4. Replies: 6
    Last Post: 06-27-2011, 07:11 PM
  5. Replies: 1
    Last Post: 07-09-2006, 09:23 AM

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