Solved

overwrite incorrect serial number w/number from other table

Posted on 2011-02-16
6
949 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculate workdays and consider holidays database from microsoft 2007 8 23
Question about DB Schema 27 56
Dlookup MSACCESS 5 25
SQL Group on First occurrence 9 25
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

777 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