Changes to Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a "master" table and an "updates" table in an Access database.
"Updates is imported from an excel spreadsheet with changes to some of the
records in "master". Both tables have the same structure and primary key.

What I want to do is match each record from "updates" with the corresponding
"master" record and then create a new query showing what the differences are
before manually updating the "master". Each record has 5 text fields
(including the key which will normally find a match), of which any one could
be different between the two tables. It is also possible that the record in
"updates" is new and will not find a match

Any help would be welcome..
 
This query pulls the records and shows the difference.

SELECT Updates.Key, Updates.Field1, IIf([Master].[Key] Is Null,"New
Record",[Master].[Field1]) AS [Master Field1], Updates.Field2, Master.Field2,
Updates.Field3, Master.Field3, Updates.Field4, Master.Field4, Updates.Field5,
Master.Field5
FROM Updates LEFT JOIN Master ON Updates.Key = Master.Key
WHERE (((Updates.Field1)<>[Master].[Field1])) OR
(((Updates.Field2)<>[Master].[Field2])) OR
(((Updates.Field3)<>[Master].[Field3])) OR
(((Updates.Field4)<>[Master].[Field4])) OR
(((Updates.Field5)<>[Master].[Field5])) OR (((Master.Key) Is Null));
 
Just a couple of typos. Remember the when coping and pasting from a post you
may get hard returns in the statement that ain't really supposed to be there.
First typo was AS Hardware.Location instead of AS [Hardware Location].
Second was a space between the period and the bracket in
.... Hardware. [Connection Point],

SELECT Updates.Asset, Updates.Location, IIf(Hardware.Asset Is Null,"New
Record",Hardware.Location) AS [Hardware Location], Updates.[Connection
Point],
Hardware.[Connection Point], Updates.Category, Hardware.Category,
Updates.Classification, Hardware.Classification
FROM Updates LEFT JOIN Hardware ON Updates.Asset= Hardware.Asset
WHERE (((Updates.Location)<> Hardware.Location)) OR (((Updates.[Connection
Point])<> Hardware.[Connection Point])) OR (((Updates.Category)<>
Hardware.Category)) OR (((Updates.Classification)<> Hardware.Classification))
OR (((Hardware.Asset)
Is Null));


Simon said:
Karl.
Thanks for the quick and helpful response. This is my SQL but I am getting
an error "The SELECT statement includes a reserved word or an argument name
that is misspelled or missing, or the punctuation is incorrect. (Error 3141)"
I have checked it but must be missing something.

SELECT Updates.Asset, Updates.Location, IIf(Hardware.Asset Is Null,"New
Record",Hardware.Location) AS Hardware.Location, Updates.[Connection Point],
Hardware. [Connection Point],
Updates.Category, Hardware.Category, Updates.Classification,
Hardware.Classification
FROM Updates LEFT JOIN Hardware ON Updates.Asset= Hardware.Asset
WHERE (((Updates.Location)<> Hardware.Location)) OR
(((Updates.[Connection Point])<> Hardware.[Connection Point])) OR
(((Updates.Category)<> Hardware.Category)) OR
(((Updates.Classification)<> Hardware.Classification)) OR (((Hardware.Asset)
Is Null));




KARL DEWEY said:
This query pulls the records and shows the difference.

SELECT Updates.Key, Updates.Field1, IIf([Master].[Key] Is Null,"New
Record",[Master].[Field1]) AS [Master Field1], Updates.Field2, Master.Field2,
Updates.Field3, Master.Field3, Updates.Field4, Master.Field4, Updates.Field5,
Master.Field5
FROM Updates LEFT JOIN Master ON Updates.Key = Master.Key
WHERE (((Updates.Field1)<>[Master].[Field1])) OR
(((Updates.Field2)<>[Master].[Field2])) OR
(((Updates.Field3)<>[Master].[Field3])) OR
(((Updates.Field4)<>[Master].[Field4])) OR
(((Updates.Field5)<>[Master].[Field5])) OR (((Master.Key) Is Null));


Simon said:
I have a "master" table and an "updates" table in an Access database.
"Updates is imported from an excel spreadsheet with changes to some of the
records in "master". Both tables have the same structure and primary key.

What I want to do is match each record from "updates" with the corresponding
"master" record and then create a new query showing what the differences are
before manually updating the "master". Each record has 5 text fields
(including the key which will normally find a match), of which any one could
be different between the two tables. It is also possible that the record in
"updates" is new and will not find a match

Any help would be welcome..
Click to expand...
Click to expand...
Click to expand...
 
Karl.
Thanks for the quick and helpful response. This is my SQL but I am getting
an error "The SELECT statement includes a reserved word or an argument name
that is misspelled or missing, or the punctuation is incorrect. (Error 3141)"
I have checked it but must be missing something.

SELECT Updates.Asset, Updates.Location, IIf(Hardware.Asset Is Null,"New
Record",Hardware.Location) AS Hardware.Location, Updates.[Connection Point],
Hardware. [Connection Point],
Updates.Category, Hardware.Category, Updates.Classification,
Hardware.Classification
FROM Updates LEFT JOIN Hardware ON Updates.Asset= Hardware.Asset
WHERE (((Updates.Location)<> Hardware.Location)) OR
(((Updates.[Connection Point])<> Hardware.[Connection Point])) OR
(((Updates.Category)<> Hardware.Category)) OR
(((Updates.Classification)<> Hardware.Classification)) OR (((Hardware.Asset)
Is Null));




KARL DEWEY said:
This query pulls the records and shows the difference.

SELECT Updates.Key, Updates.Field1, IIf([Master].[Key] Is Null,"New
Record",[Master].[Field1]) AS [Master Field1], Updates.Field2, Master.Field2,
Updates.Field3, Master.Field3, Updates.Field4, Master.Field4, Updates.Field5,
Master.Field5
FROM Updates LEFT JOIN Master ON Updates.Key = Master.Key
WHERE (((Updates.Field1)<>[Master].[Field1])) OR
(((Updates.Field2)<>[Master].[Field2])) OR
(((Updates.Field3)<>[Master].[Field3])) OR
(((Updates.Field4)<>[Master].[Field4])) OR
(((Updates.Field5)<>[Master].[Field5])) OR (((Master.Key) Is Null));


Simon said:
I have a "master" table and an "updates" table in an Access database.
"Updates is imported from an excel spreadsheet with changes to some of the
records in "master". Both tables have the same structure and primary key.

What I want to do is match each record from "updates" with the corresponding
"master" record and then create a new query showing what the differences are
before manually updating the "master". Each record has 5 text fields
(including the key which will normally find a match), of which any one could
be different between the two tables. It is also possible that the record in
"updates" is new and will not find a match

Any help would be welcome..
Click to expand...
Click to expand...
 
Back
Top