| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2005-12-08 : 14:35:48
|
| Recently my organization has decided that double-entry of data is needed to enhance data integrity.Basic plan: data entry person enters first cut of data, data goes to temp table. Data entry person enters same data again, posts it to another temp table, results are compared and discrepancies flagged, after which fixes are made and data is posted to production table.Currently I use classic ASP pages to interface with SQL Server 2000. I know it's old and crusty, but I try to do most of the work on the SQL end via SPROCs.I'm wondering: rather than invest in an off-the-shelf product that provides more than we need in the way of features, and also makes more work for me since I'd have to administer it, can't I just build this in SQL Server 2000?Like this: user submits to temp table, redirect to same web form, user enters and submits same record to another temp table, clicks "Accuracy Check" button (or some such) on web page -- SPROC compares the two records and returns editable inconsistencies. Accuracy Check again. Success, new button on web form: "Commit this record to the db", done. Anyone done something like this or can refer me to links so I can learn?thanks |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2005-12-08 : 15:42:13
|
Yep, I've done this idea.What we had to accomplish was maintain two sets of labour records,One went to SQL one went to ACCESS. the real trick was the writes to SQL were starttime endtime on a code per employeeand the Access one were hours (ie. 3.75) on a code per day per employee.The end result of the report though, "your accuracy check" can only really be represented in a two table versioning report format.The left display table would show you have records in Table1 but not in Table2 AND the right display would show you have records in Table2 but not in Table1.It sounds confusing but it really isn't and as corrections are made it often removes an entry from both tables. (Maybe always, I can never remember  )You don't even really need two sql tables to do this, colx can be a partition column but it might be easier and scale simpler if all round one writes go to round1 table and round two writes go to round2 table. At least it will be easier to develop for multi dataentry people this way.The gist of the query to get this result set isSELECT A.col1, A.col2, A.col3, B.col1, B.col2, B.col3 FROM A FULL OUTER JOIN BON A.col1=B.col1 AND A.col2=B.col2WHERE(A.col1 is null AND A.col2 is null)OR(B.col1 is null AND B.col2 is null) The good Dr. will let us know if this is correct form.col1 and col2 are the things that you are checking for consistency against.for multi data entry folks I think I'd build in the applicationa session begin and/or session guid and keep that attached to each record in Table1 and Table2, This guid would be persistant somewhere,so it could be recovered by the application. (wrong it would be persistant in a little sessions table right next to the other tables with username and one or many guid's, many probably needs a name or date to remind the person which one they stopped started. I would't use cookies for this, just because it's another thing to talk to)So if the dataentry person logs of the web they can recover back what they have put in, once the check is good! delete all the stuff for that session. Sounds like a whole lot of stuff being typed in; and Windows apps have a much more advanced level of validation readily available. Depending on the data, you could have it so the person barely has to move their hand from the numeric keypad, flip paper with the left and just chicken poke the letter keys to auto find stuff in drop downs etc. Hope that makes sense. "it's definitely useless and maybe harmful". |
 |
|
|
jhermiz
3564 Posts |
Posted - 2005-12-08 : 16:46:49
|
Why are they entering the same data twice? Reports should be able to validate data. I would hate to have a job where Im entering the same thing over and over.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url] |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2005-12-08 : 16:52:25
|
| In my case it is only one spin at the entry, then it transforms and writes to two stores. I hope I didn't misunderstand the reporting problem."it's definitely useless and maybe harmful". |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-09 : 02:05:17
|
"Why are they entering the same data twice? Reports should be able to validate data. I would hate to have a job where Im entering the same thing over and over."Normally accuracy. Mind-numbingly boring data entry will result in some mistakes. For hourly-paid folk, for example, its important to get the hours entered correctly. 1.2 hours and 1.3 hours will both pass the validation, but only one is accurate.  Besides, the second entry is normally made by a different person (different interpretation of hand writing etc.), so you'll still only have to do your job once!Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-12-09 : 03:42:54
|
| What I don't quite get about this is how you could match the records to be compared. e.g. if a customers details are being double entered and one of the data entry people makes a mistake, how do you "know" to compare these records? steve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2005-12-09 : 06:27:09
|
| Or what about a repeating double entry? Did they really buy 5 widgets on Tuesday or did they buy 10. A 5 widget purchase, twice. This is an evaluation by the data entry person."it's definitely useless and maybe harmful". |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-09 : 23:49:21
|
| "how do you "know" to compare these records?"One way would be to output the "ID" number to the first inputter to write on the data entry sheet.Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-10 : 13:05:48
|
| You could use a batch and pass methodology for this. This isn't that uncommon in accounting or data entry applications. They will create a batch number for each set of customer data to be entered. The batch will start off with the batch number and pass one. When they have finished pass one, they hit a shortcut key or a pass button to start the second pass. The validation pulls both passes together with a full outer join where any side is different. They make the corrections and submit. First pass is submitted. Second pass is deleted. You can randomize the multiple pass requirements or break the passes out between multiple people if you want to even further randomize for audit and integrity reasons. You also might want to place an order id in so the same person can enter the data forward on first pass and backward on second pass. This is handy if you are actually working on papers in front of you. Anyway....have fun. I've only created something like this twice. It was fun though and did really point out a lot of errors.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-10 : 13:39:38
|
| "and did really point out a lot of errors"Indeedie.We have done something not dissimilar with Parallel Running of a new application prior to adoption.We build a Data Conversion to get the data from the Old App to the new App.We make a conversion on "Day 1", and then the data entry folk enter all data into both systems for, say, a week.Then we make another conversion from the Old System and compare against the New System.And then we laugh at the differences! Especially when management have said "Our data entry people are the best this is a huge waste of time & money" Yeah, right! At issue is:1) Sloppy data entry - hopefully not much of this, but when there is management wise up to "This is the normal rate of error in your data - you know, the data you 'just trust'"2) Data which is consistently wrong because:2a) More training needed2b) New application is doing it wrongly3) Other stuff ... which generally does not need to be worried about (interpretation of data when differences are inevitable, and permissible - such as free-form notes)Kristen |
|
|
|
|
Anyone build a double-entry web inteface to SQL?