Advanced Problem: How Do I Make a, Multiple Date, Date Counter

[Sgt Gordon]

I have a database that I track the course of Military Courts. I have to be
able to track, in number of days, the how long the case has gone and stop on
another date. The Fields are:

1. Date Charges were Preferred
2. Date Entered Pretrial Restraint
3. Date Entered Pretrial Confinement
4. Date of Arraignment
5. Date of Trial

I need it to be able to pick the earliest date from 1, 2, 3, and calculate
the number of days (what day it is on) till the date of 4 and another to the
date of 5.

 

[Jeff Boyce]

From your description, you have a ... spreadsheet!

If you were limited to using Excel (or some other spreadsheet), you'd
probably end up using "repeating columns" (date of x, date of y, date of z).
But Access is a relational database, and you will not get the best use of
its relationally-oriented features and functions if you feed it 'sheet data.

If you want to find the "earliest date" (i.e., Minimum) from a set of dates
in Access, you need to have them in a single field, separate records, not
separate fields, one record.

Is there a reason you aren't using Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lance]

To get the earliest date of the 3 fields you can use nested iif statements,
like this:

IIf(MY_TABLE!Preferred<MY_TABLE!Restraint And
MY_TABLE!Preferred<MY_TABLE!Confinement,MY_TABLE!Preferred,IIf(MY_TABLE!Restraint<MY_TABLE!Confinement,MY_TABLE!Restraint,MY_TABLE!Confinement))

You can then use that in conjunction with the datediff function to determine
how many days it's been.

The SQL would look something like:

SELECT MY_TABLE.ID, IIf(MY_TABLE!Preferred<MY_TABLE!Restraint And
MY_TABLE!Preferred<MY_TABLE!Confinement,MY_TABLE!Preferred,IIf(MY_TABLE!Restraint<MY_TABLE!Confinement,MY_TABLE!Restraint,MY_TABLE!Confinement))
AS EARLIEST_DATE, DateDiff("d",[EARLIEST_DATE],[Trial]) AS DAYS_TO_TRIAL,
DateDiff("d",[EARLIEST_DATE],[Arraignment]) AS DAYS_TO_ARRAIGNMENT
FROM MY_TABLE;

You would obviously need to adjust the table and field names to match what
your database contains.

 

[Sgt Gordon]

Well there are more fields then just those. Those were just the fields that
were part of the problem. I have to track Name, Unit, Charges, Timelines,
Actions, Punishments. All this information on each case and then all that
data has to be able to be sorted each way and then sorted by multiple fields.


I have a 120 Clock on here that I run to tell the lawyers you are on this
date and that at 120 the case will be dropped.

We used to use Spreadsheets but you can not see single files (Forms) and
then click and automatically generate a report with all the file that pertain
to this, that, or the other thing. Also we have multiple clerks generating
data from four locations and with Spreadsheet one person can only use it at a
time but with ours multiple clerks can be working and then the next second I
can generate a report from three or four locations.

 

[KARL DEWEY]

Following on what Jeff Boyce said you need two table in a one-to-many
relationship. Use a form and subform to view and edit the data.
The one side of the set would have CaseID (primary key), Name, Unit,
Charges, Timelines, Actions, Punishments, etc. The second table, many side
of the relation, CaseAction, ActionType, and ActionDate.
You could have another table for the ActionTpye with records like this --
Charges Preferred
Pretrial Restraint
Pretrial Confinement
Arraignment
Trial Start/Resume
Trial Postponement
Trial End
Punishment Begin
Punishment End
In the subform have combox/listbox to pick an action.

 

[John W. Vinson]

I have a database that I track the course of Military Courts. I have to be
able to track, in number of days, the how long the case has gone and stop on
another date. The Fields are:

1. Date Charges were Preferred
2. Date Entered Pretrial Restraint
3. Date Entered Pretrial Confinement
4. Date of Arraignment
5. Date of Trial

I need it to be able to pick the earliest date from 1, 2, 3, and calculate
the number of days (what day it is on) till the date of 4 and another to the
date of 5.

Not knowing anything about military court procedures, this may be an
irrelevant question but... what if there are other dates? Date of pretrial
hearing? Date of first continuation? Date of third continuation?

A better design would be to have a Case table related one-to-many to a
CaseEvents table; instead of one *field* for each event pertaining to a case,
you could have one *record*, with fields for CaseNumber, EventDate, and
EventType. With this design a simple self-join query can get you the time
elapsed between any pair of dates.

John W. Vinson [MVP]