Can't be in two places at once

[Oscar_W]

I have a database containing shipping details. As part of the error checking system I am trying to write a query that will identify if a ship is incorrectly recorded in two locations on the same day.

There are approx 50 ships and 60 locations all entered in one table in the following format:

Date, Ship, Location.

This is what I have got so far.

In (SELECT [Location] FROM [Ops_Log_Table] As Tmp GROUP BY [Location],[StartDate] HAVING Count(*)>1  And [StartDate] = [Ops_Log_Table].[StartDate])

This criteria finds 'Ship A' in 'Location 1' if it is entered more than once (which it is allowed to be).
What I am trying to find is 'Ship A' in 'Location 1' on the same date that 'Ship B' is there.

Thanks in advance,

Oscar

 

[CEH]

There are approx 50 ships and 60 locations all entered in one table in the following format:

Date, Ship, Location.

Sounds like your problem is right there.... You need more then one table... tblShips, tblLocations, and then a third... To record the days locations.

 

[grnzbra]

Looks like this might be the third table.

Perhaps if you move the StartDate criteria to a WHERE clause.

 

[Oscar_W]

I wasn't specific enough as usual There are several tables (Location, Ship etc..) These feed into the main table which contains all the operations.

I have modified the SQL to read as follows:

SELECT Ops_Log_Table.Location, Ops_Log_Table.StartDate, Ops_Log_Table.ReferenceNumber, Ops_Log_Table.Unit, Ops_Log_Table.Request_DTG, Ops_Log_Table.Approval_DTG, Ops_Log_Table.Operation, Ops_Log_Table.EndDate, Ops_Log_Table.Status
FROM Ops_Log_Table
WHERE (((Ops_Log_Table.Location) In (SELECT [Location] FROM [Ops_Log_Table] As Tmp GROUP BY [Location],[StartDate] HAVING Count(*)>1  And [StartDate] = [Ops_Log_Table].[StartDate])))
ORDER BY Ops_Log_Table.Location, Ops_Log_Table.StartDate;

This now identifies Locations where there are two ships on the same day, but I need to refine it to identify if these ships are two different ones (Not Allowed ) or the same ship carrying out two separate operations (Allowed ).
Any help welcome. Thanks.

 

[grnzbra]

Perhaps if you do Count(Ships) > 1 you would get only the ones where the ships are different.