Solved

overwrite incorrect serial number w/number from other table

Posted on 2011-02-16
6
954 Views
Last Modified: 2012-05-11
i have two tables in a database- attached sample-
form opens:
1st table is (Table A)tbl2- ( data is automatically imported in)
2nd table is (Table B)tblCopy-( numbers are scanned in later)

the numbers should match- but since tbl2 numbers are being keyed in- some serial#'s are not right-( missing numbers and letters).

i have a form to display the unmatched serial numbers in each table.
Is there anyway I can have serial numbers in tblCopy-overwrite the serial numbers in tbl2 ?
or somehow speed up process - rather than manually correct each serial number?


thank you



x-database.mdb
0
Comment
Question by:davetough
[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
  • 2
6 Comments
 
LVL 12

Expert Comment

by:telyni19
ID: 34910826
You would have to have a way to decide which numbers match, other than looking at them. Do both tables have other identifying fields? Is there enough information to specify a particular record by other fields? If so, you could write an update query to overwrite the serial numbers where the other identifying fields match.
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34910868
What would be the key that would allow you to MATCH the records to determine which records are matched to allow the updating

123     123
743     734
543     543
888     999

Filter out the matches leaves
743     734
888     999

How would I know that 999 should overwrite 888 and not 743
and 734 should overwrite 743 and not 888

sticky...

0
 

Author Comment

by:davetough
ID: 34911061
they are mistakes in beginning, middle and end of numbers- I am wondering then if there is no way?
I wonder if somehow I can select the bad numbers in table and good numbers in good table-
not sure now
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 11

Accepted Solution

by:
RgGray3 earned 500 total points
ID: 34911500
Well you can determine which records do not have a matching record in the other table

A few approaches to process are possible...  and that is for you to decide...

Suggestions
I might (stress MIGHT) follow the following logic

By setting up an outerjoin where all records from the Manually Key'd table are shown and only the matching ones from the scanned ones are shown...  you can determine which records Exist inthe manual table and don't in the scanned...  Hence they are bad...

Do the reverse... and you can determine which scanned records do not have a matching Manual record...

You can PUSH them into the other table...


What you really need to decide is HOW do YOU want to reconcile them

You could push each record into a temp table with an additional field to indicate the source...   manually match them and update the incorrect record...

Are both of these tables kept and maintained.
Do they get funneled into a different table...
Is one temporary and the other permenant?

Lots of questions before a "GOOD" answer can be provided
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34911517
If you have never made a query with an "Outer Join" let me know and I'll get into detail...   it's not really difficult to do...
0
 

Author Closing Comment

by:davetough
ID: 34913093
thanks I am going to work on this
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)

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

632 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