Query calculations

[Oscar_W]

Thanks mainly to John K I have the following code from a previous post which identifies when a 'Unit' has been entered at 2 different 'JettyNumber' on the same 'StartDate'

SELECT Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Count(Qry_All_Ops.Unit) AS CountOfUnit, Sum(Qry_All_Ops.JettyNumber) AS SumOfJettyNumber, [SumOfJettyNumber]/[CountOfUnit] AS Result, [result]=CInt([result]) AS Expr1
FROM Qry_All_Ops
WHERE (((Qry_All_Ops.Operation)<>"Boat Transfer" And (Qry_All_Ops.Operation)<>"Diving"))
GROUP BY Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Qry_All_Ops.Status
HAVING (((Count(Qry_All_Ops.Unit))>1) AND ((Qry_All_Ops.Status)="approved") AND ((Sum([Qry_All_Ops].[JettyNumber])/Count([Qry_All_Ops].[Unit])=CInt(Sum([Qry_All_Ops].[JettyNumber])/Count([Qry_All_Ops].[Unit])))=0));

Unfortunately the maths bit of it highlighted below...

[SumOfJettyNumber]/[CountOfUnit]

...is not cleaver enough to find all of the occasions where a Unit has been put in the wrong place.
I think I need to use CountOfUnit x JettyNumber <> SumOfJetty. If that equation is TRUE then there is a conflict, if FALSE then there isn't.
I think I can get this to work if I can get [JettyNumber] into the same query that I already have [CountOfUnit] & [SumOfJetty].
Any clues please ?

 

[Oscar_W]

I have finally managed to get this to work but have to have one query based on another. Can anyone help me to combine these two below into one please ?

First one named Qry_Test

SELECT Qry_All_Ops.StartDate, Qry_All_Ops.Unit, First(Qry_All_Ops.JettyNumber) AS FirstOfJettyNumber, Last(Qry_All_Ops.JettyNumber) AS LastOfJettyNumber, Count(Qry_All_Ops.Unit) AS CountOfUnit, Sum(Qry_All_Ops.JettyNumber) AS SumOfJettyNumber
FROM Qry_All_Ops
WHERE (((Qry_All_Ops.Operation)<>"Diving" And (Qry_All_Ops.Operation)<>"Boat Transfer"))
GROUP BY Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Qry_All_Ops.Status
HAVING (((Count(Qry_All_Ops.Unit))>1) AND ((Qry_All_Ops.Status)="approved"));

Second one named Qry_Test2

SELECT Qry_Test.StartDate, Qry_Test.Unit, [Qry_Test]![CountOfUnit]*[Qry_Test]![FirstOfJettyNumber]<>[Qry_Test]![SumOfJettyNumber] AS Expr1
FROM Qry_Test
WHERE ((([Qry_Test]![CountOfUnit]*[Qry_Test]![FirstOfJettyNumber]<>[Qry_Test]![SumOfJettyNumber])=-1));

This runs ok but would be much neater if I could combine them. Thanks.