I have two tables, "Equipment" with structure (Equip_ID as text, Equip_Type as Text) and "Equipment Assign" with structure (Begin, End, Person_ID, Equip_ID) with a one-to-many relationship between Equipment and Equipment Assign.
Obviously, the same piece of equipment can be assigned to multiple people over a certain timespan.
I want to generate a query that will pull out "bad" assignment records.
That is, cases where a single piece of equipment was assigned to two (or more) different people, but there are more than one records with a blank "End" time.
So if equipment looks like:
ID Type
1 pocket
2 pocket
3 pocket

and Equipment Assign looks like:
Then the query would pull out only Persons 4 & 5, since they share teh same ID but both have a blank End time. But the query would NOT pull out Person 1 because while his End time is blank, his ID is not shared by anyone else.

I have a feeling I SHOULD be able to do this with the relationship structure I have, I just can't figure it out.

-Mike

Hi

I would use a sub query as follows :-

SELECT *
  FROM equipment_assign
 WHERE id in (
        select ea1.id
          from equipment_assign ea1 inner join
               equipment_assign ea2 on ea1.id = ea2.id
         where ea1.person_id <> ea2.person_id
           and ea1.endd is null
        group by ea1.id
        having count(ea1.id) > 1)

I hope this helps.

Regards

Nickie

Try:

The subquery selects all Equip_ID's having more than one row where the EndDate is null. The outer query then selects all Person_ID's assigned to those Equip_ID's.

You didn't ask, but isn't an equally "bad" assignment one where dates overlap, whether or not the EndDate is null? e.g:
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Aha! Thanks guys, that's exactly what I'm after.
As for the additional "bad" query, Jeff, you're right. Equipment surely cannot be held by two people simultaneously!
I'm working on baby steps right now and identified my problem as a key issue in my current "sample" data, so wanted to correct that first.
Any way to account for your example without doing some actual coding where I loop through a recordset?
-Mike

As I was writing my original reply I was thinking that the query would be pretty tough. As I think about it some more, it looks like it really is. I'll try to work on it later on. My idea would be that given any two rows where the Equip_ID is the same, there is no overlap if (Begin1<Begin2 AND End1<End2) OR (Begin1>Begin2 AND End1>End2). If this condition is FALSE, there is an overlap, and the pair should be reported.

Note that this assumes that the BeginDate is always less than the EndDate. Of course you've insured this is true in your front end, right?

It can be difficult to clean this stuff up after the fact; it's always better to insure that this sort of "bad" data never gets in there in the first place. A well-placed CHECK constraint can insure that the end date is always after the begin date, for example.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I thought about it a bit, too, after my last post. You got closer than I did in coming up something, anything at all really. Unfortunatly, I can see a case where an overlap could exist and your initial criteria still resolves to TRUE.

I have been operating under the assumption that the Begin and End dates are well-formed, yes. While I do not have the checks in place just yet, I plan on doing so.

In fact, after reading your message, I went in and tried to do just what you suggested (and what I had thought about already). To be clear, here, I'm working with MS Access, so my table definition is not SQL, it's their fancy dancy UI. In any event, for the End field, there is a property "Validation Rule". I set it to ">=[Begin]". When trying to save the table, this is the error I got:
Invalid SQL syntax - cannot use multiple columns in a column-level CHECK constraint.

So, I'm not really surprised at all that underneath their schnazzy UI they actually implement data definition SQL. HOwever, upon seeing that error, I'm wondering, once again, just how to implement this CHECK withOUT referencing the Begin column??

I'm a code geek by nature, instead of a DB geek, so my first thoughts are always to write up some quick little code. But if I can enforce this rule at the table definition layer, then that'd save me time implementing something like that. (The 2nd rule of geek, spend 20hrs developing something to save the time it would have taken you to do in 10 minutes anyway).

-Mike

You're right, my criteria is wrong.

Let me try again. There is no overlap if one row begins and ends before another starts, or begins and ends after another ends. If that is not true, then there is an overlap. So I guess the criteria should be:

(Begin1<Begin2 AND End1<Begin2) OR (Begin1>End2 AND End1>End2)

If this is FALSE, there is an overlap. There's some kind of correlated subquery that'll do this - I just haven't figured out what it is yet.

I said this was hard. :)

Note also that you'd need to deal with the possibility of the EndDate being null. In SQL Server this is best handled by replacing the EndDate expressions with:

COALESCE(EndDate, current_timestamp)

In Access I think it's something like:

IIF(ISNULL(EndDate),date(),EndDate)

Can't help you on the Access constraint issue other than to comiserate with you and to suggest you get a real database. :D

I checked the docs and an Access "validation rule" indeed cannot reference another column. Dumb Access. A SQL CHECK CONSTRAINT can. If you have Access 2000, you might consider an Access data project instead which is really SQL Server (MSDE actually).

If not, I guess you are stuck with implementing the appropriate checks at the front end.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com