Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

overwrite incorrect serial number w/number from other table

Posted on 2011-02-16
6
Medium Priority
?
963 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

926 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