Unmatch between 2 table

[Guest]

Hi,
I tried many unmatch query but not have result. Please help.
I have 2 tables: Score1 and Score2, 2 table have identical structure.
Score1: ScoreID, Name, Q1
Score2: ScoreID,Name,Q2
How can I query list out who have the same ScoreID BUT different Name OR Q1?
like:
Score1 Score2
ScoreID Name Q1 ScoreID Name Q1
12 John 10 12 Nancy 10
11 Ron 5 11 Ron 15
13 Mike 1 13 Mike 1

It will list out:
ScoreID1 Name1 Q1_1 ScoreID2 Name2 Q1_2
11 Ron 5 12 Nancy 10
12 John 10 11 Ron 15

Any suggestion thanks in advange.
MN

 

[Guest]

Try something like that

SELECT Score1.ScoreID, Score1.[Name], Score1.Q1, Score2.[Name], Score2.Q1
FROM Score2 INNER JOIN Score1 ON Score2.ScoreID= Score1.ScoreID
WHERE Score1.[Name]<>[Score2].[Name] OR Score1.Q1<>[Score2].[Q1]

You should consider changing the field name [Name], because it a reserved
name in Access, it will be fine while it inside square brackets

 

[Guest]

Hi Ofer,
Thank for reply. But you know what?
The result come out like this:

Score1.ScoreID Score2.ScoreID Score1.Name Score2.Name S1.Q1 S2.Q1
12 12 Ron Nancy
10 10
12 12 Nancy Ron
10 10
11 11 Ron Ron
10 15
11 11 Ron Ron
15 10

BTW,
ScoreID are duplicate too (!) in both 2 table Score1 and Score2
Any Advise,
Regards,
MN

Try something like that

SELECT Score1.ScoreID, Score1.[Name], Score1.Q1, Score2.[Name], Score2.Q1
FROM Score2 INNER JOIN Score1 ON Score2.ScoreID= Score1.ScoreID
WHERE Score1.[Name]<>[Score2].[Name] OR Score1.Q1<>[Score2].[Q1]

You should consider changing the field name [Name], because it a reserved
name in Access, it will be fine while it inside square brackets

--
\\// Live Long and Prosper \\//
BS"D

Hi,
I tried many unmatch query but not have result. Please help.
I have 2 tables: Score1 and Score2, 2 table have identical structure.
Score1: ScoreID, Name, Q1
Score2: ScoreID,Name,Q2
How can I query list out who have the same ScoreID BUT different Name OR Q1?
like:
Score1 Score2
ScoreID Name Q1 ScoreID Name Q1
12 John 10 12 Nancy 10
11 Ron 5 11 Ron 15
13 Mike 1 13 Mike 1

It will list out:
ScoreID1 Name1 Q1_1 ScoreID2 Name2 Q1_2
11 Ron 5 12 Nancy 10
12 John 10 11 Ron 15

Any suggestion thanks in advange.
MN

 

[Guest]

Can you post your SQL?

--
\\// Live Long and Prosper \\//
BS"D

Hi Ofer,
Thank for reply. But you know what?
The result come out like this:

Score1.ScoreID Score2.ScoreID Score1.Name Score2.Name S1.Q1 S2.Q1
12 12 Ron Nancy
10 10
12 12 Nancy Ron
10 10
11 11 Ron Ron
10 15
11 11 Ron Ron
15 10

BTW,
ScoreID are duplicate too (!) in both 2 table Score1 and Score2
Any Advise,
Regards,
MN

Try something like that

SELECT Score1.ScoreID, Score1.[Name], Score1.Q1, Score2.[Name], Score2.Q1
FROM Score2 INNER JOIN Score1 ON Score2.ScoreID= Score1.ScoreID
WHERE Score1.[Name]<>[Score2].[Name] OR Score1.Q1<>[Score2].[Q1]

You should consider changing the field name [Name], because it a reserved
name in Access, it will be fine while it inside square brackets

--
\\// Live Long and Prosper \\//
BS"D

Hi,
I tried many unmatch query but not have result. Please help.
I have 2 tables: Score1 and Score2, 2 table have identical structure.
Score1: ScoreID, Name, Q1
Score2: ScoreID,Name,Q2
How can I query list out who have the same ScoreID BUT different Name OR Q1?
like:
Score1 Score2
ScoreID Name Q1 ScoreID Name Q1
12 John 10 12 Nancy 10
11 Ron 5 11 Ron 15
13 Mike 1 13 Mike 1

It will list out:
ScoreID1 Name1 Q1_1 ScoreID2 Name2 Q1_2
11 Ron 5 12 Nancy 10
12 John 10 11 Ron 15

Any suggestion thanks in advange.
MN

 

[Guest]

Here my SQL:
SELECT [Project Score].[ProjectID], [Project Score1].[ProjectID], [Project
Score].[ReviewerID], [Project Score1].[ReviewerID], [Project Score].[Q1],
[Project Score].[Q2], [Project Score1].[Q1], [Project Score1].[Q2]
FROM [Project Score1] INNER JOIN [Project Score] ON [Project
Score].[ProjectID]=[Project Score1].[ProjectID]
WHERE ([Project Score].[ReviewerID]<>[Project Score1].[ReviewerID]) OR
([Project Score].Q1<>[Project Score1].Q1) OR ([Project Score].Q2<>[Project
Score1].Q2);
Thanks for reply,
MN

 

[Guest]

In the first post you didn't mantion difference in Q2.
Do you realy need it?

Or that what you need
SELECT [Project Score].[ProjectID], [Project Score1].[ProjectID], [Project
Score].[ReviewerID], [Project Score1].[ReviewerID], [Project Score].[Q1],
[Project Score].[Q2], [Project Score1].[Q1], [Project Score1].[Q2]
FROM [Project Score1] INNER JOIN [Project Score] ON [Project
Score].[ProjectID]=[Project Score1].[ProjectID]
WHERE ([Project Score].[ReviewerID]<>[Project Score1].[ReviewerID]) OR
([Project Score].Q1<>[Project Score1].Q1)
--
\\// Live Long and Prosper \\//
BS"D

Here my SQL:
SELECT [Project Score].[ProjectID], [Project Score1].[ProjectID], [Project
Score].[ReviewerID], [Project Score1].[ReviewerID], [Project Score].[Q1],
[Project Score].[Q2], [Project Score1].[Q1], [Project Score1].[Q2]
FROM [Project Score1] INNER JOIN [Project Score] ON [Project
Score].[ProjectID]=[Project Score1].[ProjectID]
WHERE ([Project Score].[ReviewerID]<>[Project Score1].[ReviewerID]) OR
([Project Score].Q1<>[Project Score1].Q1) OR ([Project Score].Q2<>[Project
Score1].Q2);
Thanks for reply,
MN

 

[Guest]

Thank for reply,
Yes, I need it. It is total 20 questions (From Q1 to Q20).!
Regards
MN

In the first post you didn't mantion difference in Q2.
Do you realy need it?

Or that what you need
SELECT [Project Score].[ProjectID], [Project Score1].[ProjectID], [Project
Score].[ReviewerID], [Project Score1].[ReviewerID], [Project Score].[Q1],
[Project Score].[Q2], [Project Score1].[Q1], [Project Score1].[Q2]
FROM [Project Score1] INNER JOIN [Project Score] ON [Project
Score].[ProjectID]=[Project Score1].[ProjectID]
WHERE ([Project Score].[ReviewerID]<>[Project Score1].[ReviewerID]) OR
([Project Score].Q1<>[Project Score1].Q1)
--
\\// Live Long and Prosper \\//
BS"D

Here my SQL:
SELECT [Project Score].[ProjectID], [Project Score1].[ProjectID], [Project
Score].[ReviewerID], [Project Score1].[ReviewerID], [Project Score].[Q1],
[Project Score].[Q2], [Project Score1].[Q1], [Project Score1].[Q2]
FROM [Project Score1] INNER JOIN [Project Score] ON [Project
Score].[ProjectID]=[Project Score1].[ProjectID]
WHERE ([Project Score].[ReviewerID]<>[Project Score1].[ReviewerID]) OR
([Project Score].Q1<>[Project Score1].Q1) OR ([Project Score].Q2<>[Project
Score1].Q2);
Thanks for reply,
MN

Can you post your SQL?

 

[Guest]

Thank for all help...
I fingure it out...by created 2 primary keys for 2 these table and it is
work
Again Thank all for helping.
Regards,
MN

In the first post you didn't mantion difference in Q2.
Do you realy need it?

Or that what you need
SELECT [Project Score].[ProjectID], [Project Score1].[ProjectID], [Project
Score].[ReviewerID], [Project Score1].[ReviewerID], [Project Score].[Q1],
[Project Score].[Q2], [Project Score1].[Q1], [Project Score1].[Q2]
FROM [Project Score1] INNER JOIN [Project Score] ON [Project
Score].[ProjectID]=[Project Score1].[ProjectID]
WHERE ([Project Score].[ReviewerID]<>[Project Score1].[ReviewerID]) OR
([Project Score].Q1<>[Project Score1].Q1)
--
\\// Live Long and Prosper \\//
BS"D

Here my SQL:
SELECT [Project Score].[ProjectID], [Project Score1].[ProjectID], [Project
Score].[ReviewerID], [Project Score1].[ReviewerID], [Project Score].[Q1],
[Project Score].[Q2], [Project Score1].[Q1], [Project Score1].[Q2]
FROM [Project Score1] INNER JOIN [Project Score] ON [Project
Score].[ProjectID]=[Project Score1].[ProjectID]
WHERE ([Project Score].[ReviewerID]<>[Project Score1].[ReviewerID]) OR
([Project Score].Q1<>[Project Score1].Q1) OR ([Project Score].Q2<>[Project
Score1].Q2);
Thanks for reply,
MN

Can you post your SQL?