Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

record locking

Posted on 2014-10-27
8
Medium Priority
?
130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 85
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 39

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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 85
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
 
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 39

Accepted Solution

by:
PatHartman earned 2000 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 85
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

662 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