Urgent help needed, difference between two tables? 1

[Ayac]

Hi all!

Do you know how to get the difference as a result of two tables? I have two calendar tables in Access (both have the same data structure), one from last week and one from today. I would like to create a query that returns only those lines what are different between the two.

 

[JayKusch]

Need to post this one in the Access forum.

Thanks

J. Kusch

 

[SQLBill]

This is the forum for Microsoft SQL Server. Your question appears to be just on MS Access. If so, you need to post it in one of the Access forums.

-SQLBill



SELECT 'forum' CASE [SQL TYPE]
WHEN 'MySQL' THEN 'forum436'
WHEN 'Access' THEN 'forum700', 'forum701', 'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'ORACLE' THEN 'forum185' 'forum186', 'forum759'

 

[SQLBill]

Ya beat me to it Jay!.

-SQLBill

 

[Ayac]

Sorry for that. I did not want to hurt anybody. I actually need it in both sides because I am moving files over to SQL. So then how would you do it in MS SQL?

 

[SQLBill]

Can you tell us the column name(s) that you would be comparing? Is there an ID column or another column that is the same in both tables? Structure of the tables would be helpfull.

-SQLBill

 

[SQLBill]

BTW-you didn't hurt anyone. It's just that when someone appears to post in the wrong forum, we like to point them in the right direction so they can get the best help.

-SQLBill

 

[kgoods]

This is what I do...

SELECT a.* INTO Temp_table FROM first_table a WHERE NOT EXISTS (SELECT b.* FROM second_table b Where a.firstfield = b.firstfield AND a.secondfield = b.secondfield .... AND a.lastfield = b.lastfield)

This assumes that both tables have identical fields (columns). What it does is create a temporary table consisting of all the records that are in the first_table that are not in the second_table. You have to include all the fields in the query so hopefully you don't have too many.

If you want to find all the records in the second_table that don't have a match in the first_table, simply change the order of the callouts before the second where clause.

e.g.
SELECT b.* INTO Temp_table_2 FROM second_table b WHERE NOT EXISTS (SELECT a.* FROM first_table a Where a.firstfield = b.firstfield AND a.secondfield = b.secondfield .... AND a.lastfield = b.lastfield)

Hope that helps,
Ken

 

[Ayac]

I have "Current_Calendar" and "Previous_Calendar"

This is the layout

ID - text
FromDateTime - datetime
TillDateTime - datetime
Title - text
CIdentifier - text
Holdback - bit
ConnectionCode - text
Details - text

I have compare these two.

 

[SQLBill]

To start you off....

Let's say I have two tables with lastname and firstname. I want to make sure the firstname is the same in both tables (for instance, I might have William Smith in one and Bill Smith in the other.)

SELECT t1.lastname, t1.firstname, t2.firstname
FROM table1 t1
JOIN table2 t2
ON t1.lastname = t2.lastname
WHERE t1.firstname <> t2.firstname

(I think that'll work).

-SQLBill

 

[Ayac]

I must have the full actual record whenever any part of it is is different from the week before one (one record can even be deleted in the second, current one). ID is a unique identifier.

 

[kgoods]

Hi Ayac,

I think what I posted is exactly what you're looking for. When you say ID is unique do you mean a duplicate record in one table could have a different ID in the other table? If so, then you would have to leave that field(column) out of the query.

Give it a shot and let me know if you can't get it. I'll write the query for ya using the field names you supplied.

Regards,
Ken

 

[kgoods]

Ayac,

I had a couple minutes so what the h*ll.

Here they are:

SELECT a.* INTO Current_Not_Previous FROM Current_Calendar a WHERE NOT EXISTS (SELECT b.* FROM Previous_Calendar b Where a.ID = b.ID AND a.FromDateTime = b.FromDateTime AND a.TillDateTime = b.TillDateTime AND a.Title = b.Title AND a.CIdentifier = b.CIdentifier AND a.Holdback = b.Holdback AND a.ConnectionCode = b.ConnectionCode AND a.Details = b.Details)

This one will create a table (Current_Not_Previous) of any in current that are not in previous.

SELECT b.* INTO Previous_Not_Current FROM Previous_Calendar b WHERE NOT EXISTS (SELECT a.* FROM Current_Calendar a Where a.ID = b.ID AND a.FromDateTime = b.FromDateTime AND a.TillDateTime = b.TillDateTime AND a.Title = b.Title AND a.CIdentifier = b.CIdentifier AND a.Holdback = b.Holdback AND a.ConnectionCode = b.ConnectionCode AND a.Details =
b.Details)

This one will create a table (Previous_Not_Current) of any in previous that are not in current.

Remember to remove the a.ID = b.ID from the second Select clause if the ID's are completely unique otherwise you will return all rows in each table.

You should be able to run this in access from the query designer by selecting &quot;SQL View&quot; and copying and pasting into the SQL window.

Let me know how it goes....

Regards,
Ken

 

[Ayac]

Thanks you all, especially for Ken!
I think I can go on that last one... it is just my head hurts, I was coding all day continuously and everything seems to flow together now...

 

[kgoods]

Ayac,

Not a problem. I've had a lot of help from the guys in these forums so I try to give a little back when I can.

Don't hesitate to repost if you don't get it. I didn't test it but am pretty sure it will work as written.

Glad I could help.
Take care,
Ken

 

[SQLBill]

Ayac,

If Ken's solution solved your problem, let him and everyone else know that. HOW? By giving him a star. How do you do that? At the bottom of the post that had the solution, there is:

Thank <poster's handle> for this valuable post!

Click on that, confirm that you really want to do it, and then close the window. That will give the poster a star.

-SQLBill

 

[Ayac]

Thanks for the instructions, but I know, I always mark if it is a useful post, but I like &quot;round&quot; things. I just have to make this work first, then I mark. It looks useful to me, but I am programming in ASP using these queries and it is done whenever I finished with both the SQL and the ASP part. It was the end of the day yesterday when I add this topic to the list, and I just started working on it again today.

 

[SQLBill]

Okay, no problem. I just see a lot of posters say Great! Thanks! and not know about the awarding of stars, so I thought I would mention it.

-SQLBill

 

[Ayac]

I run the query on two tables with 175 records. It gave me back 64, however those two tables had no difference at all. Then I changed two records in one of them. The result was 66 records and the ones I changed went to the top of the new Current_Not_Previous list... any ideas why could that be?

 

[kgoods]

Ayac,
I don't quite know what you mean by the &quot;top of the list&quot;. In SQL there is no particular order to rows in tables unless you do some kind of sorting. But that's not the real problem here.

I should mention to avoid confusion.... In SQL records are normally called rows and fields are called columns. I tend to stick with that naming convention so everyone reading knows what we're talking about. So records = rows and fields = columns.

What this query does is return any row from a table that doesn't have an EXACT match on every column in the other table. This could include extra spaces which would be hard to see. If you break down the query it's fairly simple. It does the second select first returning everything in table &quot;b&quot; where every column in a row has an EXACT match for every column in a row in table &quot;a&quot;. Then it performs the &quot;Select into&quot; the new table with any rows that are in &quot;a&quot; that weren't in the first returned set (WHERE NOT EXISTS).

Without seeing your actual data I can't tell you why you seem to be getting rows that are identical. All I can tell you for sure is that they're not identical. There is something in one of the columns that is different. Look very carefully at one of the rows returned that you don't think should have been, then compare it to the row in the other table that you think is identical and you should be able to find one column that is not EXACTLY equal.

As another test, simply replicate one of the tables e.g. make an exact copy with a different name and run the query using those two tables. You should not get any rows returned. If you do then I'd really appreciate it if you could forward me the data because I have a job that runs nightly that does this same query against 50,000+ records coming from our AS400 to populate and update our contact database. I'd hate to think that it's not operating as designed.

Seriously, there is something different about the the 64 rows you mentioned. I can't tell you what it is unless I see the data.

I'm a little busy today but if you want I could take a quick look at it for you if you send me the tables (csv would be ok) and the exact queries you're using. Let me know and I'll get my email address to you.

Kind regards,
Ken