Hi, I have two tables that I am trying to extract information from and am having trouble getting it. I need to create a report that shows a list of users who have not responded to any particular notice that is sent to them. Confirmation reminders go out every seven days until they respond. The tables look like this:
Table 1, Table 2
Table1 contains notice information such as notice name, notice number, etc.
Table2 contains Recipient information such as user Name. It also contains Notice Status and Notice Type.
The problem is that there are multiple notice types such as "Confirmation Reminder" and "Original Notice." Once a notice is sent to a user, a db record is created in Table2 that shows the date the notice was sent, the status as SENT, and the notice type as "Original Notice." Once they respond to that notice, another record is created in Table2 showing the status as Accepted and the notice type, again, as "Original Notice."
If they do not respond, a confirmation notice is sent to them and a db record is created in Table2 with a status of SENT, and the type as "Confirmation Reminder."
My query has to pull up all the records where the notice type = "Confirmation Reminder," but I only need the ones where the "Original Notice" not yet been 'Accepted' AND (this is the kicker) I need the Confirmation Reminder SENT DATE as part of the result set. This date is located in Table2. As it is, I can either pull up records where the Notice Type = "Original Notice" AND Notice Status = "Sent" or "Accepted." OR I can pull up Notice type = "Confirmation Reminder" and Status = "Sent." There are no records where "Confirmation Reminder" = "Accepted" or this would have been easier to do.
Hopefully I have given enough information for someone to help me. Thank you very much
Table 1, Table 2
Table1 contains notice information such as notice name, notice number, etc.
Table2 contains Recipient information such as user Name. It also contains Notice Status and Notice Type.
The problem is that there are multiple notice types such as "Confirmation Reminder" and "Original Notice." Once a notice is sent to a user, a db record is created in Table2 that shows the date the notice was sent, the status as SENT, and the notice type as "Original Notice." Once they respond to that notice, another record is created in Table2 showing the status as Accepted and the notice type, again, as "Original Notice."
If they do not respond, a confirmation notice is sent to them and a db record is created in Table2 with a status of SENT, and the type as "Confirmation Reminder."
My query has to pull up all the records where the notice type = "Confirmation Reminder," but I only need the ones where the "Original Notice" not yet been 'Accepted' AND (this is the kicker) I need the Confirmation Reminder SENT DATE as part of the result set. This date is located in Table2. As it is, I can either pull up records where the Notice Type = "Original Notice" AND Notice Status = "Sent" or "Accepted." OR I can pull up Notice type = "Confirmation Reminder" and Status = "Sent." There are no records where "Confirmation Reminder" = "Accepted" or this would have been easier to do.
Hopefully I have given enough information for someone to help me. Thank you very much