Reporting Multiple Dates

[G d via AccessMonster.com]

I realize this sounds like it should be posted in the Report section but I am
posting it here because I suspect the answer will involve coding or modifying
my forms. I have multiple tables with bound data input forms for my law
office db. One of the items recorded is due dates for different things. For
example- the statute of limitations for each type of law suit; the date a
response to a pleading or motion is due; the date a Court orders filed by,
dates of hearings and trials, etc. Each of these duedates is recorded in a
different table.
I need to be able to generate a report showing all the duedates for the week.
I've struggled with this for a while now. There are simply too many tables to
make a query feasable. There are over 25 state civil case tables alone. Each
one has several sub-tables with case specific details and almost every table
has at least one due date. I need some way to send each duedate to a
"central storage location" for lack of a better term so that I can generate a
report. Can anyone offer anysuggestions? Can I send each date to Outlook as
it is entered and, if so, how? Or is it possible to create a calendar in
Access and record each date in that calendar?

 

[Linda Burnside]

I need to be able to generate a report showing all the duedates for the
week.
I've struggled with this for a while now. There are simply too many tables
to
make a query feasable. There are over 25 state civil case tables alone.
Each
one has several sub-tables with case specific details and almost every
table
has at least one due date. I need some way to send each duedate to a
"central storage location" for lack of a better term so that I can
generate a
report. Can anyone offer anysuggestions? Can I send each date to Outlook
as
it is entered and, if so, how? Or is it possible to create a calendar in
Access and record each date in that calendar?

First, make a list of all the field names (and their tables) which contain
due dates. Secondly, select some key that ties them all together (some
control number for the case maybe?) Create a temporary table which
contains at least three fields - YourControlKey and DueDate and a
description of the action required for that due date. Start with an empty
table each time you want to run the report. Run a series of queries which
will append due date records to your temporary table. When all of the due
dates have been added to the temporary table, you can then run a report
using more expanded information. You can make a query which shows the case
information (pulling from a separate table which contains the plain english
description/name of a case or your control key - whatever makes sense) the
due dates and the action required.

The next time you want to run the report, you would start by emptying the
temporary table and start the process over again (adding the new/updated due
date information).

Good luck,

Linda

 

[Ronald Roberts]

I realize this sounds like it should be posted in the Report section but I am
posting it here because I suspect the answer will involve coding or modifying
my forms. I have multiple tables with bound data input forms for my law
office db. One of the items recorded is due dates for different things. For
example- the statute of limitations for each type of law suit; the date a
response to a pleading or motion is due; the date a Court orders filed by,
dates of hearings and trials, etc. Each of these duedates is recorded in a
different table.
I need to be able to generate a report showing all the duedates for the week.
I've struggled with this for a while now. There are simply too many tables to
make a query feasable. There are over 25 state civil case tables alone. Each
one has several sub-tables with case specific details and almost every table
has at least one due date. I need some way to send each duedate to a
"central storage location" for lack of a better term so that I can generate a
report. Can anyone offer anysuggestions? Can I send each date to Outlook as
it is entered and, if so, how? Or is it possible to create a calendar in
Access and record each date in that calendar?

You could write a routine that is executed at startup of the database
that calculates the date needed from a control table. You could use the
control table to store a lead time (number of days) for each table type,
date type and calculated due date. Before you start, reset the
calculated due date in the control table, then update the table with
the new calculated due date and use it as part of each query to control
the dates for each report.

Todays date + the lead time goes into the table as the calculated new
due date. The query could select this date as criteria. Anything where
the due date, in your other tables, is equal to or less than the
calculated due date in the control table. This date could be included
in your query as a function.

Example:
Query
CalcDate: GetDate(TableName, LawSuitType, DateType)

Function:
Function GetDate( tblName as string, LSType as string, DteType as _
string) as date

Use a dlookup or DAO/ADO to return the correct lead time bases on
the table name, law suit type and date type.

logic to calculate the new date using lead time and todays date

GetDate=New Date Answer
End Function

Your control table could be something like:

TableName, LawSuitType, DateType, LeadTime
Ohio Civil hearing 10
Ohio Civil trial 20
and so on...


Hope this helps,

Ron

 

[G d via AccessMonster.com]

Ron and Linda,

Thank you. Both suggestions work. I like Ron's because of the leadtime
aspect. I appreciate the help from both of you.