Compare identical table structures for different values

[ago]

Hi,

Is there any way to compare two identical table structures in access for different values in them.
EG:
Table 1:
Name occupation
rob plumber

Table 2:
Name occupation
rob builder

Is there any way this can be done in a query ?
Also how can a select query be written in vba for say table one in the above example?

[ADezii]

Hi,

Is there any way to compare two identical table structures in access for different values in them.
EG:
Table 1:
Name occupation
rob plumber

Table 2:
Name occupation
rob builder

Is there any way this can be done in a query ?
Also how can a select query be written in vba for say table one in the above example?

The following SQL Statement will return the Name, Occupation in Table 1, and all Occupations in Table 2 that do not match the Occupation in Table 1 for the same individual. Is this what you want?

SELECT [Table 1].Name, [Table 1].Occupation, [Table 2].Occupation
FROM [Table 1] INNER JOIN [Table 2] ON [Table 1].Name = [Table 2].Name
WHERE [Table 2].Occupation<>[Table 1].[Occupation];

[ago]

The following SQL Statement will return the Name, Occupation in Table 1, and all Occupations in Table 2 that do not match the Occupation in Table 1 for the same individual. Is this what you want?

SELECT [Table 1].Name, [Table 1].Occupation, [Table 2].Occupation
FROM [Table 1] INNER JOIN [Table 2] ON [Table 1].Name = [Table 2].Name
WHERE [Table 2].Occupation<>[Table 1].[Occupation];

Yes. This does work fine. Just to add to it if I use <>Nz it will also compare the fields that are null right ?

[missinglinq]

I've changed the title of your post to make the subject matter clearer to members on the forum now, as well as people who may use the Search function in the future researching similar problems. Since you are a new member of the forum, please take the time to read the Posting Guidelines, paying particular attention to the section "Give a clear title to your question."

(POSTING GUIDELINES: Please read carefully before posting to a forum)

Welcome to TheScripts!

Linq ;0)>