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

x
?
Solved

overwrite incorrect serial number w/number from other table

Posted on 2011-02-16
6
Medium Priority
?
958 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 11

Accepted Solution

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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