related records by date range
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:
emailSendDate: date (mm/dd/yyyy)
nextSendDate: date (mm/dd/yyyy)
Table 2, each record contains:
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.