Multiple Date Entry Design And Report Issue

[Guest]

I have a date field that must have its initial date kept forever.
There needs to be a place to have a second date entry (either another column
or making the original date field its own table for multiple entries).
Once dates are inputted (there will be some entires for the initial date and
some entries for a second date), I need to run a report that will show the
most recent dates from either of the date inputs.
What is the best way to design the initial field/table and how do I pull up
the most recent dates?

 

[Tim Ferguson]

=?Utf-8?B?dHJ5aW5nIHRvIGRlbGV0ZSBsb2NraW5nIGZpbGUgLmxkYg==?=

I have a date field that must have its initial date kept forever.

No strict way of doing this in Access. Best you can do is making the
field have a DefaultValue=Now() or =Date() and then hiding it by not
putting it onto the forms etc you use for data entry, or at least making
the textboxes Disabled and Locked.

Somewhat better security comes with a lot more complication using
Access's built-in User Level security and a bunch of RWOP queries.

There needs to be a place to have a second date entry (either another
column or making the original date field its own table for multiple
entries). Once dates are inputted (there will be some entires for the
initial date and some entries for a second date),

This is starting to sound like a Design Problem, actually. Either these
dates are independent of each other, different attributes of the same
thing -- or they are not. If they are not independent, or they are not
directly describing some aspect of the entity that is the table, then
they should be stored somewhere else or in some other way.

I need to run a
report that will show the most recent dates from either of the date
inputs.

SELECT This, That, TheOther,
IIf(OneDate IS NOT NULL, OneDate, TheOtherDate) AS FinalDate,
Etc
FROM MyTable
etc

For a really rigorous treatment it's something like

IIF(OneDate IS NULL,
IIF(OtherDate IS NULL, NULL, OtherDate),
IIF(OtherDate IS NULL, OneDate,
IIF(OneDate > OtherDate, OneDate, OtherDate)
)
) AS FinalDate

but bear in mind that I've not tested that.

What is the best way to design the initial field/table and how
do I pull up the most recent dates?

Quite seriously, I strongly suspect it's something different from what
you've got; but I cannot say any more without knowing more about the
domain.

Hope that helps


Tim F

 

[Guest]

Thanks Tim,

I'm still a bit away from creating the database, but I'll keep your
suggestions and see how they work. As for what domain, I'm not sure I can
answer that since the database will be attached to the company's customized
intranet site, which I know little of. Apparently, people that have access
can click on the Access icon and the database opens. Each person has to have
Access on their desktop to do this. Does this help at all?

 

[Tim Ferguson]

=?Utf-8?B?dHJ5aW5nIHRvIGRlbGV0ZSBsb2NraW5nIGZpbGUgLmxkYg==?=

As for what domain, I'm not sure I
can answer that since the database will be attached to the company's
customized intranet site, which I know little of. Apparently, people
that have access can click on the Access icon and the database opens.

Sorry: I'm using jargon. The "domain" is the real life business practices
and environment and knowledge that the database is to meant to model.
That is, whether you are trying to track ambulance response times,
numbers of widgets sold, students defaulting on classes, etc.

If you are able to tell us more about the problem you are trying to
solve, we may be able to make some suggestions about a robust design.

All the best


Tim F

 

[Guest]

Hi Tim,

The actual business practice is to track changes for a purchasing
department. They create a Master Price List (which needs to be saved and not
changed, and then compared to later changes in dates or quantity fields).
There will be several fields that will be like the dates/quantity fields
where the initial date needs to be saved and no one will be able to change
it. Then there needs to be another area (I was thinking of a subtable) to
contain multiple updates of dates, quantities etc. Then I will need to be
able to compare the initial date versus the most recently inputted date and
report the change or no change.

Does this help?

Nanette

 

[Tim Ferguson]

=?Utf-8?B?dHJ5aW5nIHRvIGRlbGV0ZSBsb2NraW5nIGZpbGUgLmxkYg==?=

The actual business practice is to track changes for a purchasing
department. They create a Master Price List (which needs to be saved
and not changed, and then compared to later changes in dates or
quantity fields). There will be several fields that will be like the
dates/quantity fields where the initial date needs to be saved and no
one will be able to change it. Then there needs to be another area (I
was thinking of a subtable) to contain multiple updates of dates,
quantities etc. Then I will need to be able to compare the initial
date versus the most recently inputted date and report the change or
no change.

It seems to me that the most important entities here are Changes. Changes
have EffectiveDates, Targets (the thing that is changed), NewValues,
Authorisers, etc.

You probably have entities relating to the things that get changed, and
things that contain the things that get changed. Therefore a table of
MasterPriceLists would contain records about the lists themselves; the
Prices table would have one record for every item quoted in each MPList;
and each Prices record would have lots of Changes associated with it. A
structure like that should be able to reconstruct all the master price
lists at any point in time. Whether that meets your need for "comparing"
only you can tell -- it's a bit of a vague word although I am sure you
know what you need to do to accomplish it. Remember that forms are about
processes, but Tables are about Things and you need to get them right
first.

As for fixing the historic data, there is no way to do this infallibly in
Access. There are ways of making it very hard to alter old stuff (and,
after all, you have to be able to correct mistakes and data entry errors)
using Access Security and a technique known as RWOP. More simply, your
forms design can make it awkward for an ordinary user to edit the tables
themselves. For maximum security and a formal audit trail you need to
move up to a proper industrial-strength DBMS like SQL Server or MySQL.

Hope that helps


Tim F

 

[Tim Ferguson]

=?Utf-8?B?dHJ5aW5nIHRvIGRlbGV0ZSBsb2NraW5nIGZpbGUgLmxkYg==?=

The actual business practice is to track changes for a purchasing
department. They create a Master Price List (which needs to be saved
and not changed, and then compared to later changes in dates or
quantity fields). There will be several fields that will be like the
dates/quantity fields where the initial date needs to be saved and no
one will be able to change it. Then there needs to be another area (I
was thinking of a subtable) to contain multiple updates of dates,
quantities etc. Then I will need to be able to compare the initial
date versus the most recently inputted date and report the change or
no change.

It seems to me that the most important entities here are Changes. Changes
have EffectiveDates, Targets (the thing that is changed), NewValues,
Authorisers, etc.

You probably have entities relating to the things that get changed, and
things that contain the things that get changed. Therefore a table of
MasterPriceLists would contain records about the lists themselves; the
Prices table would have one record for every item quoted in each MPList;
and each Prices record would have lots of Changes associated with it. A
structure like that should be able to reconstruct all the master price
lists at any point in time. Whether that meets your need for "comparing"
only you can tell -- it's a bit of a vague word although I am sure you
know what you need to do to accomplish it. Remember that forms are about
processes, but Tables are about Things and you need to get them right
first.

As for fixing the historic data, there is no way to do this infallibly in
Access. There are ways of making it very hard to alter old stuff (and,
after all, you have to be able to correct mistakes and data entry errors)
using Access Security and a technique known as RWOP. More simply, your
forms design can make it awkward for an ordinary user to edit the tables
themselves. For maximum security and a formal audit trail you need to
move up to a proper industrial-strength DBMS like SQL Server or MySQL.

Hope that helps


Tim F