How to create a chain of custody in Access

[Desstro]

I know on a basic level how I want to set up this chain of custody. In a nut shell here is the break down....

I receive work package 12345 and I track that I have indeed received it through a form created from a table in access.

I then schedule the work package to be completed by a crew.

I then give this crews boss the work package to distribute to the crew.

The boss gives it to that crews lead.

Lead gives it to the crew.

Crew does the work.

Package comes back to our data entry clerk to be posted for completion.

This happens on a much more massive scale, everyday about a billion times a day...ok maybe not a billion but close.

The tracking of these work packages is at best....lousy.

Here is what I want to do...

After I schedule the work package 12345, I would like to be able to copy just that specific data from my "received" table to the boss' table and have the "boss" be able to move it from his table to the leads table, and so on. I would like to be able to do this using forms and preferably with only the click of a button by each end user. Also it would be a plus if once it was moved from one table to another, it was removed from the previous.

Please help.....

P.S. I work for the cheapest oil company in the world and am running with MS Access 2003.

[Jim Doherty]

@Desstro
Hi Desstro,

Maybe the cheapest oil equates to the cheapest database compared against SQL Server or Oracle or whatever other enterprise level database they might be inclined to give you :)

Seriously though, it seems to me the issue here is who sees what based on certain logic? Generally one does not need to throw data back and forward to different tables necessarily. Logic can be set whereby certain flags in the data can made (ie fields created) and queries used to query that data based on those flags instead. Its difficult to be concise with this without knowing what you currently have specifically.

Are you in a postion to upload an empty copy of your database as a ZIP file so maybe our team can look at it? secondly the 'billions' or nearto thing whilst said in joke can you be more explicit on that on potential datasize?

Welcome to Bytes :)

[NeoPa]

Jim has the right of this I suspect.

Depending on how much tracking (logging) you require you need a single table, with a status flag to indicate the current status of the package. A separate transaction table is probably also required to log each change of status. This may include identifying the date/time as well as the operative involved. Various forms, or a single form, can be used to enter these changes of status.

If the volumes are truly large, it may be worth considering some sort of automation for entering the data, such as bar-coding the items, as well as all the operatives.

Good luck with your project :)

[Desstro]

Thanks guys for responding.

Jim,
I do not have a data base I can upload, I am still working on creating it from scratch. I did however figure out how I want to keep track of who has what. I do now have another question...
I want to put a command button on a form that when clicked removes the data from its original table and archives it into another table. Any Suggestions?

NeoPa,

The discussion has been suggested to bar code, but it would mean bar coding paper packages that go out into the most extreme weather conditions in Prudhoe Bay Alaska's oil field. A lot of times when I get these work packages back they look like they have been through a hurricane and then shoved into a barrel of oil. But thanks for the suggestion and reply.

[Jim Doherty]

@Desstro
I will shortly create a little sample database that you can look at and rip apart. I have no idea of your fieldnames or your own design but at least you can interpet method my offering to see if it fits your design concept. I get the idea from your original post. It will be very simple include relevant tables,queries,forms, as I see it, even if the fields do not match at least you should get some idea of different approaches. I will also SPLIT this database frontend and backend so that table data is in one database and forms and queries in another database (we can thread about this later if you are not familiar)

You are asking in your LAST post to (using a button) take all data record from one table (A) and place in another table (B) and then delete all from table (A). This is very easy to do and I will also include this method in the sample I will create for you. I will upload it shortly as an attachment for you to download so watch this space :)

[NeoPa]

NeoPa,

The discussion has been suggested to bar code, but it would mean bar coding paper packages that go out into the most extreme weather conditions in Prudhoe Bay Alaska's oil field. A lot of times when I get these work packages back they look like they have been through a hurricane and then shoved into a barrel of oil. But thanks for the suggestion and reply.

I simply suggested it was worth considering. It seems you've done this already and your reasoning makes perfect sense :)

[Jim Doherty]

@Desstro

OK... attached is the bare bones of a sample for you to look at. Create a folder and dump the extracted files into it.

The database is 'split' and relies on linked tables from the backend database to the frontend database called desstro.mdb. Open desstro.mdb and form frmMainMenu. You will see a display that will reflects the location of the backend database. Just Click the filepath to change the backend location to where you have stored the desstro_data.mdb file. The code will pickup on it and refresh accordingly. If you dont know the concept of splitting a db then research that on here, it is an important aspect and has advantages.

Obviously I cannot follow any data design model you have there, but one hopes you can pick up on some techniques like populating dropdowns with notinlist events and adding items not currently in the list, and so on, and other such material to help you along your way. This sample is in Access 2000 and coding reflects that. If you have Access 2003 you will have increased functionality over 2000 in certain areas but the general principles are the same.

The append and delete queries I put in there are not in fact used by the sample as it is done in SQL code behind the forms. I merely put them in there in case you wanted to do stuff manually whilst playing with it.

Good luck with your project! :)