Before and After

The first two are easy to determine.

You can do a left join from T1 to T2 and add where <keyfield> is null for
each of your keyfields in T2 to the select statement. This will tell you
records in T1 that are not in T2...i.e. records deleted from T2.

You can do a left join from T2 to T1 and add where <keyfield> is null for
each of your keyfields in T1 to the select statement. This will tell you
records in T2 that are not in T1...i.e. records added to T2.

The third problem is going to be messy based on your design. When you change
a key field, it makes identifying differences nearly impossible...what I
mean is that now you've changed the key field, what's to say that isn't a
NEW record vs. a CHANGED record. There is no way to know, unless I'm misunderstanding
the design.

If you want to track changes to a record. You need to have a timestamp field
that gets updated on a record CHANGE and not change the key fields.

Chris

"A" . wrote:
>
>Can anyone help me please? I am in a bind..
>I have two tables, one table T1 is an original table and the other table
>T2 is a copy of the first table. Som records in T2 have been deleted, added,
>or some of the values have been changed. There is 4 unique keys per record
>that would determine if anything out of the record has been changed.
>
>How would I find out if the entire record has been deleted within T2?
>
>How would I find out if an entire record has been added within T2?
>
>How would I find out if any field within a record in T2 has been changed?
>
>I am using the default Access program for SQL.
>
>TIA!!
>

Can you post the TEXT of your query ?

If you have a left join and then your where statement also has where the
keys from the right hand table are null, you should get the desired result.

I created a similar table structure and the following query works fine.
I'm just using two fields instead of four, but the logic is the same.

select l.*, r.*
from tbl_test1 l
left join tbl_test2 r
on ((l.my_field1 = r.my_field1)
and (l.my_field2 = r.my_field2))
where r.my_field1 is null
and r.my_field2 is null

The above query should return data in your LEFT table that is NOT in your
right table, based on the key values stored in the key fields.

Chris

"A" . wrote:
>
>I tried the code for the deleting function that you suggested and it gives
>me the wrong output. It gives me the records for which any of those fields
>are null.. I need the output to be the records in which all of those fields
>are null. Is there anyway to do that?
>
>"Chris Hylton" <cchylton@hotmail.com> wrote:
>>
>>The first two are easy to determine.
>>
>>You can do a left join from T1 to T2 and add where <keyfield> is null for
>>each of your keyfields in T2 to the select statement. This will tell you
>>records in T1 that are not in T2...i.e. records deleted from T2.
>>
>>You can do a left join from T2 to T1 and add where <keyfield> is null for
>>each of your keyfields in T1 to the select statement. This will tell you
>>records in T2 that are not in T1...i.e. records added to T2.
>>
>>The third problem is going to be messy based on your design. When you
change
>>a key field, it makes identifying differences nearly impossible...what
I
>>mean is that now you've changed the key field, what's to say that isn't
>a
>>NEW record vs. a CHANGED record. There is no way to know, unless I'm misunderstanding
>>the design.
>>
>>If you want to track changes to a record. You need to have a timestamp
>field
>>that gets updated on a record CHANGE and not change the key fields.
>>
>>Chris
>>
>>"A" . wrote:
>>>
>>>Can anyone help me please? I am in a bind..
>>>I have two tables, one table T1 is an original table and the other table
>>>T2 is a copy of the first table. Som records in T2 have been deleted,
added,
>>>or some of the values have been changed. There is 4 unique keys per record
>>>that would determine if anything out of the record has been changed.
>>>
>>>How would I find out if the entire record has been deleted within T2?
>>>
>>>How would I find out if an entire record has been added within T2?
>>>
>>>How would I find out if any field within a record in T2 has been changed?
>>>
>>>I am using the default Access program for SQL.
>>>
>>>TIA!!
>>>
>>
>

Looks almost exactly like my query...only difference is that the ON part of
my query is entirely enclosed in () in addition to the () around each part
of the ON clause...

Try adding () around the entire ON part and see if that changes the results...

Outside of that, not sure why it's returning data that way...mine query is
returning the expected results, not where ANY of the join fields are null,
they ALL have to be null.

Chris

"A" . wrote:
>
>Here is the code.. I get the records in which ANY of the four fields are
null.
>I need the output to be the records where ALL of the four fields are null
>at the same time.
>
>SELECT Before.*
>FROM ProjectDocs AS Before LEFT JOIN ProjectDocsCOPY AS After ON (Before.ProjectID
>= After.ProjectID) AND (Before.Number= After.Number) AND (Before.[deliever
>on]= After.[deliever on]) AND (Before.[Version]= After.[Version])
>WHERE ( (After.ProjectID Is Null)
> AND (After.Number Is Null)
> AND (After.[deliever on] Is Null)
> AND (After.[Version] Is Null));
>
>Thanks!
>
>"Chris Hylton" <cchylton@hotmail.com> wrote:
>>
>>Can you post the TEXT of your query ?
>>
>>If you have a left join and then your where statement also has where the
>>keys from the right hand table are null, you should get the desired result.
>>
>>I created a similar table structure and the following query works fine.
>
>>I'm just using two fields instead of four, but the logic is the same.
>>
>> select l.*, r.*
>> from tbl_test1 l
>> left join tbl_test2 r
>> on ((l.my_field1 = r.my_field1)
>> and (l.my_field2 = r.my_field2))
>> where r.my_field1 is null
>> and r.my_field2 is null
>>
>>The above query should return data in your LEFT table that is NOT in your
>>right table, based on the key values stored in the key fields.
>>
>>Chris
>>
>>"A" . wrote:
>>>
>>>I tried the code for the deleting function that you suggested and it gives
>>>me the wrong output. It gives me the records for which any of those fields
>>>are null.. I need the output to be the records in which all of those fields
>>>are null. Is there anyway to do that?
>>>
>>>"Chris Hylton" <cchylton@hotmail.com> wrote:
>>>>
>>>>The first two are easy to determine.
>>>>
>>>>You can do a left join from T1 to T2 and add where <keyfield> is null
>for
>>>>each of your keyfields in T2 to the select statement. This will tell
>you
>>>>records in T1 that are not in T2...i.e. records deleted from T2.
>>>>
>>>>You can do a left join from T2 to T1 and add where <keyfield> is null
>for
>>>>each of your keyfields in T1 to the select statement. This will tell
>you
>>>>records in T2 that are not in T1...i.e. records added to T2.
>>>>
>>>>The third problem is going to be messy based on your design. When you
>>change
>>>>a key field, it makes identifying differences nearly impossible...what
>>I
>>>>mean is that now you've changed the key field, what's to say that isn't
>>>a
>>>>NEW record vs. a CHANGED record. There is no way to know, unless I'm
>misunderstanding
>>>>the design.
>>>>
>>>>If you want to track changes to a record. You need to have a timestamp
>>>field
>>>>that gets updated on a record CHANGE and not change the key fields.
>>>>
>>>>Chris
>>>>
>>>>"A" . wrote:
>>>>>
>>>>>Can anyone help me please? I am in a bind..
>>>>>I have two tables, one table T1 is an original table and the other table
>>>>>T2 is a copy of the first table. Som records in T2 have been deleted,
>>added,
>>>>>or some of the values have been changed. There is 4 unique keys per
record
>>>>>that would determine if anything out of the record has been changed.
>
>>>>>
>>>>>How would I find out if the entire record has been deleted within T2?
>>>>>
>>>>>How would I find out if an entire record has been added within T2?
>>>>>
>>>>>How would I find out if any field within a record in T2 has been changed?
>>>>>
>>>>>I am using the default Access program for SQL.
>>>>>
>>>>>TIA!!
>>>>>
>>>>
>>>
>>
>