Solved

overwrite incorrect serial number w/number from other table

Posted on 2011-02-16
6
950 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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