Solved

record locking

Posted on 2014-10-27
8
121 Views
Last Modified: 2014-11-05
I have the following table structures:
Cartons:  CartonID (autoID), TrackingNumber, RANumber, DateReceived, etc...
CartonContents: ContentID (autoID), CartonID, etc...

RANumber, and sometimes even TrackingNumbers can be found on several cartons. In other words, records in table Cartons can look identical.

When it comes time to enter the contents, staff pull the boxes, several staff at the same time working the same batch of cartons.  I was planning on having them select a carton record and start entering the content data.  Then I thought it may be safer to have an unbound form that when clicking 'Add' it grabs an available CartonID.  The thing I need to guarantee about is that two staff don't try and use the same CartonID at the same time but either method seems it could be possible for users to 'clash'

Any suggestions?
0
Comment
Question by:UniqueData
  • 3
  • 3
  • 2
8 Comments
 
LVL 84
ID: 40408115
In other words, records in table Cartons can look identical.
This is somewhat troubling, since a single Record in a table really should be unique. If it's not, then either (a) you haven't really built the system correctly or (b) the Carton doesn't really need a unique table. Either way, a review of your process and business rules would be in order.

That said: If the records are identical, then why would you care if the users choose the same CartonID? It really wouldn't matter, unless you have to track specific Cartons (at which point my earlier comment would be very relevant).

Can you tell us more about your business, and what the real-world process is?
0
 
LVL 7

Author Comment

by:UniqueData
ID: 40408489
So a shipment comes in with printed rma labels. If it comes in truck freight, the shipping company assigns one tracking number to all the cartons. And if they are from the same customer, all the cartons will have the same rma number. So as far as labels, they will all be identical. So say 20 cartons come in with the same indentfiers. When it comes time to pull them and see what is inside (days later) each inspector would just grab a few of the twenty and enter details into the contents table. The contents would be tied to a carton but it doesnt matter which, as long as it hasnt been used by someone else already.

However, shipments that come in ups, fedex, and usps will usually have unique shipper tracking numbers. So when those get inspected there will be no worries about grabbing a carton id.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40408519
Doesn't the truck freight company or the original shipper put a "1 of n" number on the individual cartons along with the rma number?  How do they keep track of the pieces?  How do you fill out an insurance claim?

If push comes to shove and you really don't have anything unique on the specific carton, you can update a record to "lock" it.  Then, your form would check for the "lock" indicator and not let anyone else update the record.  When the update is complete, you have to unset the "lock".  You will also need a query to find and clear locks because invariably, locks get left on when you use this type of process.
0
 
LVL 84
ID: 40408602
The contents would be tied to a carton but it doesnt matter which, as long as it hasnt been used by someone else already.
So if you receive a skid with 20 cartons on it, you'd have twenty identical records in your Cartons table. All you care is that you grab a record for the Carton+RMANumber+TrackingNumber that is not yet being used in the CartonContents table?

I agree with Pat - it would seem odd that a specific package could not be identified. I deal with a LOT of material movement, and I've yet to see that sort of setup.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 7

Author Comment

by:UniqueData
ID: 40408620
I guess an alternative would be to have them go to a receivings table first and add an "end of receiving posting" that takes all the cartons that contain the same identifiers and makes them one record, with a qty of 20 in the Cartons table. However, the contents table would have things like 5 widgets, XL, 4 Blue thingys.  I would need to change that somehow so I know those two came in one carton so I can track how many cartons have been inspected and how many are outstanding.  But that could be a "group posting" process too so they enter all the contents of one box and post it so they all get an identifier indicating they were all one carton.

hmmm.. that may be the safest method.  Because updating a "lock" field still has the slight possibility that two users could grab the same carton id number and lock at the same exact moment, right?
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40408667
No.  Using the lock process will prevent that.  If two users read the same record and both try to lock it by updating the record, one will succeed and the second will fail with an error message from the database engine that someone has changed the record since you read it.
0
 
LVL 84
ID: 40408670
It's always possible, depending on the level of usage and such. Pat's suggestion to create a "Lock" field is something I've done in the past, and it works well enough for the most part.

If an RMANumber or TrackingNumber are unique, it might be worthwhile to consider something like this:

tRMA
-----------
ID
RMANumber
DateReceived
NumCartons
etc etc

Then associate CartonContents with tRMA, and have a field where the user could indicate which "carton" they're working on (i.e. 1 of 20, or 10 of 20, etc).
0
 
LVL 7

Author Closing Comment

by:UniqueData
ID: 40424807
I will go ahead and go this route.  Thanks for your time.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Between formula (beginning of month) 11 18
Access 2010 Run Time Not Using Custom Toolbar 7 17
Max per month 3 15
Join vs where 2 11
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now