Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2016
    Posts
    2

    Question Unanswered: How to plug an email table in a contacts database for sending newsletter?

    I work for a company which sends a newsletter to their customers as well as Christmas e-cards. Both are sent by email. We are using an old database which was not properly designed and I have to normalize it. No one in the office knows how to use Access properly, including myself, so I did a fair bit of reading online and followed a few tutorials in order to familiarize myself with DB design. The DB is also used as a contact database with phone numbers and addresses for customers.

    Now, the old database is a single table which lists every person in their own row, with information such as their organization, address (sometimes personal, sometimes the organization's, phone number (work, personal, cell phone, etc...), email address, assigned employee, etc., all of them as attributes of the person. Naturally, since many persons are part of the same organization, there is a lot of redundant data (organization contact info mostly). Now here are the rules on which I try to redesign my database:


    • A person may or may not be part of an organization
    • A person can only belong to one organization, if they do belong to one
    • Many persons can belong to the same organization
    • An organization may or may not have have linked persons (basically, an organization CAN be a customer in itself, but not necessarily)
    • Some persons provide provide a personal email address (either at work or at home) or sometimes the main email address of the organization is provided. It is important to differentiate between those email address because the greetings reflects the kind of addressee (either Mr. xxxx, Ms. xxxx, or a generic greeting for a whole organization).




    It seemed to make sense to me to separate persons and organizations in their own tables, linking them with an optional one-to-many relationship. However, when it comes to emails, this gives me a problem. I cannot put email addresses as an attribute in the persons and organizations table, because each email address needs to have two boolean attributes itself: whether the person OR organization wishes to receive our newsletter/christmas card on that email address. Therefore, emails have to be in their own table. However, since persons and organizations are each in their own table, I don't think I can use both their primary keys as foreign keys in the emails table (i.e. having one row linking to an organization and the next linking to a person). But they are all emails, and I would like them to be unique and make sure there is no duplication (basically if a customer unsubscribe from the newsletter, is it important that this emails exists only in ONE place to make sure that they stop receiving the emails, also to prevent an email being sent more than once at the same email address), so I'm not too sure about making two separate tables for emails: organizations and persons.

    Here is my ERD so far:

    Click image for larger version. 

Name:	Capture.JPG 
Views:	8 
Size:	56.6 KB 
ID:	16708

    I'm not sure where and how to plug in the emails table. I'm also not sure about the ERD in general, this is my first time and I might have overlooked something completely. Any input on the matter would be greatly appreciated!

    Guillaume

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    I have the tEmployee table with field Email. 1 corp email / employee.

    I have a form with a list box. Above is a combo box to pick from a table [caption],[Qry]
    Each query pulls different lists. I pick the batch list from the combo:
    pick Company, which pulls all employees emails
    pick Customers, which pulls only customers from the tClient table.

    once the query is picked, the listbox fills with name,email.
    cboQry_afterupdate
    lstEAddrs.rowsource = cboQry

    Enter Text box (or list box) picks the report (for body of email),
    enter a Subject box.
    Click button to scan each person in the list box, to get the TO address
    then send thru outlook:

    Code:
    '------------
    Public Sub btnSendEmails_click()
    '------------
    Dim vTo, vSubj, vBody, vRpt
    Dim vFilePath
    dim i as integer
    
    vRpt = txtRpt
    vSubj = txtSubj
    vBody = txtBody
    
         'scan the list box
    For i = 0 To lstEAddrs.ListCount - 1
       vTo = lstEAddrs.Column(1)           'col0 = user, col1=email
        
       DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody
    Next
    End Sub

  3. #3
    Join Date
    Jan 2016
    Posts
    2
    Quote Originally Posted by ranman256 View Post
    I have the tEmployee table with field Email. 1 corp email / employee.

    I have a form with a list box. Above is a combo box to pick from a table [caption],[Qry]
    Each query pulls different lists. I pick the batch list from the combo:
    pick Company, which pulls all employees emails
    pick Customers, which pulls only customers from the tClient table.

    once the query is picked, the listbox fills with name,email.
    cboQry_afterupdate
    lstEAddrs.rowsource = cboQry

    Enter Text box (or list box) picks the report (for body of email),
    enter a Subject box.
    Click button to scan each person in the list box, to get the TO address
    then send thru outlook:

    Code:
    '------------
    Public Sub btnSendEmails_click()
    '------------
    Dim vTo, vSubj, vBody, vRpt
    Dim vFilePath
    dim i as integer
    
    vRpt = txtRpt
    vSubj = txtSubj
    vBody = txtBody
    
         'scan the list box
    For i = 0 To lstEAddrs.ListCount - 1
       vTo = lstEAddrs.Column(1)           'col0 = user, col1=email
        
       DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody
    Next
    End Sub
    Thank you for taking the time to answer. Unfortunately, this is complete gibberish to me... I was looking for a user-friendly way to my problem, I have absolutely no experience in programming. Am I in the wrong section of the forums?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •