Table Differences

[Jimbolya]

Ok - please bear in mind that I AM NOT A SQL PROGRAMMER lol.

However, that being said I have been charged with learning enough Access to make me mildly dangerous. I am trying to compare two tables and find the differences; and, no - what the Query Wizard has to offer is not what I'm looking for. Here's what I need to do:

I have two tables. For right now they only have 1 Field. These fields contain lists of IP addresses. I have an old list and a new list. I need to be able to NOT display the similarities between the two lists. From the old list I need to know what is NOT in the new list. From the new list is need to know what is NOT in the old list. Therefore I have two results:

1. Stuff that's in the old list that is not in the new list.
2. Stuff that's in the new list that is not in the old list.
3. All items that are a match between both lists does not get displayed.

I've tried this in Excel and it was epic fail. Tried the Query Wizard in Acces and it was epic fail. The organization has decided to stay with Access because of it's ability to perform SQL operations and has a reporting function. Any help would be great; and, yes...I do know how to get to the SQL portion of the Query Design tab so if you simply have a Query for me to try type it in and I'll copy it to the SQL query design window and run it.

Cheers ~

 

[plog]

Read up on queries; specifically LEFT JOINS (http://www.w3schools.com/sql/sql_join_left.asp). It's hard to gauge skill level on here, so I'm going to talk to you like you've played with Access for a week (if you need it made simpler just tell me).

Create a new query in Design View (where you graphically bring in tables, link them, bring down fields, etc.). Bring in both your tables and link them. Right click on the line that gets created and click 'Join Properties'. In the box that pops up, select either the 2nd or 3rd option and pay attention to what it says.

Doing this creates a LEFT JOIN--selecting all the records from one table (let's call this A) and just the records that match in the other (B). Click OK and in the design view bring down both fields. Underneath the field from B, in the criteria field type 'Null'.

Run that query and you have all the values in A that are not in B. Reverse that procedure I described and you can get all values in B that are not in A.

 

[David R]

It's possible to do this in one query, but it's tricky and given your experience level, you will lose less hair (and find it easier to maintain) if you store it as three queries.

1. Make one Find Unmatched looking for things in Old that are not in New.
2. Make one Find Unmatched looking for things in new that are not in Old.
   You probably already have these lying about from your earlier attempts. You may want to rename them to something simpler, but it doesn't really matter.
   
3. Your third query is called a UNION query. You'll have to do some very minor SQL editing by hand because the Access Query Wizard doesn't do UNION for you.
   Opening Notepad may make this easier for you.
   • Open a new blank query.
   • Drag findUnmatchedOld into the design window. Add your [IP] field to the query.
   • Add a calculated field to this new query: Source: "Old"
   • In the top left, change to SQL view. Copy everything in that window to clipboard/Notepad.
   • Delete the final semicolon of that SQL, then hit return. On the blank line, put the word UNION, then hit return again.
   • On this blank line, paste your clipboard contents. Make sure there is ONLY ONE semicolon on the screen!
   • In this new, second half of the query, change all the references to 'Old' to say 'New'. That should be three places: The query name (twice), and the "oldIP" AS Source calculated field. It should look something like this, with better field names:

     SELECT [findUnmatchedOld].[IP], "oldIP" AS Source
     FROM [findUnmatchedOld]
     UNION
     SELECT [findUnmatchedNew].[IP], "newIP" AS Source
     FROM [findUnmatchedNew];

   • Run your query. Assuming all went well, you should now see both sets of data, one from your old table, one from the new, labeled helpfully.
     If it didn't go well, post back and include your UNION SQL.
   I don't know how you want to sort this data, but you can change the end of it to something like ORDER BY Source DESC; (again, on a new line, remembering to move the semicolon). You have to do it by hand from now on because Access can't represent a UNION query in Query Design view, only SQL View. That's why I suggested keeping the actual Find Unmatched code in their own stored queries, rather than rolling it all together into one megaquery by hand.

 

[Jimbolya]

Thanks you two -
I'm going to try both of these now =).

As far as experience goes I haven't used Access since my intro to Windows Apps in college about 5 years ago lol. Most of my experience is with Security and Network apps.

 

[Jimbolya]

plog -
I ran your idea two different ways and it worked just great. I'm going to test david's code and see how that falls out. So far making progress =).

Yea!!

 

[Jimbolya]

Also - once i get this all squared away I'm going to want to make a new master list...

 

[David R]

And the new Master List will contain everything from both current tables?

 

[Jimbolya]

David - Here's what i have that seems to run but it asks me for parameters when I run it?

SELECT [UnmatchedOld].[Test Table 1], "oldIP" AS Source
FROM [UnmatchedOld]
UNION
SELECT [UnmatchedNew].[Test Table 2], "newIP" AS Source
FROM [UnmatchedNew];

 

[David R]

Are the "IP" fields really named [Test Table 1] and [Test Table 2]? If so you can alias them in the Design View of the Find Unmatched Queries - the field names should match or UNION will get confused.

If not (and I suspect not, since it's acting like they're parameters), post your table design.

Something like this, or a screenshot:

tableOldIPLogging
-----------------
RecordID
IPLog
LogDateStamp

and tell us which fields are the ones you're wanting to compare, if the field names don't make it obvious.

 

[Jimbolya]

I'm thinking that keeping them in their own queries is cool. I have no problem with that due to the fact that I'm probably going to get report requests based on one or both of the criteria; so, having one query per is cool. I need to do some design work around how I want the end state to look. I'd would like to build a new master list based off of what the queries show.

For example:
Stuff in the old list that did not have a match is deleted from the old list, and stuff in the new list that did not have a match is added to the old list; and, the new data becomes the new master list...

See here's the deal - we have several sources for this data and we are working on getting them into one coordinated and accurate db. Luckily - I was the one chosen to design and build the new db lol.

Cheers ~

 

[Jimbolya]

David,

Here's the UnmatchedOld query code:

SELECT [Test Table 1].Field1, [Test Table 2].Field1
FROM [Test Table 1] LEFT JOIN [Test Table 2] ON [Test Table 1].Field1 = [Test Table 2].Field1
WHERE ((([Test Table 2].Field1) Is Null));

 

[David R]

Try this then:

SELECT [UnmatchedOld].[[B]Field1[/B]], "oldIP" AS Source
FROM [UnmatchedOld]
UNION
SELECT [UnmatchedNew].[[B]Field1[/B]], "newIP" AS Source
FROM [UnmatchedNew];

I believe you can see what went wrong.

 

[Jimbolya]

The specified field '[UnmatchedOld].[Field1]' could refer to more than one table listed in the FROM clause of your SQL statement.

 

[David R]

Oh, I see. You don't need both Field1's in the SELECT line of UnmatchedOld, because [Test Table 2].[Field1] will always be null, according to the WHERE line.

You can remove it in Design View or SQL View. make sure you do the same (but reversed) in UnmatchedNew!

 

[Jimbolya]

Lol - still didn't work. Loops on asking me for parameter type. I have to head off to the docs so going to eat quick lunch and then head out. Thanks for the help this afternoon both of you. Will be back on tomorrow...will PM when on if that's okay?

 

[David R]

Just reply back to this thread, it'll bump to the top of the screen.

I think we need to see the current SQL behind each of your three queries when you come back.

 

[Jimbolya]

Sounds good - heading out - will hit this back up when I get in tomorrow EST time zone.

 

[Steve@trop]

I've been working on the same thing and I just figured it out on my own. It's a miracle because I don't really know what I'm doing. This has worked on my test tables so far so your mileage may vary. I haven't tested it fully yet.

I have two test databases (KeepUp1 and KeepUp2) with a single test table each that have the same exact fields in them and the same name (Table_1). There is a uniqueIdentifier field (BeUnique) that is the primary key. In Access it's called a replication field, you choose the autonumber datatype and then format it as "Replication". I'm going to be syncing five databases so I definitely need the replication field. The other fields aren't important since I just want it to use them all anyway. I just used a name and number field so I can tell if the records synced or not. I put different records into each of the tables so they each have different data to sync. The first one simply inserts records that exist in one table and not the other and the second one does the same thing in the other direction.

Use KeepUp1
INSERT INTO KeepUp2.dbo.Table_1
SELECT Table_A.*
FROM [dbo].[Table_1] as Table_A
Left Join KeepUp2.dbo.Table_1 as Table_B
On Table_A.BeUnique=Table_B.BeUnique
Where Table_B.BeUnique IS NULL
 
Use KeepUp2
INSERT INTO KeepUp1.dbo.Table_1
SELECT Table_B.*
FROM [dbo].[Table_1] as Table_B
Left Join KeepUp1.dbo.Table_1 as Table_A
On Table_B.BeUnique=Table_A.BeUnique
Where Table_A.BeUnique IS NULL

I'm using this code on an SQL server and running it from the Management Studio. I don't know if it will work the same in Access but it may be worth a try. I'm going to do some more tests on copies of live tables to make sure this will work in production. One thing this doesn't address is deleted records. In my case, I don't have to worry about syncing any deletions but YOU might. With this setup, if a record is deleted from one of the databases, it will "magically" reappear if it isn't deleted from all the databases.

 

[David R]

The syntax for SQL server is going to be rather different (hence the "Use") but yeah the principles are the same.

 

[Jimbolya]

Well I've run the individual queries for Old and New on actual data and it seems fairly accurate. I like the fact that I have two separate queries running for each question individually. Management does like to look at both sides individually as well as combined; and, combining the information isn't going to be that though. Now I need to figure out how I'm going to implement this into the environment, and eventually work towards a master list that gets changed via the results of the individual queries.

Thanks Dave and Plog for the help!