Unable to bring back all results from table with query

For a uni assignment we have a designated query that we have to create.

But when I run the query, I get one result returned to me. I should have two
returned to me, as I have two records in the Problem table.

The SQL is:- SELECT Problem.[Problem Number], User.[User ID], User.[User
Name], User.[Job Title], User.Email, User.[Telephone No], Asset.[Asset Code
No], Problem.[Problem Description], Problem.[Current Activity Log],
Problem.[Solution Description]
FROM [User] INNER JOIN ((Asset INNER JOIN Problem ON Asset.[Asset Code No] =
Problem.[Asset Code No]) INNER JOIN [Contact Log] ON Problem.[Problem Number]
= [Contact Log].[Problem No]) ON User.[User ID] = [Contact Log].[User ID];

The thing is that when I do a select all from the problem table I get all
the results, but I do not when I use the SQL described above.

Any ideas as to what may be causing this?

Cheers in advance

You are using inner joins which means that there must be a matching record in
both tables or the record won't be returned.

Try changing the joins from the Problem table to Left or Right joins. If the
tables are small enough, you should be able to see which table doesn't have a
matching record.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Karrot_Bex" wrote:

For a uni assignment we have a designated query that we have to create.

But when I run the query, I get one result returned to me. I should have two
returned to me, as I have two records in the Problem table.

The SQL is:- SELECT Problem.[Problem Number], User.[User ID], User.[User
Name], User.[Job Title], User.Email, User.[Telephone No], Asset.[Asset Code
No], Problem.[Problem Description], Problem.[Current Activity Log],
Problem.[Solution Description]
FROM [User] INNER JOIN ((Asset INNER JOIN Problem ON Asset.[Asset Code No] =
Problem.[Asset Code No]) INNER JOIN [Contact Log] ON Problem.[Problem Number]
= [Contact Log].[Problem No]) ON User.[User ID] = [Contact Log].[User ID];

The thing is that when I do a select all from the problem table I get all
the results, but I do not when I use the SQL described above.

Any ideas as to what may be causing this?

Cheers in advance

You are using inner joins which means that there must be a matching record in
both tables or the record won't be returned.

Try changing the joins from the Problem table to Left or Right joins. If the
tables are small enough, you should be able to see which table doesn't have a
matching record.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Karrot_Bex" wrote:

For a uni assignment we have a designated query that we have to create.

But when I run the query, I get one result returned to me. I should have two
returned to me, as I have two records in the Problem table.

The SQL is:- SELECT Problem.[Problem Number], User.[User ID], User.[User
Name], User.[Job Title], User.Email, User.[Telephone No], Asset.[Asset Code
No], Problem.[Problem Description], Problem.[Current Activity Log],
Problem.[Solution Description]
FROM [User] INNER JOIN ((Asset INNER JOIN Problem ON Asset.[Asset Code No] =
Problem.[Asset Code No]) INNER JOIN [Contact Log] ON Problem.[Problem Number]
= [Contact Log].[Problem No]) ON User.[User ID] = [Contact Log].[User ID];

The thing is that when I do a select all from the problem table I get all
the results, but I do not when I use the SQL described above.

Any ideas as to what may be causing this?

Cheers in advance

You are using inner joins which means that there must be a matching record in
both tables or the record won't be returned.

Try changing the joins from the Problem table to Left or Right joins. If the
tables are small enough, you should be able to see which table doesn't have a
matching record.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Karrot_Bex" wrote:

For a uni assignment we have a designated query that we have to create.

But when I run the query, I get one result returned to me. I should have two
returned to me, as I have two records in the Problem table.

The SQL is:- SELECT Problem.[Problem Number], User.[User ID], User.[User
Name], User.[Job Title], User.Email, User.[Telephone No], Asset.[Asset Code
No], Problem.[Problem Description], Problem.[Current Activity Log],
Problem.[Solution Description]
FROM [User] INNER JOIN ((Asset INNER JOIN Problem ON Asset.[Asset Code No] =
Problem.[Asset Code No]) INNER JOIN [Contact Log] ON Problem.[Problem Number]
= [Contact Log].[Problem No]) ON User.[User ID] = [Contact Log].[User ID];

The thing is that when I do a select all from the problem table I get all
the results, but I do not when I use the SQL described above.

Any ideas as to what may be causing this?

Cheers in advance