|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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));
"Simon" wrote:
[Quoted Text] > 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..
|
|
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" wrote:
[Quoted Text] > 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" wrote:
>
> > 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" wrote:
> >
> > > 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..
|
|
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" wrote:
[Quoted Text] > 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" wrote:
>
> > 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..
|
|
Karl
Very many thanks - that was just what was needed.
"KARL DEWEY" wrote:
[Quoted Text] > 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));
|
|
|