Intercalate Sequence-help! :)

[mfaqueiroz]

Hi!
I have a lot of registers for machines status that could be "off" and "on".
However due to data error sometimes I have something like that:

Machine Status
A Off
B Off
A On
A on
B on
B off
A off
B of
A on
B off


I want something like that


Machine Status
A Off
A on
A off
A on
B Off
B on
B off
B on
B off




Do you have any suggestion how can i do that?


Thank Youuu

 

[Ranman256]

You would sort on:
Machine, time stamp, status

 

[sneuberg]

What do you mean by register?
What are A and B ;field names, Labels?
What is going on to change their status?
What is going on to change their status incorrectly?

 

[mfaqueiroz]

Hi Rannam Sorry, i will try to explain better:

Register is an input.
A and B are the name of machines.
I don't want to change nothing only to put the status in right sequence:
off follow by one on
off on off on....

But sometimes due to wrong data I have this:
off on on on off off on off on off
I want to delete this wrong inputs to get the right order:
off on off on ...

 

[sneuberg]

You say

I don't want to change nothing only to put the status in right sequence.

but then

I want to delete this wrong inputs to get the right order:

So what do want to do change the order somehow or delete records? And what field is determining order?

It would appear you are saying that two records with the same machine and status in a row is an error and you want to delete the extra records, but you initial post shows something else. I am still very confused?

 

[mfaqueiroz]

Sorry...!!! I will try to be more clear as possible.
I want two things
1-Delete Extra Records
2-Put the information in order (off on off on off on)
My table have information relative to a lots of machines the metadata is:
Date, Machine, Status
Sometimes the sensor of the machine don't work in the perfect way what creates cases like this:
off on on on on on on off on off on off
I want to delete this wrong records and put the information in the right sequence
off on off on off on...
I hope i was more explicit, and i'm sorry for the confusion.
Thank you for your time.

 

[sneuberg]

Let's say for example you have

Dec 31,2015 A Off
Jan 1, 2016 A On
Jan 2, 2016 A On
Jan 3, 2016 A On
Jan 4, 2016 A Off

Which records get deleted and why? How do you know which one(s) are in error.

 

[mfaqueiroz]

Dec 31,2015 A Off
Jan 1, 2016 A On
Jan 2, 2016 A On
Jan 3, 2016 A On
Jan 4, 2016 A Of

The red ones because don't have a precendent match with one Off

 

[sneuberg]

Don't you mean

Dec 31,2015 A Off
Jan 1, 2016 A On
Jan 2, 2016 A On
Jan 3, 2016 A On
Jan 4, 2016 A Off

The last in the sequence was Off

 

[mfaqueiroz]

Hi!
I want to keep the last one, once that i'm adding daily information and the ON can appear in other data

 

[sneuberg]

A record should be delete if there is a record just below it with the same status. So how do you get the record just below. It's the first record (TOP 1) of the records with dates less than its date. If that record has the same status then it should be deleted. You can use the methods described in http://www.allenbrowne.com/subquery-01.html#TopN to identify and delete these records. It will be very helpful if your table has a single unique field like an autonumber as the subquery needs to return a single field.

If you can't get something working I'll give it a shot, but I need to poke these things with a stick sometimes so I'd want to work with your data without having to create a database. So I'd want you to upload your database or a copy of it with the table in question with representative data.

 

[mfaqueiroz]

thank you a lot for the tip! I will study that
!

 

[mfaqueiroz]

I think that i will ask you for your help . I 've tried but I'm realy stuck with that :/
there is my data:


http://we.tl/OFJR5tusbx


I really appreciate your time and help
it's really generous

 

[sneuberg]

Just wanted to let you know that I'm working on it. Please be patient. It might take me a while.

 

[The_Doc_Man]

Here is a variant thought regarding your question. WHY do you have the extraneous ON or OFF records?

No, I'm not being facetious. I have a similar problem with login/logout records where sometimes, for some reason, I miss a logout and realize that I have two logins in a row for the same person for the same workstation but at different times - which can't happen legally in my environment.

In my case, I am trying to do some session accounting, but what appears to have happened is that one of the events didn't get written correctly. What I have to do is try to figure out what happened to the session before I can dismiss the extraneous event.

So let me ask you the analogous question: When you see, for one machine, the time-sorted sequence ON OFF ON ON OFF - can you tell whether the 4th entry (ON) is extraneous or that you missed the OFF that should have followed the 3rd (ON) entry? Does it matter to whatever you are computing? Do the machines ever "hiccup" and send the extra status?

From the programming viewpoint, removing the unmatched ON is easy and has been discussed above - but from the OPERATIONAL viewpoint, is it right to remove the extra ON without doing something else first?

 

[mfaqueiroz]

Hi Doc_Mac!
Thank you for your point!
I've already reported this error to techinicals, they are trying to understand the reason beyond this errors.
However was told me to ignore this registers and only consider the Off immediatly follow by one ON.

I wish you the best luck with your case!!
!!

 

[sneuberg]

You will find my solution in the attached database which includes the following:

qryOnOffMS: The query is used in the other queries and joins the Date and MS fields together in a expression named TimeMS. TimeMS is the date multiplied by the number of millisecond in a day (86,400,000) and then MS is added. This is used to sort the records so that records with the same date can be distinguished. This results in the odd situation where the record with ID = 23 is out of order. You might want to look into this. If this is wrong and MS can't be added to the Date/Time how do you want to distinguish between two records with the same Date/Time?

qryShouldRecordsBeDeleted: This query will show you what records will be deleted by the solution qryDeleteErrorRecords so that you can decide if this solution is working the way you want. This query uses qryIDToBeDeleted


qryDeleteErrorRecords: The solution. This uses and requires qryOnOffMS. You could probably put the calculation of TimeMS in qryDeleteErrorRecords and get rid of this if you want.

 

[mfaqueiroz]

Sneuberg, thank you for your solution!!
I've learnt so much with that.
I've only one issue with that, in the qryIDtoBeDeledet appears some ID's that are right as 106.
ID Date ms Machine State
102 16-01-2015 05:32:07 260 Machine B OFF
106 16-01-2015 07:01:12 790 Machine B ON

Do you have some suggestion to solve that?

 

[sneuberg]

ID 106 is not right when viewed from the stand point of time; the way the query sorts by default.

102 1/16/2015 5:32:07 AM 260 OFF
101 1/16/2015 5:32:07 AM 620 ON
106 1/16/2015 7:01:12 AM 790 ON


Here ID 101 immediate precedes 106 not 102. 102 occured 400 milliseconds before 101. So what are you going to believe, the sequence number or time. If you can't believe the times then I guess there's not sense in recording them.

The question I'm wondering about is how that happened. How is this data entered into Access?

 

[mfaqueiroz]

Thank you! You are right !!!
This is perfect.
However, I have almost 53508 inputs and this is really slow to proccess and the access is always stucking and closing. Did you know if it is possible to do this "formula" with so many records?

I'm really thankfull for your help.