Comparing Tables 02-22-10

I have looked at the postings for this but don't quite understand.
I have an excel doc that is now a table used in DB.
There are several fields, Carrier, Client_Name, Appeals_Phone, Fax, 
Comp_Add2, City, State, Zip
I get a revised table every quarter and would like to compare the old and 
new and see what changes there are.
For example Carrier 1234, Client Sesame Street, Appeals_Phone 999-999-9999, 
Fax 123-456-7890, Comp Ses St, Add2 1234 Big Bird Lane, Yardley, PA, 12346
Now a new list comes in and the address is different 4567 Big Bird Lane.
How do I run a query against the 2 tables to find out what the differences 
are and make the change to the original table.  Also find out what is not 
listed any longer on the new table from the old.
Please let me know if you need more info to assist.
Thanks
0
Utf
2/22/2010 4:35:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
585 Views

Similar Articles

[PageSpeed] 21

Is there a field in the data that you get quarterly that could be considered 
the primary key or at least unique? For example is the Carrier field unique?

If so you could run a Find Unmatched Query Wizard to find records no longer 
in the table. 

You could also run a Find Duplicates Query Wizard to find records that are 
duplicates, then use that result as a subquery to leave just the different 
records.

However if there is no primary key candidate field OR the data in the 
primary key can changes, such as a company name, the results might not be all 
that good.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"LG" wrote:

> I have looked at the postings for this but don't quite understand.
> I have an excel doc that is now a table used in DB.
> There are several fields, Carrier, Client_Name, Appeals_Phone, Fax, 
> Comp_Add2, City, State, Zip
> I get a revised table every quarter and would like to compare the old and 
> new and see what changes there are.
> For example Carrier 1234, Client Sesame Street, Appeals_Phone 999-999-9999, 
> Fax 123-456-7890, Comp Ses St, Add2 1234 Big Bird Lane, Yardley, PA, 12346
> Now a new list comes in and the address is different 4567 Big Bird Lane.
> How do I run a query against the 2 tables to find out what the differences 
> are and make the change to the original table.  Also find out what is not 
> listed any longer on the new table from the old.
> Please let me know if you need more info to assist.
> Thanks
0
Utf
2/22/2010 6:32:01 PM
The first thing is to determine what field or combination of fields uniquely
identifies each record, i.e. what is its 'candidate key'.  Its important that
this value or values will be the same in both the old and new tables as
otherwise there is no way of joining them to determine if there have been any
changes.

Lets assume for this example that Client_Name is the key.

You say you want to find out the differences and make the changes to the
original table, but why not simply replace the original record with the new
one?  If there have been any changes these will then be the values in the
record; if not the old values will simply be replaced with the same values.

So, first delete all records from the old table which have matches in the new
one:

DELETE *
FROM OldTable
WHERE EXISTS
   (SELECT *
    FROM NewTable
    WHERE NewTable.Client_Name = OldTable.Client_Name);

Next insert the rows from the new table into the old table:

INSERT INTO OldTable
SELECT *
FROM NewTable;

To find out which rows exist in the old table but not in the new table:

SELECT OldTable.*
FROM OldTable LEFT JOIN NewTable
ON OldTable.Client_Name = NewTable.Client_Name
WHERE NewTable.Client_Name IS NULL;

If you want to delete those rows:

DELETE *
FROM OldTable
WHERE NOT EXISTS
   (SELECT *
    FROM NewTable
    WHERE NewTable.Client_Name = OldTable.Client_Name);

It goes without saying that before undertaking set operations like this its
imperative to back up the old table.

Ken Sheridan
Stafford, England

LG wrote:
>I have looked at the postings for this but don't quite understand.
>I have an excel doc that is now a table used in DB.
>There are several fields, Carrier, Client_Name, Appeals_Phone, Fax, 
>Comp_Add2, City, State, Zip
>I get a revised table every quarter and would like to compare the old and 
>new and see what changes there are.
>For example Carrier 1234, Client Sesame Street, Appeals_Phone 999-999-9999, 
>Fax 123-456-7890, Comp Ses St, Add2 1234 Big Bird Lane, Yardley, PA, 12346
>Now a new list comes in and the address is different 4567 Big Bird Lane.
>How do I run a query against the 2 tables to find out what the differences 
>are and make the change to the original table.  Also find out what is not 
>listed any longer on the new table from the old.
>Please let me know if you need more info to assist.
>Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201002/1

0
KenSheridan
2/22/2010 6:55:00 PM
Reply:

Similar Artilces:

Workflow Rule Issue 02-21-04
Hello I tried creating a simple workflow rule that would email two internal managers when an opportunity was created (using the new opportunity created email template supplied). Unfortunately, when an email is sent, it is sent to the customer (presumably the primary contact of the account) as well. Is there anyway I can stop this? The issue is when I select an email template in workflow manager, it greys out the To: list Thanks for any help! These templates will not work against an internal contact. All you can do is send an email to these interal people saying an oportunity was create...

Workflow in French Version GP.10. someone test it ?
Hi, I want to know if someone try some tests with French Version, in purchasing with a PO approval and workflow ? -- Eric Is there a version of GP V10 in french? I heard that was not going to happen. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Eric" wrote: > Hi, > I want to know if someone try some tests with French Version, in purchasing > with a PO approval and workflow ? > -- > > Eric Yes it is ! f...

Comparing Data
I would 1st like to say thank you for anyone who reads this as what I am currently doing is taking way to long and I'm sure there is a better way of speeding this up. This is what I'm trying to do. I have a workbook that contains 2 worksheets (lets say 1 of them is called sheet1 and the other is sheet2) and I'm looking at sheet1 to see if the same exact invoice number is on sheet2. I would like to know if there is a forumula that will automatically look for the same invoice number in sheet2 and display a true/false answer in sheet1 to let me know whether or not the same i...

how do I compare data in Exel spreadsheets?
I have two workbooks. I need a formula that will: Look for a specific reference in a selection of cells, check to see if there is a value in another selectoin of cells that is connected to the first selection, if there is a value in the second selection, return THAT value into a specific cell. Something like: Look at worksheet b, If X = X then look at column n, copy value y from column n into column z of worksheet a. Try using the VLOOKUP function. "tee" wrote: > I have two workbooks. I need a formula that will: Look for a specific > reference in a selection of cells...

excel 2007 01-31-10
Hi all Is it possible for an excel work book created by microsoft office xp professional version 2002 to be compatable with office 2007? Thanks for looking Excel 2007 can open workbooks made in earlier versions The title bar with display file name followed by "Compatibility Mode" Excel 2003 (and earlier?) can open Excel 2007 files if the users has installed the utility supplied by Microsoft. http://www.microsoft.com/downloads/details.aspx?FamilyId=941B3470-3AE9-4AEE-8F43-C6BB74CD1466&displaylang=en Of course, new features (example: the COUNTIFS function) cannot ...

xml parser error: xml document must have a top level element 09-22-05
hi, when deleting an invoice or order i receive the following error message: "xml parser error: xml document must have a top level element" when running sql profiler trace during the procedure, i can see that the following t-sql-statement: update Invoice SET ModifiedBy=N'{1D3ACF63-3D76-49AE-AC90-A6FEB6113545}',ModifiedOn=getutcdate(),DeletionStateCode=2 Where InvoiceId='{45280801-F8C6-4F7E-BC01-DBF3F8FDF701}' and then IF @@TRANCOUNT > 0 ROLLBACK TRAN to me it looks as if the transaction is rollbacked hereby (due to the xml parser error), but i can not u...

Relationships 03-14-10
New to Access and using version 2007. I am building a database covering the membership of a boating club. Looking at tables that would include: * Primary Members Data - all members in the Club - type of membership, year joined/left and similar. One record per member (so spouses/partnerships have two line items) * Members Contact Data - address and similar - again one line item per member * Members Boats Names and specifics * Members history with the Club - membership class per year for past 25 years And so on. My question is the confusion of setting up the relation...

Pivot Table
In a Excel Pivot Table, when we double-click a cell in the Data area, a new sheet is created with the details. What I want to do (I already did it in the past), is when I click a cell in the Row area, several sheets are created (one per value). I have already had this feature in Excel in the past (I think it was an add-on), but I don't remember how to do it again. Thxs,, Pedro Valente I think you mean from the Page field, not the Row field. From the PT Toolbar>dropdown>Show Pages -- Regards Roger Govier "Valente" <pedro.valente.geral@gmail.com> wrote in messag...

Pivot table grouping problem
I have a pivot table that will not group numbers in a column. ie: Year Group 2004 2005 10 2 10 1 11 5 11 3 12 8 12 10 Why won't these rows combine and look like: Year Group 2004 2005 10 1 2 11 3 5 12 10 8 All other columns group fine when I try them, but the group column does not. Any ideas? Sheryl Check for leading/trailing spaces. Make sure each is numeric ...

VLookUp Question 02-06-10
Hi is there any quick way of knowing what columns hold what information while doing a vlookup - I mean the index number? For example, in a small array of data there is no need as I can see quite clearly all the columns and know how many index numbers are visible but what if my array consisted of a huge amount of columns do I need to keep scrolling back and forth??? Many thanks -- Kind regards Ann Shaw Hi Annie, I don't know if the following will help or if you will find it too much of a hassle. if the Table array starts in column A then you could nest the Column ...

Pivot Table Item Translation
Hello All, I am having a little problem with my Pivot Table... My PT shows custom names in the ROWS section and there spends over the last 12 months as the Data However I overtyped some of the Customer names just to see what would happen and now I can not get the original names back. my data still shows the original names but the PT only shows the ones i overtyped (so i have a lot of customer name which follow the 'sdsd', 'ffee' 'fred bloggs' pattern) even when i refresh the names i entered still return. It would appear that excel is translating the actual name for ...

Subform displaying all 24158 records in Products Table
I created a form/subform using the form wizard. This was based on 2 queries. 1 for the Invoice Summary and 1 for the Invoice Detail. When you select the (open the form to view or enter information option) the subform has 24158 records. All have the same Product Number but the descriptions represent every record in the Products table. All 24158 of them. The "link child fields" and "link master fields" properties are blank in the respective tables. I am obviously doing something drastically wrong. Please help. Thanks Mark You need to set the Invoice Number as the Li...

GP 10 Security Task
What is the reason the DEFAULTUSER security task ID in version 10 has access to so many security operations in each module? I know most of them are lookups and inquiries, but is there anything that shows what the exact security operation allows? I have a customer that wants to restrict users from modifying their home page, user preferences, etc. By removing any of the security operations on the DEFAULTUSER will that cause any other problems with other security tasks? Thanks. I could send an Excel spreadsheet that shows all the Operations that task has. There are 345 default opera...

Set default path for doc compare?
This might apply to other Word tasks, but right now I need to compare pairs of documents in different folders. The path has 7 nodes on a network drive, and the default is to show "My Documents", so it wastes time to keep specifying the path. Every so often the system seems to remember the path that I need (don't know why this would be intermittent), but how do I set it as a default? -- Christopher Brewster Lockheed Martin, Eagan MN Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-word-general/200912/1 Addendum to my note: I used Tools&...

GP6.0 Tables
The BM00101 table is primarily used for BillOfMaterials. Is there a table similarly used for Kits? Thanks IV00104 is the Kit Master Table. Richard L. Whaley Author / Consultant http://www.AccoladePublications.com btw....there is considerable information on tables and their relationships and how data moves through them in our book Information Flow and Posting. Check it out on our web site. "JDR" wrote: > The BM00101 table is primarily used for BillOfMaterials. Is there a table > similarly used for Kits? > > Thanks > > > ...

How to write ten to the second power(10 square).
I am writing a report that requires this. Use superscript text. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "ollie" <ollie@discussions.microsoft.com> wrote in message news:7587BCB7-E72D-4B1A-8428-BA8EC4086959@microsoft.com... >I am writing a report that requires this. WORD 2007 You need to superscript the 2 (highlight the 2 / Home / Font group / click on arrow in lower right hand corner / Font window should launch / Font tab / place a tick (check) in the Superscript box and hit OK...

Pivot Table? #3
Excel 2000 ... No answer to previous post (11/04) so will attempt to clarify & try again today ... I have several columns of data ... For 1 Col I wish to express a "sum" on the Pivot Table ... I am new to Pivot Tables, but I have no problem with this. For a 2nd Col ... I wish the Pivot Table to express (the total sum) as a percentage of the other Col. Assume "sum" of Col A = 120 & "sum" of Col B = 30. I would like Pivot Table to return the following values: Col A = 120 Col B = 25% Is it possible to setup Pivot Table to do this ... & if so .....

Email accounts #10
I just got an Office 2003. I have set up my account to receive/send mail (mail client) from my mail server. I run MS XP Professional as my OS. It is a home computer (for the family) so there are different log-ins. I am set up as the administrator. In my access I was able to set-up Outlook for my email. When I log in my wife's account (on computer) and launched Outlook (email), it prompted me to put in the user name and initials (by default it was set up with her log-in). On that note should I put in my user name and initial since the software is licensd in my name or should I use...

Difference In An Excel Pivot Table
I am trying to create a difference column in an Excel Spreadsheet that contains a Pivot Table. The Table is based on data marked in a spreadsheet identifying each record as pertaining to a policy year of 2005/2006 or 2004/2005. I want to get the difference between the subtotal for each of these years. I have tried using the formula option on the pivot table wizard menu bar. However, when I try to pick the fields only one field is listed which is named "Year" not one for each individual policy year. I am attaching the spreadsheet in hopes that someone can help. I can easily do ...

what is the perl table ??? and how i can use this
...

email problem 08-14-10
I had my computer worked on and they reconfigured everything, supposedly putting everything back as it originally was. Since then, I can not download email except when I shut down Windows Mail, leave it shut down for a while and then reopen it. When I hit send/receive while it is open, I get a message that an error has occurred and to check the error box but nothing is listed in the box. It is blank. I have a bellsouth.net address. I contacted AT&T and they gave me new settings to try but that was no help. Any suggestions or support would be appreciated. BTW, I have AV...

GP 10 GL reconciliation to AP
I tried to use the GL reconcil feature for accounts payable, but for some reason it isn't giving me the correct AP aging amount. Is there setup that I missed? Michelle, Has SP1 which includes the Feature Pack been installed? The reconciliation tools will not work correctly until they are. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com Thank you very much. We have SP1 in our test enviornment as we speak! "Frank Hamelly, MCP-GP, MCT, MVP" wrote: > Michelle, > > Has SP1 which includes the Feature Pack been installed? The > reco...

If statement 02-03-10
I need a formula that will help me produce the correct answer in this scenario: If Revised forecast is <0 then Forecast - Actual, if Revised Forecast is >0 then Revised Forecast - Actual Forecast 360 Revised Forecast 600 Actual 10 Variance 590 This formula is not working - =IF(D13<"0",SUM(D4-D22),IF(D13>"0",SUM(D13-D22))) =IF(D13<0,D4-D22,IF(D13>0,D13-D22,"")) "Donna" wrote: > I need a formula that will help me produce the correct answer in this > scenario: If Revised forecast is <0 then ...

pivot table, vba or ???
Hi, Thank you in advance for reading my post. I am trying to assign random dates to fiscal week, month and years so that I can produce a pivot table that drills down by fiscal dates. As of now, the random dates in one spreadsheet. The fiscal information is in another. I know how to make a simple pivot table with from one spreasheet. Do I even need to populate the random dates with their corresponding fiscal information into one spreadsheet before I can make this pivot table or is there a way to connect the two? Must this task involve VBA? I am sure this is a common task, but ...

How do I combine Field1 + Field2 into Field3 in a table?
I want to combine information from Field1 and Field2 into Field3 in a table, not a query. The third field would be a Primary Key. I know it's not necessarily advisable, but this info as a Primary Key is essential. Can anyone help me? Don't! If you want Field1 + Field2 to be a (composite, multi-field) primary key, do that in the table definition by selecting both fields and designating them (combined) as the primary key. Don't create a new (third) field just to hold the two fields repeated. Regards Jeff Boyce Microsoft Office/Access MVP "riotgear" <riotge...