Count problem

[Oscar_W]

I have one table which contains the following fields:
StartDate, Unit, Location, Operation.

I want to run 2 checks:
1. To show if a Unit is in two different locations on the same day ?

Eg.
23 May 07, Ship A, Jetty B, Fuelling
23 May 07, Ship A, Jetty B, Water - Is permitted.

23 May 07, Ship A, Jetty B, Fuelling
23 May 07, Ship A, Jetty C, Water - Is Not.

2. To show if more than one different Unit is in the same Location on the same day.

Eg.
23 May 07, Ship A, Jetty B, Fuelling
23 May 07, Ship A, Jetty B, Water - Is permitted.

23 May 07, Ship A, Jetty B, Fuelling
23 May 07, Ship B, Jetty B, Water - Is Not.

I have tried 'count' but it get confused because Units are allowed to be in the same location twice on the same day.

Any help welcome. Thanks.

 

[llkhoutx]

You should only query StarrtDate, Unit, Location and CounterColumn:Unit, grouped with CounterColumn:Unit being counted.

Note that I;'ve added the copunterColimn and have not included.

Do not include Operation in your counting query as it is unique for each unit on each date, and hence will not group for the counting you want.

 

[Oscar_W]

Thanks very much for your quick reply but I regret that I really don't understand what you mean. I couldn't trouble you for a quick & dirty SQL text could I ? Thanks

 

[llkhoutx]

SELECT [YourTableName].StartDate,
[YourTableName].Unit,
[YourTableName].Location,
Count([YourTableName].Unit) AS CounterColumn
FROM [YourTableName]
GROUP BY [YourTableName].StartDate,
[YourTableName].Unit,
[YourTableName].Location;

Not that your postings have "Starrtdate" which I've presumed to be mispelled.

 

[Oscar_W]

I have managed to make a little progress on this but not much.

I have written two queries: I have to admit that I don’t fully understand how they work but the first one below narrows the data down such that if a ‘Unit’ is listed more than once it must be in more than one location.

Qry_Unit_Two_Locations1

SELECT Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Qry_All_Ops.Location
FROM Qry_All_Ops
GROUP BY Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Qry_All_Ops.Location;

This query is based on the one above and just identifies the name of the ‘Unit’ that is in more than one place.

Qry_Unit_Two_Locations2

SELECT [Qry_Unit_Two_Locations 1].Unit, [Qry_Unit_Two_Locations 1].StartDate, [Qry_Unit_Two_Locations 1].Location
FROM [Qry_Unit_Two_Locations 1]
WHERE ((([Qry_Unit_Two_Locations 1].Unit) In (SELECT [Unit] FROM [Qry_Unit_Two_Locations 1] As Tmp GROUP BY [Unit] HAVING Count(*)>1 )))
ORDER BY [Qry_Unit_Two_Locations 1].Unit;

How do I modify this to only indicate a problem if a ‘Unit’ is in two places on the same StartDate. It is not a problem being in two places on different StartDates.

Thanks, Oscar

 

[llkhoutx]

SELECT Qry_All_Ops.StartDate, 
Qry_All_Ops.Unit, 
Qry_All_Ops.Location, 
Count(Qry_All_Ops.Unit) AS [Count]
FROM 
Qry_All_Ops
GROUP BY 
Qry_All_Ops.StartDate, 
Qry_All_Ops.Unit, 
Qry_All_Ops.Location
HAVING (((Count(Qry_All_Ops.Unit))>1));

If you have multiple rows for the same StartDate because of multiple "Locations" for the same StartDate and Unit use

SELECT Qry_All_Ops.StartDate, 
Qry_All_Ops.Unit, 
Count(Qry_All_Ops.Unit) AS [Count]
FROM 
Qry_All_Ops
GROUP BY 
Qry_All_Ops.StartDate, 
Qry_All_Ops.Unit
HAVING (((Count(Qry_All_Ops.Unit))>1));