AnsweredAssumed Answered

related records by date range

Question asked by BillGreen on Dec 12, 2013
Latest reply on Dec 12, 2013 by BillGreen

Title

related records by date range

Post


     I'm stumped on what I thought should be fairly straight forward, and I'm sorry if this has been addressed elsewhere. I need to report on responses to email campaigns. My trouble is between 2 related tables:

     table 1, each record contains:

     campaign_ID: text

     email_ID: text

     emailSendDate: date (mm/dd/yyyy)

     nextSendDate: date (mm/dd/yyyy)

      

     Table 2, each record contains:

     campaign_ID

     responseDate: date (mm/dd/yyyy)

     other response data

      

     The relationship is based on campaign_ID, which is unique for every campaign but not for every email in the campaign. One of my goals is to see, over a period of time, which email version is performing better.

     I've tried a portal in table 1 where the relationship is defined as:

     campaign_ID = campaign_id

     I'm filtering the portal with:

     table 2::responseDate >= table 1::emailSendDate and table 2::responseDate <= table 1::next_sendDate

     The portal displays the same data in every record of table 1 - the data for record 1. I thought that the AND criteria would filter the portal results based on the email send dates.

      

     I've tried to remove the portal filter and set up the criteria in the relationship but I'm not getting anywhere. I don't have the relationship set up correctly and need some advice, please.

Outcomes