R
Robert5833
Good day all;
Vista Business / Access 2007 (Office 2007 Small Business)
I have the following dilemma that I could not find a previous or similar
post to resolve.
In short; I need to evaluate a series of records and based on the occurrence
of a specific value (“coordinates�), identify the records occurring between
them.
Our RS-232 barcode scanner captures data in batch form, which through its
wedge software is dumped into a text file, and I have a table linked to that
file. Below are some sample batch data, with spaces added for clarity to show
the subject field:
090804094845*DeviceID001* 1108342 *RRL!
090804094850*DeviceID001* 11022555 *RRL!
090804100100*DeviceID001* 11022560 *RRL!
090804100104*DeviceID001* 1108342 *RRL!
090804100111*DeviceID001* 1108342 *RRL!
090804100119*DeviceID001* 11022569 *RRL!
090804102156*DeviceID001* 11022598 *RRL!
090804102200*DeviceID001* 1108342 *RRL!
Through a query on the linked table, these data are then parsed and
formatted, and thanks to some help from members of this group, all of that is
working quite well.
The problem has to do with events and sequencing and how I wish to treat the
data, as outlined below:
In the example batch data there are two different categories of information;
1) a work order, 2) one or more sub tasks of a respective work order.
A work order barcode scan yields the following string:
090804094845*DeviceID001*1108342*RRL!
Where:
090804094845 – Unformatted Date/Time
DeviceID001 – Device ID
1108342 – Transaction Code of *1108*, and Work Order ID of 342
RRL! – Suffix data which is irrelevant to this issue
And Work Order “task†barcode scan yields the following string:
090804094850*DeviceID001*11022555*RRL!
Where:
090804094850 – Unformatted Date/Time
DeviceID001 – Device ID
11022555 – Transaction Code of *1102*, and Work Order “task†ID of 2555
RRL! – Suffix data which is irrelevant to this issue
The sequence and order of the different scans serves the following purpose
(when occurring serially):
First Work Order scan (1108/342): Job Start
First Work Order “task†scan (1102/2555): starting point of work
(Assumption: Tasks 2555 through 2560 completed)
Second Work Order “task†scan (1102/2560): ending point of work
Second Work Order scan (1108/342): Job End
Repeat as above for the entire stack…
The above schema was devised for the reasons that there may be many hundreds
of sub tasks to a work order and those tasks will not be completed all at the
same time or in strict sequence or order. To minimize the number of scans
required but to identify which of the tasks have been completed, we wish to
apply the logic that the presence of preceding and succeeding transaction
codes denotes completion of all tasks falling between them.
What I need to be able to do then is to read and compare a stack of string
data, and extrapolate from the occurrence of serial events: transactions;
work order / sub task x / sub task xx / work order, which tasks have been
completed and which tasks have been skipped.
If I can identify all such records as a group or recordset in this manner, I
then need to pick up the date portion of the preceding work order scan date
and insert it into the related field in the work order task(s) which were not
scanned.
Is this possible, and if so, how, and in SQL, or VBA SQL?
I am really weak in the area of variables (creating, storing, retrieving,
etc.), but it seems like I should be able to collect a piece of data from the
preceding/succeeding scan records, store them as variables, treat them as
parameters, and then take some action (i.e., insert the date) on each related
record.
Each record has a unique identifier. The basic work order number such as
1000 may itself be repeated, but it carries as a prefix the Sales Order
number (which is never repeated), and thus, as a serial number would always
be unique; Sales Order 10, Work Order 1000, unique work order number 10-1000,
Sales Order 11, Work Order 1000, unique work order number 11-1000, etc. The
same is true with Work Order tasks, which carry as a prefix the Sales Order,
Work Order serial numbers; e.g., 10-1000-100, 11-1000-100, etc.
Thank you in advance for any suggestions, examples of methods, and as always
your assistance is greatly appreciated.
Regards,
Robert
Vista Business / Access 2007 (Office 2007 Small Business)
I have the following dilemma that I could not find a previous or similar
post to resolve.
In short; I need to evaluate a series of records and based on the occurrence
of a specific value (“coordinates�), identify the records occurring between
them.
Our RS-232 barcode scanner captures data in batch form, which through its
wedge software is dumped into a text file, and I have a table linked to that
file. Below are some sample batch data, with spaces added for clarity to show
the subject field:
090804094845*DeviceID001* 1108342 *RRL!
090804094850*DeviceID001* 11022555 *RRL!
090804100100*DeviceID001* 11022560 *RRL!
090804100104*DeviceID001* 1108342 *RRL!
090804100111*DeviceID001* 1108342 *RRL!
090804100119*DeviceID001* 11022569 *RRL!
090804102156*DeviceID001* 11022598 *RRL!
090804102200*DeviceID001* 1108342 *RRL!
Through a query on the linked table, these data are then parsed and
formatted, and thanks to some help from members of this group, all of that is
working quite well.
The problem has to do with events and sequencing and how I wish to treat the
data, as outlined below:
In the example batch data there are two different categories of information;
1) a work order, 2) one or more sub tasks of a respective work order.
A work order barcode scan yields the following string:
090804094845*DeviceID001*1108342*RRL!
Where:
090804094845 – Unformatted Date/Time
DeviceID001 – Device ID
1108342 – Transaction Code of *1108*, and Work Order ID of 342
RRL! – Suffix data which is irrelevant to this issue
And Work Order “task†barcode scan yields the following string:
090804094850*DeviceID001*11022555*RRL!
Where:
090804094850 – Unformatted Date/Time
DeviceID001 – Device ID
11022555 – Transaction Code of *1102*, and Work Order “task†ID of 2555
RRL! – Suffix data which is irrelevant to this issue
The sequence and order of the different scans serves the following purpose
(when occurring serially):
First Work Order scan (1108/342): Job Start
First Work Order “task†scan (1102/2555): starting point of work
(Assumption: Tasks 2555 through 2560 completed)
Second Work Order “task†scan (1102/2560): ending point of work
Second Work Order scan (1108/342): Job End
Repeat as above for the entire stack…
The above schema was devised for the reasons that there may be many hundreds
of sub tasks to a work order and those tasks will not be completed all at the
same time or in strict sequence or order. To minimize the number of scans
required but to identify which of the tasks have been completed, we wish to
apply the logic that the presence of preceding and succeeding transaction
codes denotes completion of all tasks falling between them.
What I need to be able to do then is to read and compare a stack of string
data, and extrapolate from the occurrence of serial events: transactions;
work order / sub task x / sub task xx / work order, which tasks have been
completed and which tasks have been skipped.
If I can identify all such records as a group or recordset in this manner, I
then need to pick up the date portion of the preceding work order scan date
and insert it into the related field in the work order task(s) which were not
scanned.
Is this possible, and if so, how, and in SQL, or VBA SQL?
I am really weak in the area of variables (creating, storing, retrieving,
etc.), but it seems like I should be able to collect a piece of data from the
preceding/succeeding scan records, store them as variables, treat them as
parameters, and then take some action (i.e., insert the date) on each related
record.
Each record has a unique identifier. The basic work order number such as
1000 may itself be repeated, but it carries as a prefix the Sales Order
number (which is never repeated), and thus, as a serial number would always
be unique; Sales Order 10, Work Order 1000, unique work order number 10-1000,
Sales Order 11, Work Order 1000, unique work order number 11-1000, etc. The
same is true with Work Order tasks, which carry as a prefix the Sales Order,
Work Order serial numbers; e.g., 10-1000-100, 11-1000-100, etc.
Thank you in advance for any suggestions, examples of methods, and as always
your assistance is greatly appreciated.
Regards,
Robert