|
| Im working on a project for class and I need help trying to update a RFID output file into a database.
Example RFID Output looks like this:
Time, ID, Room
12:45:30, 1000, 100 (First Initalization of item 1000)
12:45:45, 1000, 100 (Leaves Room 100)
12:45:50, 1000, 101 (Enters Elevator 101)
12:47:37, 1000, 501 (Leave Elevator on floor 5 at 501)
12:48:20, 1000, 500 (Enters storage room 500)
Now the file is done by the second so there is alot of just junk text like
12:41:00
12:41:01
12:41:02
12:41:03
12:41:04
12:41:05
12:41:06
Where nothing is happening so I dont want to store this in the database.
Also every time the clock hits the hour or half hour mark it needs to kick up a msgbox asking them if they want to continue parsing the file (No Problems with this)
I also have no problems just storing the data into the database and not including the junk data.
Problem occurs when I have to update the status of the items in the database.
DB looks like
ID, Time, Item, Room, Status, Travel Time
ID=Auto Number
1 12:45:30, 1000, 100 STORED
2 12:45:45, 1000, 100 TRANSIT
3 12:45:50, 1000, 101 TRANSIT
4 12:47:37, 1000, 501 TRANSIT
5 12:48:20, 1000, 500 STORED
But when the item leaves room 100 then it needs to look like
1 12:45:30, 1000, 100 LEFT/WAS_STORED
2 12:45:45, 1000, 100 TRANSIT
3 12:45:50, 1000, 101 TRANSIT
4 12:47:37, 1000, 501 TRANSIT
5 12:48:20, 1000, 500 STORED
then
1 12:45:30, 1000, 100 LEFT/WAS_STORED
2 12:45:45, 1000, 100 WAS_TRANSIT
3 12:45:50, 1000, 101 TRANSIT
4 12:47:37, 1000, 501 TRANSIT
5 12:48:20, 1000, 500 STORED
then
1 12:45:30, 1000, 100 LEFT/WAS_STORED
2 12:45:45, 1000, 100 WAS_TRANSIT
3 12:45:50, 1000, 101 WAS_TRANSIT
4 12:47:37, 1000, 501 TRANSIT
5 12:48:20, 1000, 500 STORED
then
1 12:45:30, 1000, 100 LEFT/WAS_STORED
2 12:45:45, 1000, 100 WAS_TRANSIT
3 12:45:50, 1000, 101 WAS_TRANSIT
4 12:47:37, 1000, 501 WAS_TRANSIT
5 12:48:20, 1000, 500 STORED
and then if item 1000 leave the room again
1 12:45:30, 1000, 100 LEFT/WAS_STORED
2 12:45:45, 1000, 100 WAS_TRANSIT
3 12:45:50, 1000, 101 WAS_TRANSIT
4 12:47:37, 1000, 501 WAS_TRANSIT
5 12:48:20, 1000, 500 LEFT/WAS_STORED
So that if your were looking at the database at 12:45:59 then the item should be in transit but then if you look at it at 12:45:30 the item is stored. (I have the options setup and know how to have them stop the parsing and look at database/do reports)
Im just lost on how to setup the inner loops for updating the status of the items from stored->left and so on
Heres what I got so far and it sort of works =) but not really hehe
Any help would be great
Thanks
Brad | |