Design Question
Kat
get it right from the begining. Thanks for any help in advance.
The database will record entries into a warehouse enviorment of stock and
the process off our main inventory system. Each delivery will have a record
in tblDelivery with a unique DeliveryID and related information to that
delivery. When it is first entered it will have a status of "new" set
against it and the date/time needs to be recorded. Seperate stock items are
not part of this database only the delivery as a whole.
This is where I have run into my problem. I had originally intended to have
a seperate tblStatus where I would keep this information. Each time the
delivery moved to a different status, it would then have a new record added
into tblStatus.
I am not sure this is the best set up however because part of the reporting
I will need to do is how long something was "in process" and the current
stage of any or all deliveries. In general I am only interested in broad
information such as on Monday we had 160 deliveries and each delivery took an
average of 2hrs to complete. However I will need the ability to drill down to
a by delivery analysis of something is flagged up as inconsistant.
The current set up of the database is
tblDeliveries
DeliveryID (PK)
Delivery feilds (general information)
tblStatus
DeliveryID(FK)
StatusID(FK - codes used from a lookup table)
DateChanged (date field)
TimeChanged(time field)
tblLocation - this will need to be set up in the same was as the status
table to do much the same thing only handle the locations the shipment has
been moved to. Not actually set this one up until I resolve the current issue.
There is a one to many relationship between tblDelivery and tblStatus.
Will this table stucture allow for the kind of reporting I need? and If so,
could you give an example of the "high" level reporting such as how long on
average the deliveries for a day have taken to go from status new to status
complete considering there could be up to 5 different statuses inbetween?
Hopefully this is enough information, and thank you for any help in advance.
Kat
Jason Lepack
Devlivery_Status:
delivery_id (FK - references Deliveries)
status_id (FK - references Statuses)
status_changed_dt (datetime variable)
Based on your description I get the hint that each delivery will only
be in a given status once, therefore you may have a primary key on
(delivery_id, status_id).
Kat
Thanks for your reply. Indeed each delivery will only have a status once
(or so I am building this to belive) and it will move through the statuses in
a linear manner.
If I use a single date/time field as you've suggested is it easy to
determine how long a particular delivery has taken from status new to status
completed? I'm worried that it will be difficult to determine actual time
spans, which I am sure the client will want in the future, even if they don't
know it now.
Any chance I could bother you further for an example of the query to do
this? I am a bit stumped on how to get it to look for the first status (call
it "new") and find the difference(date/time wise) to the last status (call it
"complete") without dlookups in the query which will effect performace after
a few months and a few hundred thousand records.
Thanks again for your help so far.
Kat
Jason Lepack
SELECT
A.delivery_id,
A.status_id AS start_status,
A.status_changed_dt AS start_status_dt,
B.status_id AS end_status,
B.status_changed_dt AS end_status_dt,
DateDiff("s",[B].[status_changed_dt],[A].[status_changed_dt]) AS
time_elapsed
FROM
Delivery_Status AS A
INNER JOIN Delivery_Status AS B
ON A.delivery_id = B.delivery_id
If you find for certain that statuses will travel 1,2,3... you could
add:
WHERE
A.status_id < B.status_id
The query is a list for all deliveries, each possible pair of statuses
and their times at which they entered that status, as well as the
difference between those two times.
Cheers,
Jason Lepack
> > > Kat- Hide quoted text -
>
> - Show quoted text -
Chris2
"Kat" <K...@discussions.microsoft.com> wrote in message
news:D9CC608B-A886-4944...@microsoft.com...
Kat,
Comments:
You should probably not use two DATETIME columns, one for date, and one for time. In
order to work with this, you will find yourself hopping through endless function
manipulation of those columns.
The SQL DATETIME data type represents a point in time, not an interval. Recording just
one point in time per row (whether with one DATETIME column, or two as you proposed) means
that you must use points in time from different rows to somehow find a duration between
two points in time. This is quite difficult (but not impossible) in SQL.
By keeping two points in time in a row, you have the whole duration involved right there
were you need it.
When a new status is ready to be entered, your Forms in MS Access will take the current
Now() value, and then UPDATE that value into StatusEnd for the status that is about to
end, and then INSERT a new row with a StatusStart equal to that same value (and the new
StatusID, etc.).
Tables:
Create a blank MS Access database. You can copy and paste these DDL SQL queries each into
an MS Access Query, executing each one in order to create the tables and the
relationships.
Query: Create_Deliveries
CREATE TABLE Deliveries
(DeliveryID AUTOINCREMENT
,TruckID INTEGER
,CONSTRAINT pk_Deliveries
PRIMARY KEY (DeliveryID)
)
(TruckID is a column I added into the table so that you can manually enter the sample data
given below. If there is only an autonumber column in a table, you can't manually enter
data. It is also reasonable to assume that each delivery arrives on a truck, although I
realize your system may not keep track of this. If it did, TruckID would likely be a
foreign key to a Trucks table, and that key is also omitted in this example.)
Query: Create_Statuses
CREATE TABLE Statuses
(StatusID AUTOINCREMENT
,StatusName TEXT(255)
,CONSTRAINT pk_Statuses
PRIMARY KEY (StatusID)
,CONSTRAINT un_Statues_StatusName
UNIQUE (StatusName)
)
Query: Create_DeliveryStatuses
CREATE TABLE DeliveryStatuses
(DeliveryStatusID AUTOINCREMENT
,DeliveryID INTEGER NOT NULL
,StatusID INTEGER NOT NULL
,StatusStart DATETIME NOT NULL
,StatusEnd DATETIME
,CONSTRAINT pk_DeliveryStatuses
PRIMARY KEY (DeliveryStatusID)
,CONSTRAINT fk_DeliveryStatuses_Deliveries
FOREIGN KEY (DeliveryID)
REFERENCES Deliveries (DeliveryID)
,CONSTRAINT fk_DeliveryStatuses_Statuses
FOREIGN KEY (StatusID)
REFERENCES Statuses (StatusID)
,CONSTRAINT un_DeliveryStatuses_DeliveryID
UNIQUE (DeliveryID
,StatusID
,StatusStart)
)
Sample Data:
You will need to manually load the following data into the tables above.
Deliveries
DeliveryID
1, 1
2, 1
3, 1
Statuses
StatusID, StatusName
1, New
2, Early Middle
3, Middle
4, Late Middle
5, Completed
DeliveryStatuses
DeliveryStatusID, DeliveryID, StatusID, StatusStart, StatusEnd
1, 1, 1, 07/13/2007 8:00 AM, 07/13/2007 09:00 AM
2, 1, 2, 07/13/2007 9:00 AM, 07/13/2007 10:00 AM
3, 1, 3, 07/13/2007 10:00 AM, 07/13/2007 11:00 AM
4, 1, 4, 07/13/2007 11:00 AM, 07/13/2007 12:00 PM
5, 1, 5, 07/13/2007 12:00 PM, 07/13/2007 01:00 PM
6, 2, 1, 07/13/2007 3:00 PM, 07/13/2007 04:00 PM
7, 2, 2, 07/13/2007 4:00 PM, 07/13/2007 05:00 PM
8, 2, 3, 07/14/2007 8:00 AM, 07/14/2007 09:00 AM
9, 2, 4, 07/14/2007 9:00 AM, 07/14/2007 10:00 AM
10, 2, 5, 07/14/2007 10:00 AM, 07/14/2007 11:00 AM
11, 3, 1, 07/14/2007 08:00 AM
Queries:
You may create and execute the following queries to test this.
Query: ElapsedStatusTimes
(Watch out for line-wrap.)
SELECT DS1.DeliveryID
,Format(SUM(DateDiff("s", DS1.StatusStart, DS1.StatusEnd)) \ 3600, "00")
& ":" &
Format(SUM(DateDiff("s", DS1.StatusStart, DS1.StatusEnd)) \ 60 MOD 60, "00")
& ":" &
Format(SUM(DateDiff("s", DS1.StatusStart, DS1.StatusEnd)) MOD 60, "00")
As ElapsedStatusTime
FROM DeliveryStatuses AS DS1
WHERE DS1.StatusEnd IS NOT NULL
GROUP BY DS1.DeliveryID
Results:
DeliveryID, As TotalElapsedStatusTime
1, 05:00:00
2, 05:00:00
Sincerely,
Chriis O.
Kat
Kat
Kat
linearly. It didn't even occur to me to have both a start and end date/time
and a new record for each status. For some strange reason my brain got stuck
on either having a new record for each status with only one date, or having
some horribly badly designed table with a date/time field for each status.
Its amazing how simple things can be when someone suggests them.
Thanks a bunch again!
Kat
Jamie Collins
<rainofsteel.NOTVA...@GETRIDOF.luminousrain.com> wrote:
>
> CREATE TABLE DeliveryStatuses
> (DeliveryStatusID AUTOINCREMENT
> ,DeliveryID INTEGER NOT NULL
> ,StatusID INTEGER NOT NULL
> ,StatusEnd DATETIME
> ,CONSTRAINT pk_DeliveryStatuses
> PRIMARY KEY (DeliveryStatusID)
> ,CONSTRAINT fk_DeliveryStatuses_Deliveries
> FOREIGN KEY (DeliveryID)
> REFERENCES Deliveries (DeliveryID)
> ,CONSTRAINT fk_DeliveryStatuses_Statuses
> FOREIGN KEY (StatusID)
> REFERENCES Statuses (StatusID)
> ,CONSTRAINT un_DeliveryStatuses_DeliveryID
> UNIQUE (DeliveryID
> ,StatusID
> ,StatusStart)
> )
>
> Sample Data:
>
> DeliveryStatusID, DeliveryID, StatusID, StatusStart, StatusEnd
> 1, 1, 1, 07/13/2007 8:00 AM, 07/13/2007 09:00 AM
> 2, 1, 2, 07/13/2007 9:00 AM, 07/13/2007 10:00 AM
> 3, 1, 3, 07/13/2007 10:00 AM, 07/13/2007 11:00 AM
> 4, 1, 4, 07/13/2007 11:00 AM, 07/13/2007 12:00 PM
> 5, 1, 5, 07/13/2007 12:00 PM, 07/13/2007 01:00 PM
> 6, 2, 1, 07/13/2007 3:00 PM, 07/13/2007 04:00 PM
> 7, 2, 2, 07/13/2007 4:00 PM, 07/13/2007 05:00 PM
> 8, 2, 3, 07/14/2007 8:00 AM, 07/14/2007 09:00 AM
> 9, 2, 4, 07/14/2007 9:00 AM, 07/14/2007 10:00 AM
> 10, 2, 5, 07/14/2007 10:00 AM, 07/14/2007 11:00 AM
> 11, 3, 1, 07/14/2007 08:00 AM
>
Your proposed table wants a sequenced 'primary key' e.g.
12, 2, 4, 07/14/2007 10:30 AM, 07/14/2007 10:45 AM
creates an overlapping period i.e. duplication.
You probably also need to pay regard to transitions: the above row
represents a 'regression' from status 5 to status 4, presumably a
business rule violation, as I assume would this:
11, 3, 1, 07/14/2007 08:00 AM, 07/14/2007 09:00 AM -- UPDATE
13, 3, 5, 07/14/2007 09:00 AM, 07/14/2007 10:00 AM -- INSERT
which jumps straight from 'New' to 'Complete' while omitting the
intermediate steps.
Jamie.
--
Jamie Collins
> The best way to handle the Delivery_Status table is like this:
>
> Devlivery_Status:
> delivery_id (FK - references Deliveries)
> status_id (FK - references Statuses)
> status_changed_dt (datetimevariable)
>
> Based on your description I get the hint that each delivery will only
> be in a given status once, therefore you may have a primary key on
> (delivery_id, status_id).
Not only that, they have to be 'given' statuses in sequence (1, 2,
3...) and in chronological order. Care to post an amendment to your
proposal to enforce this business rule?
I acknowledge this may be a little unfair may be because you table is
fundamentally flawed e.g. if I knew the transition dates for statuses
1,2,4,5, (i.e. had a missing date for status 3) I would not be able to
model the missing data using your proposal, it would erroneously show
the end date for status 2 as being the start date for status 4.
Jamie.
--
Jason Lepack
>
> Not only that, they have to be 'given' statuses in sequence (1, 2,
> 3...) and in chronological order. Care to post an amendment to your
> proposal to enforce this business rule?
Not really.
In SQL Server I would use triggers to implement this. However, in MS
Access, I don't know how to do this, other than through VBA, but that
doesn't guard against someone entering data through the back-end.
Also, my solution cares not about when a package leaves a status, but
uses the difference in time between the "start" date/times of each
status.
If you have some advice that would be nice, but based on the
environment, I don't see any problem with my proposal.
Cheers,
Jason Lepack
Jamie Collins
> > Not only that, they have to be 'given' statuses in sequence (1, 2,
> > 3...) and in chronological order. Care to post an amendment to your
> > proposal to enforce this business rule?
>
> Not really.
Apathy is a real killer for design-by-email <g>.
> my solution cares not about when a package leaves a status, but
> uses the difference in time between the "start" date/times of each
> status.
> I don't see any problem with my proposal.
That *is* the problem with your proposal i.e. remove a row and another
row gets an entirely new and erroneous end date. IIRC the jargon is
'delete anomaly'.
> If you have some advice that would be nice
In brief, add an end_date to the table. Add a CHECK constraint or
validation rule to test that start_date occurs before end_date. Add
candidate keys (delivery_id, status_id, start_date), (delivery_id,
status_id, end_date), (delivery_id, status_id, start_date, end_date),
making an arbitrary decision about which to promote to PK
(alternatively employ knowledge of clustering on disk <g>). Add a
CHECK constraint to ensure no overlapping periods for each
delivery_id. Assuming status codes are sequential, add a CHECK
constraint to ensure contiguous periods for each delivery_id have
sequential status values and a further CHECK constraint to ensure that
for each delivery_id there are no earlier periods with a status code
greater than a later status code.
FWIW if the transitions are non-sequential, create a two-column table
of legal transitions (e.g. 'from_status' and 'to_status') and
corresponding columns in the usage table with referential integrity
(of course) and appropriate CHECK constraints, though hard to say what
they would be without knowledge of the transitions e.g. cyclic?
Jamie.
--
Jason Lepack
> CHECK constraint to ensure no overlapping periods for each
> delivery_id. Assuming status codes are sequential, add a CHECK
> constraint to ensure contiguous periods for each delivery_id have
> sequential status values and a further CHECK constraint to ensure that
> for each delivery_id there are no earlier periods with a status code
> greater than a later status code.
How would you go about writing these CHECK constraints to check for
overlaps, and ordered statuses? I didn't think that was possible with
ms access. In SQL Server I implement these using triggers.
Cheers,
Jason Lepack
Jason Lepack
> CHECK constraint to ensure no overlapping periods for each
> delivery_id. Assuming status codes are sequential, add a CHECK
> constraint to ensure contiguous periods for each delivery_id have
> sequential status values and a further CHECK constraint to ensure that
> for each delivery_id there are no earlier periods with a status code
> greater than a later status code.
How would you go about writing these constraints in ms access? In SQL
Server I use triggers to implement things such as this. I didn't
think it was possible to implement these in standard sql.
Cheers,
Jason Lepack
Chris2
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1184577254.8...@57g2000hsv.googlegroups.com...
> Your proposed table wants a sequenced 'primary key' e.g.
>
> 12, 2, 4, 07/14/2007 10:30 AM, 07/14/2007 10:45 AM
>
> creates an overlapping period i.e. duplication.
What is your solution to the prevention of overlapping dates in DDL SQL for this case?
What fully working solution would you provide to the OP?
>
> You probably also need to pay regard to transitions: the above row
> represents a 'regression' from status 5 to status 4, presumably a
> business rule violation, as I assume would this:
Yes, DeliveryID and StatusID need to be indexed separately to stop that.
>
> 11, 3, 1, 07/14/2007 08:00 AM, 07/14/2007 09:00 AM -- UPDATE
> 13, 3, 5, 07/14/2007 09:00 AM, 07/14/2007 10:00 AM -- INSERT
>
> which jumps straight from 'New' to 'Complete' while omitting the
> intermediate steps.
No requirement specifying that all statuses be present in consecutive order (or any order)
was hinted at.
Sincerely,
Chris O.
Chris2
"Jason Lepack" <jle...@gmail.com> wrote in message
news:1184596697.8...@n2g2000hse.googlegroups.com...
> I don't know if my previous response went through...
>
> > CHECK constraint to ensure no overlapping periods for each
> > delivery_id. Assuming status codes are sequential, add a CHECK
> > constraint to ensure contiguous periods for each delivery_id have
> > sequential status values and a further CHECK constraint to ensure that
> > for each delivery_id there are no earlier periods with a status code
> > greater than a later status code.
>
> How would you go about writing these constraints in ms access? In SQL
> Server I use triggers to implement things such as this. I didn't
> think it was possible to implement these in standard sql.
>
> Cheers,
> Jason Lepack
>
In theory, you could write some supporting CHECK constraints.
Personally, I would be happy to see a set of CHECK constraints in MS Access that can
prevent the INSERTion of (or UPDATing to) overlapping dates, by group, in the presence of
many prior intervals.
In practice, triggers have to be implemented as VBA code in Form objects in MS Access . .
. which I find to be, well, unpleasant to say the least.
That, or you go with unbound forms and have Class Modules containing all code that
accesses tables, which in turn are instantiated as an object on any form needing to access
the data via the object (that represents an entity, which in turn is represented by a
table) and have any required "additional logic" controlling the table be inside the Class
Module along with the rest of the data access code.
Sincerely,
Chris O.
Chris2
> Thanks Chris, I will give this idea a try. I suppose I was thinking too
> linearly. It didn't even occur to me to have both a start and end date/time
> and a new record for each status. For some strange reason my brain got stuck
> on either having a new record for each status with only one date, or having
> some horribly badly designed table with a date/time field for each status.
> Its amazing how simple things can be when someone suggests them.
>
> Thanks a bunch again!
>
> Kat
>
>
Kat,
You're welcome.
As noted in another branch of this thread, you'll need an extra DeliveryID/StatusID index
on DeliveryStatuses.
The prevention of overlapping dates is something that will have to be detected and
prevented in VBA code. (Barring the appearance of a DDL SQL solution for MS Access.)
Basically, prior to any INSERT or UPDATE, you'll need to instantiate a recordset (or check
the existing one via a recordsetclone (in bound forms)), and then iterate through any
existing dates for the same DeliveryID, and make sure the new dates you are about to enter
don't overlap.
Sincerely,
Chris O.
Kat
Thanks for all your ideas. I have elimated the problem of overlaping
date/times for statuses by not exposing the date/time fields to the user and
updating them at the time of entry. Since the brief of this database is for
the users to add/amend before and after they are done with a task (read
status) this will give date/times close enough for the use of the management.
All the appending/updating/deleting is done via unbound forms with checks
for the right data in vba prior to the updates happening.
After having the discussion with the managers in question, they have also
decided to allow all status movements, ie move from status 1 to 5 back to 2 ,
without checks. If they decide after its up and running that it is a problem
I have a couple of ideas how I would limit the status interactions from your
comments above.
Thanks again for all your help!
Kat
"Chris2" wrote:
>
> "Jason Lepack" <jle...@gmail.com> wrote in message
> news:1184596697.8...@n2g2000hse.googlegroups.com...
> > I don't know if my previous response went through...
> >
> > > CHECK constraint to ensure no overlapping periods for each
> > > delivery_id. Assuming status codes are sequential, add a CHECK
> > > constraint to ensure contiguous periods for each delivery_id have
> > > sequential status values and a further CHECK constraint to ensure that
> > > for each delivery_id there are no earlier periods with a status code
> > > greater than a later status code.
> >
> > How would you go about writing these constraints in ms access? In SQL
> > Server I use triggers to implement things such as this. I didn't
> > think it was possible to implement these in standard sql.
> >
> > Cheers,
> > Jason Lepack
> >
>
> In theory, you could write some supporting CHECK constraints.
>
> Personally, I would be happy to see a set of CHECK constraints in MS Access that can
> prevent the INSERTion of (or UPDATing to) overlapping dates, by group, in the presence of
> many prior intervals.
>
> In practice, triggers have to be implemented as VBA code in Form objects in MS Access . .
> .. which I find to be, well, unpleasant to say the least.
Chris2
> Hi Guys,
>
> Thanks for all your ideas. I have elimated the problem of overlaping
> date/times for statuses by not exposing the date/time fields to the user and
> updating them at the time of entry. Since the brief of this database is for
> the users to add/amend before and after they are done with a task (read
> status) this will give date/times close enough for the use of the management.
> All the appending/updating/deleting is done via unbound forms with checks
> for the right data in vba prior to the updates happening.
>
> After having the discussion with the managers in question, they have also
> decided to allow all status movements, ie move from status 1 to 5 back to 2 ,
> without checks. If they decide after its up and running that it is a problem
> I have a couple of ideas how I would limit the status interactions from your
> comments above.
>
> Thanks again for all your help!
>
> Kat
>
>
Kat,
You're welcome and I'm glad to hear that everything worked out.
Sincerely,
Chris O.
Jason Lepack
overlapping dates in MS Access.
Cheers,
Jason
On Jul 17, 12:55 am, "Chris2"
<rainofsteel.NOTVA...@GETRIDOF.luminousrain.com> wrote:
> "Jason Lepack" <jlep...@gmail.com> wrote in message
> Chris O.- Hide quoted text -
Jamie Collins
> So we're on the same page in that we can't at the DDL level prevent
> overlapping dates in MS Access.
I think when Chris2 said, "In theory, you could write some supporting
CHECK constraints," I think he actually meant to say, "You can write
some supporting CHECK constraints."
Consider the following SQL DDL (which pays no regard to transitions
between statuses):
CREATE TABLE Deliveries
(
delivery_id INTEGER NOT NULL PRIMARY KEY
)
;
CREATE TABLE Statuses
(
status_id INTEGER NOT NULL PRIMARY KEY
)
;
CREATE TABLE Devlivery_Status
(
delivery_id INTEGER NOT NULL
references Deliveries,
status_id INTEGER NOT NULL
references Statuses,
start_date datetime NOT NULL,
end_date datetime,
CHECK (start_date < end_date),
UNIQUE (delivery_id, start_date),
UNIQUE (delivery_id, end_date),
CONSTRAINT no_overlapping_periods
CHECK
(
NOT EXISTS
(
SELECT *
FROM Devlivery_Status AS T1, Devlivery_Status AS T2
WHERE T1.delivery_id = T2.delivery_id
AND T1.start_date < T2.start_date
AND T2.start_date <
IIF(IIF(T1.end_date IS NULL, NOW(), T1.end_date) >
IIF(T2.end_date IS NULL, NOW(), T2.end_date), IIF(T2.end_date IS NULL,
NOW(), T2.end_date), IIF(T1.end_date IS NULL, NOW(), T1.end_date))
)
)
)
;
The above is Access/Jet ANSI-92 Query Mode SQL syntax. To be able to
execute this from a query window in ms access you must first put set
it to ANSI-92 Query Mode (http://office.microsoft.com/en-us/access/
HP030704861033.aspx. Perhaps the easiest way to execute the SQL DDL is
to use an ADO connection with the appropriate OLE DB (either
Microsoft.Jet.OLEDB.4.0 or Microsoft.ACE.OLEDB.12.0) which always uses
ANSI-92 Query Mode SQL syntax. Once in place, the CHECK constraints
will be effective regardless of query mode used to access the data.
If you missed CHECK constraints when they came out (Access 2000, circa
1999), then you may want to review the other features new to Jet 4.0:
Description of the new features that are included in Microsoft Jet 4.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;275561
A new Microsoft Jet 4.0 data type (DECIMAL)
Compressible data types
Lock promotion
Record-level locking
etc
Jamie.
--
Jamie Collins
<rainofsteel.NOTVA...@GETRIDOF.luminousrain.com> wrote:
> > Your proposed table wants a sequenced 'primary key' e.g.
>
> > 12, 2, 4, 07/14/2007 10:30 AM, 07/14/2007 10:45 AM
>
> > creates an overlapping period i.e. duplication.
>
> What is your solution to the prevention of overlapping dates in DDL SQL for this case?
>
> What fully working solution would you provide to the OP?
I wouldn't encourage the OP or anyone else to seek a solution in a
newsgroup thread, it's simply not the forum for anything beyond hints,
general approaches, ideas, suggestions, proposals, etc.
My general approach would be, in a test environment, to add some 'bad'
data to the table, write a query to detect the 'bad' data, then remove
the bad data, incorporate the query into a CHECK constraint and try to
add the 'bad' data. If the CHECK constraint is bug free then 'bad'
data will be prevented from getting into the table.
I detailed an example a while back, here:
http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc
As a footnote to that, and knowing you like a Calendar table approach,
I'd replace the overlapping periods constraint with something like
this:
ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__no_overlapping_periods
CHECK (NOT EXISTS (
SELECT C1.dt
FROM EarningsHistory AS E1, Calendar AS C1
WHERE C1.dt BETWEEN E1.start_date AND IIF(E1.end_date IS NULL, NOW(),
E1.end_date)
GROUP BY C1.dt
HAVING COUNT(*) > 1
));
Jamie.
--
Jamie Collins
<rainofsteel.NOTVA...@GETRIDOF.luminousrain.com> wrote:
> In practice, triggers have to be implemented as VBA code in Form objects in MS Access . .
> . which I find to be, well, unpleasant to say the least.
>
> That, or you go with unbound forms and have Class Modules containing all code that
> accesses tables, which in turn are instantiated as an object on any form needing to access
> the data via the object (that represents an entity, which in turn is represented by a
> table) and have any required "additional logic" controlling the table be inside the Class
> Module along with the rest of the data access code.
Another approach (more proactive than triggers which are reactive) is
to provide SQL procedures to achieve the task in hand. To use the
tables in this thread, the following SQL procedure (Access/Jet ANSI-92
Query Mode syntax) adds a row to the Devlivery_Status table
incorporates the test for overlapping periods:
CREATE PROCEDURE AddDevliveryStatus (
arg_delivery_id INTEGER,
arg_status_id INTEGER,
arg_start_date DATETIME,
arg_end_date DATETIME
)
AS
INSERT INTO Devlivery_Status (delivery_id,
status_id, start_date, end_date)
SELECT DISTINCT arg_delivery_id,
arg_status_id, arg_start_date, arg_end_date
FROM Statuses AS S1
WHERE NOT EXISTS
(
SELECT *
FROM
(
SELECT T3.delivery_id, T3.status_id, T3.start_date, T3.end_date
FROM Devlivery_Status AS T3
WHERE T3.delivery_id = arg_delivery_id
UNION ALL
SELECT DISTINCT arg_delivery_id,
arg_status_id, arg_start_date, arg_end_date
FROM Statuses AS S2
) AS T1,
(
SELECT T3.delivery_id, T3.status_id, T3.start_date, T3.end_date
FROM Devlivery_Status AS T3
WHERE T3.delivery_id = arg_delivery_id
UNION ALL
SELECT DISTINCT arg_delivery_id,
arg_status_id, arg_start_date, arg_end_date
FROM Statuses AS S2
) AS T2
WHERE T1.start_date < T2.start_date
AND T2.start_date <
IIF(IIF(T1.end_date IS NULL, NOW(), T1.end_date) > IIF(T2.end_date
IS NULL, NOW(), T2.end_date), IIF(T2.end_date IS NULL, NOW(),
T2.end_date), IIF(T1.end_date IS NULL, NOW(), T1.end_date))
)
;
Now that a means to add a rows has been provided the INSERT
permissions can be revoked from the base table.
Jamie.
--
Michael Gramelspacher
jamiec...@xsmail.com says...
Are you sure this constraint works? It works until you add a second employee.
How do I make the constraint apply by employee_id, not for the table as a
whole?
Jamie Collins
> > I'd replace the overlapping periods constraint with something like
> > this:
>
> > ALTER TABLE EarningsHistory ADD
> > CONSTRAINT earnings_history__no_overlapping_periods
> > CHECK (NOT EXISTS (
> > SELECT C1.dt
> > FROM EarningsHistory AS E1, Calendar AS C1
> > WHERE C1.dt BETWEEN E1.start_date AND IIF(E1.end_date IS NULL, NOW(),
> > E1.end_date)
> > GROUP BY C1.dt
> > HAVING COUNT(*) > 1
> > ));
>
No, that's why I said "something like this" <g>.
> It works until you add a second employee.
> How do I make the constraint apply by employee_id, not for the table as a
> whole?
I suggest adding E1.employee_number to the GROUP BY clause.
Jamie.
--
Michael Gramelspacher
jamiec...@xsmail.com says...
With CurrentProject.Connection
.Execute _
"CREATE TABLE EarningsHistory" & _
" (employee_id INTEGER NOT NULL," & _
" start_date datetime NOT NULL," & _
" end_date datetime," & _
" CHECK (start_date < end_date)," & _
" UNIQUE (employee_id, start_date)," & _
" UNIQUE (employee_id, end_date)," & _
" salary DECIMAL(12,2) NOT NULL," & _
" CONSTRAINT no_overlapping_periods" & _
" CHECK (NOT EXISTS" & _
" (SELECT C1.calendar_date" & _
" FROM EarningsHistory AS E1, Calendar AS C1" & _
" WHERE C1.calendar_date BETWEEN E1.start_date" & _
" AND IIF(E1.end_date IS NULL, NOW(),E1.end_date)" & _
" GROUP BY E1.employee_id,C1.calendar_date" & _
" HAVING COUNT(*) > 1)));"
End With
and assuming there is a calendar table with column calendat_date,
will this work? Or is this not the same as your constraint?
Jamie Collins
> given this table:
>
> With CurrentProject.Connection
> .Execute _
> "CREATE TABLE EarningsHistory" & _
> " (employee_id INTEGER NOT NULL," & _
> " start_date datetime NOT NULL," & _
> " end_date datetime," & _
> " CHECK (start_date < end_date)," & _
> " UNIQUE (employee_id, start_date)," & _
> " UNIQUE (employee_id, end_date)," & _
> " salary DECIMAL(12,2) NOT NULL," & _
> " CONSTRAINT no_overlapping_periods" & _
> " CHECK (NOT EXISTS" & _
> " (SELECT C1.calendar_date" & _
> " FROM EarningsHistory AS E1, Calendar AS C1" & _
> " WHERE C1.calendar_date BETWEEN E1.start_date" & _
> " AND IIF(E1.end_date IS NULL, NOW(),E1.end_date)" & _
> " GROUP BY E1.employee_id,C1.calendar_date" & _
> " HAVING COUNT(*) > 1)));"
> End With
>
> and assuming there is a calendar table with column calendat_date,
> will this work?
I'm perplexed as to why you are asking me this but I'll play with a
straight bat and say, "I don't know. Shall we test it?"
First, note that a typical Calendar table has one row per day. If we
are using it in the EarningsHistory table constraint to test periods,
the smallest granule time for those periods must be one day (and
further note we've gone a little OT because this does not fit the OP's
case, where the smallest time granule is less than one day). I would
be happier if there were constraints to ensure this is indeed the case
e.g. the following assumes closed-closed representation of periods:
ALTER TABLE EarningsHistory ADD
CONSTRAINT start_date__granule
CHECK
(
DATEPART('H', start_date) = 0
AND DATEPART('N', start _date) = 0
AND DATEPART('S', start _date) = 0
);
ALTER TABLE EarningsHistory ADD
CONSTRAINT end_date__granule
CHECK
(
DATEPART('H', end_date) = 23
AND DATEPART('N', end_date) = 59
AND DATEPART('S', end_date) = 59
);
Next, populate the table with some base data:
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-03 00:00:00#, #1990-01-06 23:59:59#, 100)
;
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(2, #1990-01-03 00:00:00#, NULL, 200)
;
The way I see it, the row where employee_id = 1 would be overlapped
by
1) a row with an end_date after its start date
2) a row with a start_date before its end date
Similarly, the row where employee_id = 2 would be overlapped by
1) a row with an end_date after its start date
2) a row with a start_date before the current timestamp.
Therefore, if the constraint is doing its job, the following additions
should cause the constraint to bite:
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-01 00:00:00#, #1990-01-04 23:59:59#, 30)
;
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-05 00:00:00#, #1990-01-08 23:59:59#, 300)
;
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(2, #1990-01-01 00:00:00#, #1990-01-04 23:59:59#, 20)
;
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(2, #1990-01-05 00:00:00#, #1990-01-08 23:59:59#, 600)
;
In my testing (VBA code below), the CHECK does indeed bite for each.
However, to be working correctly, the CHECK should allow non-
overlapping data to be added. To this end, the following are
contiguous periods which should NOT cause the CHECK to bite:
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-01 00:00:00#, #1990-01-02 23:59:59#, 15);
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-07 00:00:00#, #1990-01-08 23:59:59#, 115);
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(2, #1990-01-01 00:00:00#, #1990-01-02 23:59:59#, 25);
Again, this works for me as expected.
Here's the full VBA. As written, you have to manually step over the
expected INSERT failures. Note that because the current timestamp is
beyond the limits of the very limited example Calendar table, I've
replaced NOW() with a lookup of the largest date in the Calendar
table:
Sub TestCon()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = _
"CREATE TABLE Calendar (calendar_date" & _
" DATETIME NOT NULL PRIMARY KEY);"
.Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-01 00:00:00#);"
.Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-02 00:00:00#);"
.Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-03 00:00:00#);"
.Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-04 00:00:00#);"
.Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-05 00:00:00#);"
.Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-06 00:00:00#);"
.Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-07 00:00:00#);"
.Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-08 00:00:00#);"
.Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-09 00:00:00#);"
.Execute Sql
Sql = _
"CREATE TABLE EarningsHistory (employee_id" & _
" INTEGER NOT NULL, start_date" & _
" datetime NOT NULL, end_date datetime," & _
" CHECK (start_date < end_date)," & _
" UNIQUE (employee_id, start_date)," & _
" UNIQUE (employee_id, end_date)," & _
" salary DECIMAL(12,2) NOT NULL," & _
" CONSTRAINT no_overlapping_periods" & _
" CHECK (NOT EXISTS (SELECT C1.calendar_date" & _
" FROM EarningsHistory AS E1, Calendar" & _
" AS C1 WHERE C1.calendar_date" & _
" BETWEEN E1.start_date AND IIF(E1.end_date" & _
" IS NULL, (SELECT MAX(C2.calendar_date)" & _
" FROM Calendar AS C2) ,E1.end_date)" & _
" GROUP BY E1.employee_id,C1.calendar_date" & _
" HAVING COUNT(*) > 1)));"
.Execute Sql
Sql = _
"ALTER TABLE EarningsHistory ADD" & _
" CONSTRAINT start_date__granule" & _
" CHECK (DATEPART('H', start_date)" & _
" = 0 AND DATEPART('N', start_date)" & _
" = 0 AND DATEPART('S', start_date)" & _
" = 0)"
.Execute Sql
Sql = _
"ALTER TABLE EarningsHistory ADD" & _
" CONSTRAINT end_date__granule" & _
" CHECK (DATEPART('H', end_date)" & _
" = 23 AND DATEPART('N', end_date)" & _
" = 59 AND DATEPART('S', end_date)" & _
" = 59)"
.Execute Sql
' Starting point:
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-03 00:00:00#," & _
" #1990-01-06 23:59:59#, 100);"
.Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (2, #1990-01-03 00:00:00#," & _
" NULL, 200);"
.Execute Sql
' The following rows are overlaps and
' should cause the CHECK to bite:
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-01 00:00:00#," & _
" #1990-01-04 23:59:59#, 30);"
.Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-05 00:00:00#," & _
" #1990-01-08 23:59:59#, 300);"
.Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (2, #1990-01-01 00:00:00#," & _
" #1990-01-04 23:59:59#, 20);"
.Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (2, #1990-01-05 00:00:00#," & _
" #1990-01-08 23:59:59#, 600);"
.Execute Sql
' The following rows are continguous
' (the closest legal thing to an overlap)
' and should NOT cause the CHECK to bite:
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-01 00:00:00#," & _
" #1990-01-02 23:59:59#, 15);"
.Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-07 00:00:00#," & _
" #1990-01-08 23:59:59#, 115);"
.Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (2, #1990-01-01 00:00:00#," & _
" #1990-01-02 23:59:59#, 25);"
.Execute Sql
End With
Set .ActiveConnection = Nothing
End With
End Sub
Jamie.
--
Michael Gramelspacher
jamiec...@xsmail.com says...
the clarification.