Sub Query for report output?

[Hugh self taught]

Hi All,

I need to get my head around a query based on the following to output to my
report:-

My table has Idx, Fname, Sname, Type, Resp (among others)

For most records where Type = 3, there will be a reference in Resp which is
a reference to Idx (PK)

I need to select all records where Type = 3 & then get Fname & " " & Sname
of Resp.

In other words in datasheet view it would look something like
1 Silly Nut 3 Joe Bloggs
2 Some Twit 3 Mary Lamb

I'd really appreciate it if someone could help me with the query to achieve
that.

 

[KARL DEWEY]

Try this --
SELECT YourTable.Idx, YourTable.Fname, YourTable.Sname, Type,
YourTable_1.Fname, YourTable_1.Sname
FROM YourTable LEFT JOIN YourTable_1 ON YourTable.Resp = YourTable_1.Idx
WHERE Type = 3;

If Type is a text field then place double quotes around the 3 above.

 

[KARL DEWEY]

Try this --
SELECT YourTable.Idx, YourTable.Fname, YourTable.Sname, Type,
YourTable_1.Fname, YourTable_1.Sname
FROM YourTable LEFT JOIN YourTable_1 ON YourTable.Resp = YourTable_1.Idx
WHERE Type = 3;

If Type is a text field then place double quotes around the 3 above.

 

[Hugh self taught]

Hi Karl,

Thanks for the reply,

I get the response that Access can't represent join the expression
Candidates.AccResp = Candidates_1.Candidates_Idx in Design View and that it
cannot find the Input table or query Candidates_1 when I try change to
Datasheet view. I even resorted to complete table.field names to try overcome
the problem.

Here is the complete SQL with the full field names:-

SELECT Candidates.Candidates_Idx, Candidates.FirstName, Candidates.Surname,
Candidates.Type, Candidates_1.FirstName, Candidates_1.Surname,
Candidates_1.Candidates_Idx
FROM Candidates LEFT JOIN Candidates_1 ON Candidates.AccResp =
Candidates_1.Candidates_Idx
WHERE Candidates.Type = 3;

 

[Hugh self taught]

Hi Karl,

Thanks for the reply,

I get the response that Access can't represent join the expression
Candidates.AccResp = Candidates_1.Candidates_Idx in Design View and that it
cannot find the Input table or query Candidates_1 when I try change to
Datasheet view. I even resorted to complete table.field names to try overcome
the problem.

Here is the complete SQL with the full field names:-

SELECT Candidates.Candidates_Idx, Candidates.FirstName, Candidates.Surname,
Candidates.Type, Candidates_1.FirstName, Candidates_1.Surname,
Candidates_1.Candidates_Idx
FROM Candidates LEFT JOIN Candidates_1 ON Candidates.AccResp =
Candidates_1.Candidates_Idx
WHERE Candidates.Type = 3;

 

[Hugh self taught]

Me again, Just discovered that "LEFT JOIN Candidates AS Candidates_1 ON ..."
was the culprit

 

[Hugh self taught]

Me again, Just discovered that "LEFT JOIN Candidates AS Candidates_1 ON ..."
was the culprit