Nedlog said:
When I try this access returns "At most one record can be returned by
this subquery"
This is because the table I am building the query on has many projects
in each phase (Implementation, Feasibility, Design etc)
How can I write the subquery so it joins on code from the main query?
This should work as each project code has only one record for each
phase.
Sorry Damien, should have realised...
SELECT
Code,
[Name],
(SELECT q.Start_Date FROM yurtable As q
WHERE
q.Phase='Feasability'
AND
q.Code = t.Code) As Feasibility_Start_Date,
(SELECT q.End_Date FROM yurtable As q
WHERE
q.Phase='Feasability'
AND
q.Code = t.Code) As Feasibility_End_Date,
(SELECT q.Start_Date FROM yurtable As q
WHERE
q.Phase='Design'
AND
q.Code = t.Code) As Design_Start_Date,
(SELECT q.End_Date FROM yurtable As q
WHERE
q.Phase='Design'
AND
q.Code = t.Code) As Design_End_Date,
(SELECT q.Start_Date FROM yurtable As q
WHERE q.Phase= 'Implementation'
AND
q.Code = t.Code) As Implementation_Start_Date,
(SELECT q.End_Date FROM yurtable As q
WHERE q.Phase='Implementation'
AND
q.Code = t.Code) As Implementation_End_Date
FROM yurtable As t
GROUP BY
Code,
[Name];
=========================
A less "quick-and-dirty" solution:
Create a query (say "qryDistinctProject")
that gives you each "distinct project."
SELECT DISTINCT
Code,
[Name]
FROM
yurtable;
Start a new query.
Add "qryDistinctProject"
Add original table 3 times.
====
aliases
====
Right-mouse click on each
of the 3 table copies,
choose "Properties"
change Alias to
F
for the "feasibility" table
D
for the "design" table
I
for the "implementation" table
Right-mouse click on the query,
choose "Properties"
change Alias to
Q
====
criteria
====
Drop-and-drag F.Phase
down to a Field row in grid.
In Criteria row for this column, type in
Feasibility
Drop-and-drag D.Phase
down to a Field row in grid.
In Criteria row for this column, type in
Design
Drop-and-drag I.Phase
down to a Field row in grid.
In Criteria row for this column, type in
Implementation
None of these 3 columns need to be shown
in final query output, they just determine we
are going to get
"feasibility dates" from "F"
"design dates" from "D"
"implementation dates" from "I"
so uncheck "Show" checkbox
for these 3 columns in grid.
====
joins
====
Drag-and-drop Q.Code over
on F.Code which will produce
a join line between Q and F.
You say that
"each project code has only one record for each
phase"
so maybe this "INNER JOIN" is all you
need to connect Q with F.
But, just to be sure, I might
right-mouse click on the join line,
choose properties,
and choose option to include
"all of Q and only matching F"
which will "LEFT JOIN" Q to F.
You might also need to complete
further join lines between Q and F
(you know your data)
Repeat above for joining
Q to D
and
Q to I
=====
output fields
=====
Drag-and-drop fields from Q
down to a field row in there own
columns in the grid.
Drag-and-drop date fields from F
down to a field row in there own
columns in the grid.
The F.Start_Date column might look like
Field: Start_Date
Table: F
Sort:
Show: <checked>
Criteria:
Or:
give it an alias by entering text
at the start of the Field row,
then a colon, for example:
Field: Feasibility_Start_Date: Start_Date
Table: F
Sort:
Show: <checked>
Criteria:
Or:
Do similarly for
F.End_Date
D.Start_Date
D.End_Date
I.Start_Date
I.End_Date
