query/report on multiple dates

[carriey]

I have a table that houses a number of due dates and reminder dates. When
you type the due date in the form it automatically calculates when the
reminder should be sent. The first date will always happen (Decl_Due and
Send_Decl_Remind) then depending on the decisions made one or many of the
following could happen:

Sale_Date / Send_Sale_Remind
Trsfr_Date / Send_Trsfr_Remind
Trsf_Ext_Date / Send_Trsf_Ext_Remind
Aband_Date / Send_Aband_Remind
Aband_Ext_Date / Send Aband_Remind

For example they may decide to sell and the sale falls through. Then they
may decide to transfer, the transfer may take awhile and they will need to
request an extension. After that, the transfer may fall through and they may
decide to abandon.
Or they could go from sale to abandon. Or they may skip everything and go
straight to abandon.

We need to keep track of all of events which is why we have so many dates
but in terms of sending reminders - I just need a query that will give me a
report that will list when I need to send reminders no matter what the event
is (preferably in the order they need to be sent).

I have an active/closed field so my query would be on all active records.
Is there a way I can query on all the reminder dates but somehow only the
current applicable one and sort accordingly?

 

[[MVP] S.Clark]

There is a lot going on here, so the answer is probably going to require a
few hours of VBA coding to make it really useful.

I think I would have a status table that holds the statusdate, statusname,
statusreminddate. Then work from there to achieve the goal.

 

[John Vinson]

I have a table that houses a number of due dates and reminder dates. When
you type the due date in the form it automatically calculates when the
reminder should be sent. The first date will always happen (Decl_Due and
Send_Decl_Remind) then depending on the decisions made one or many of the
following could happen:

Sale_Date / Send_Sale_Remind
Trsfr_Date / Send_Trsfr_Remind
Trsf_Ext_Date / Send_Trsf_Ext_Remind
Aband_Date / Send_Aband_Remind
Aband_Ext_Date / Send Aband_Remind

For example they may decide to sell and the sale falls through. Then they
may decide to transfer, the transfer may take awhile and they will need to
request an extension. After that, the transfer may fall through and they may
decide to abandon.
Or they could go from sale to abandon. Or they may skip everything and go
straight to abandon.

We need to keep track of all of events which is why we have so many dates
but in terms of sending reminders - I just need a query that will give me a
report that will list when I need to send reminders no matter what the event
is (preferably in the order they need to be sent).

I have an active/closed field so my query would be on all active records.
Is there a way I can query on all the reminder dates but somehow only the
current applicable one and sort accordingly?

This begins to look like a table design change might be in order.
Rather than having ten date fields, you might want to consider a one
to many relationship to a SignificantDates field, with a foreign key
to this table, a date/time field EventDate, and a field for EventType
containing values like "Sale", "Send Sale Reminder", etc.

Failing that, you could create a UNION query to do the same thing:

SELECT [identifier], "Sale Date" AS Type, [Sale_Date] AS TheDate
FROM yourtable
WHERE [Sale_Date] IS NOT NULL AND [Active]
UNION
SELECT [identifier], "Sale Reminder Date", [Send_Sale_Remind]
FROM yourtable
WHERE [Send_Sale_Remind] IS NOT NULL AND [Active]
UNION
<etc. etc.>

Sort this by TheDate in the last SELECT clause (to sort the entire
recordset chronologically).

John W. Vinson[MVP]