Find the record in the same table that follows specific requisites

[mfaqueiroz]

Hello,

I've a Totals Query grouped by:
-Day
-Hour
-Minute
-Second
-Machine

and counts the number of "Alarms".


I want to flag the cases where at in the exact time (day,hour,minute,second) i've alarms in different machines.

Day=day
minute=minute
Second=second
Machine <>Machine


Do you have ideia how can i do that efficiently?
I've replicated the table as shows the follow code, but i think that are a cleaver way to do that, do you have suggestions?

Sub Alarms()

           Dim hour As String
           Dim minute As String
           Dim Second As String
           Dim Machine As String
        
     
            Set dbs = DBEngine(0)(0)
             Set CountAlarms1 = dbs.OpenRecordset("CountAlarms1", DB_OPEN_DYNASET)
            Set CountAlarms2 = dbs.OpenRecordset("CountAlarms2", DB_OPEN_DYNASET)
     
         CountAlarms1.MoveFirst
         Do while not CountAlarms1.EOF
         Day=CountAlarms1.Fields(0)
         Hour=CountAlarms1.Fields(1)
         Minute=CountAlarms1.Fields(2)
         Second=CountAlarms1.Fields(3)
         Machine=CountAlarms1.Fields(4)

         CountAlarms2.MoveFirst
         Do while not CountAlarms2.EOF
         Day1=CountAlarms2.Fields(0)
         Hour1=CountAlarms2.Fields(1)
         Minute1=CountAlarms2.Fields(2)
         Second1=CountAlarms2.Fields(3)
         Machine1=CountAlarms2.Fields(4)

        If day1=Day and hour1=hour and minute1=minute and second1=second and machine1<>machine than
        CountAlarms1.Edit
        CountAlarms1.Fields(5)="Yes"
        CountAlarms1.Update

        End if
        CountAlarms2.MoveNext
        Loop
        CountAlarms1.MoveNext
        Loop

end sub

 

[Ranman256]

I don't see how you can count items by the second.
You will get 1 for every item.

But normally it's 1 line,
Select count(item), [timeField]

 

[mfaqueiroz]

I can count cause I also have the miliseconds, so I can have more than one alarm in the same second...

 

[Cronk]

The question is when did a fault occur in more than one machine at the same
mm/dd/yy hh:mm:ss

Then use your Totals query with the criteria that the count based on this grouping is more than one, ie
Count >1

No need for any code.

Incidentally, it would be far easier just to record the fault time in one dateTime field, including the hour/minute/second value.

 

[mfaqueiroz]

I need code, because I've also miliseconds.
So at the same second could occur more than one alarm in the same machine...
I want to flag the machines that had simultaneous alarms (ie i have one alarm in machine b and at the same time i have one alarms in the machine c)

 

[Cronk]

You did not mention milliseconds in your original post.

If you want to know when faults occurred in more than one machine in the same second, use your original query.

If you want when faults occurred in more than one machine in the same millisecond, include that field in your totals query.

If you want to achieve the same result using a more complicated way in code, I have better things to do.

 

[arnelgp]

same as mr.cronk suggestion.
use an update query to update your CountAlarms1 table:

UPDATE CountAlarms1 AS T1 INNER JOIN CountAlarms2 AS T2
ON
T1.hours = T2.hours AND
T1.minutes = T2.minutes AND
T1.seconds = T2.seconds AND
T1.milliseconds = T2.milliseconds
SET T1.fieldToUpdate = "Yes"
WHERE
T1.machine <> T2.machine;

 

[mfaqueiroz]

Arnelpg, thank you!
I've tried this code:

Sub CrossCountry()
DoCmd.RunSQL "UPDATE [CountAlarms1] AS T1 INNER JOIN [CountAlarms2] AS T2 ON T1.[Day]=T2.[Day] and T1.[Hour]=T2.[Hour] and T1.[Minute]=T2.[Minute] and T1.[Second]=T2.[Second] and T1.[machine]<>T2.[machine] SET T1.[Flag] = '1'; "
End Sub


but appears this error
"Run-time error 3073. Operation mus use an updateable query."

Do you have idea how can i solve that?

 

[arnelgp]

i overlooked your first post.
you are using aggregate query, which is not updatable.
you must do it in the table.
it will work on table, i just tested it.

 

[mfaqueiroz]

Hei arnelpg,
thanks for the reply,
I didn't get how can i do that on a table. I should do an append query to transfer the information to a table? I've tried but appears the following error " your query not include the specifieds expression" CountAlarms" as aprt of an aggreagate fuction"

 

[arnelgp]

what are your tables names and their fieldnames?