Assign a unique identifier to a group... (1 Viewer)

[bmhuettinger]

Good morning all,
I'll do my best to explain the issue...
We have manufacturing machine that exports a lot of data about how long it takes to complete each "pass" and the speed of the machine at any given interval when running coils of material. If I limit the time increment to only minutes and seconds, each pass could have 200-300 records before moving on to the next pass. Unfortunately, the one data point that isn't exported (or available) is a unique identifier for each coil. I need a query that looks for sequential patterns in the pass number, and after each coil has gone through all 5 passes (around 1500 records), it assigns the next group of data a "coil number" and starts the count all over again. In my test table, a 24-hour period, I have 9000 records that equates to approximately 8 unique coils.

I hope I articulated the situation properly. And I really hope there's a solution.
Thanks in advance.

 

[plog]

Probably best to demonstrate this with data. Show us what you have and what you want.

 

[Pat Hartman]

Start by adding an autonumber to the table. You will use that to sort a query that you can examine using VBA. Without a description of the pattern you are looking for, we can't help with actual code.

 

[bmhuettinger]

Good morning - thanks for the feedback. I've attached the table as an .txt for now...if there's a different preferred format, let me know

Essentially, every time the Pass = 1 and the Speed is 0, a new group starts, regardless of how many passes there are...sometimes only 3, sometimes as many as 6 or 7. I need to count (and identify) each group, i.e Group 1, Group 2, etc...Once that has been accomplished, I'll need to analyze the speed data within each group's passes.

thank you in advance

 

[plog]

Essentially, every time the Pass = 1 and the Speed is 0

I scrolled through 200 records and never did I see Formatted Speed=0

Please provide data that is easily accessible and demonstrates your issue.

 

[bmhuettinger]

I scrolled through 200 records and never did I see Formatted Speed=0

Please provide data that is easily accessible and demonstrates your issue.

 

[bmhuettinger]

there are several rows of data where the formatted speed is 0 - the crux of the issue is that there are a lot of records within each pass (like, somewhere in the neighborhood of 300-400 on average). Is there something wrong with the format of the data?

 

[plog]

I got it and I made a database to process it for you. Here's how it works.

1. Load the file into the RawFile table.

2. Run 05_ProcessingQuery - this makes tmpTable which converts all those individual fields into a usable date/time field

3. Run 10_ProcessQuery - this makes FinalTable which determines what CoilGroup each record belongs to.

2 issues--
A. Some of your data wasn't numeric. When I imported it into tmpTable it wanted to be ShortText. I didn't look into the actual file to find those values, I just deleted the records from tmpTable and moved on. I'd validate your source data.

B. 10_ProcessingQuery takes a long time to run. It's just the nature of the beast--for every record its gotta look through so many records to know what CoilGroup that record belongs to. So its possible, but time consuming.

 

[bmhuettinger]

I got it and I made a database to process it for you. Here's how it works.

1. Load the file into the RawFile table.

2. Run 05_ProcessingQuery - this makes tmpTable which converts all those individual fields into a usable date/time field

3. Run 10_ProcessQuery - this makes FinalTable which determines what CoilGroup each record belongs to.

2 issues--
A. Some of your data wasn't numeric. When I imported it into tmpTable it wanted to be ShortText. I didn't look into the actual file to find those values, I just deleted the records from tmpTable and moved on. I'd validate your source data.

B. 10_ProcessingQuery takes a long time to run. It's just the nature of the beast--for every record its gotta look through so many records to know what CoilGroup that record belongs to. So its possible, but time consuming.

Plog,
I'm beyond grateful for your efforts. I'm running the queries now; at first blush there's an issue with the raw data (from our machine) that's creating 1st passes in the middle of rolling schedules so it errantly creates new groups when it should continue on...I may have a follow up question once I ask our engineer what the reason behind these rogue passes may be...
in the meantime, i feel like a should buy your a cupcake or car or something.....