RE: Refining query

WHERE (((F.DateID - 14) >=(SELECT Max(X.DateID)
FROM funding_history AS X
Where X.DateID <= Forms!frmRptQuery!Combo0
AND X.proj_exec_id=F.proj_exec_id

Test the above.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"SMT" wrote:

I asked this question before and didnt get reponse but I think I didnt ask it
properly nor provide the appropriate information. I am hoping to have
explained it better this time.

I have a query which selects the most recent record that works great (see
below) I want to refine that query to also show me those records which have
actual_complete_date that is less than or equal to 14 days from date chosen
in Forms!frmRptQuery!Combo0.

SELECT PM.proj_mgr_branch AS [Proj Mgr Code], P.project_title AS Project,
PM.proj_mgr_name AS [Proj Mgr Name], P.proj_risk_level AS [Project Risk
Level], C.cust_adv_name AS [Customer Advocate], E.ee_description AS
[Enterprise Executive (EE)], F.planned_funding AS [Funding to spend in
FY07($K) (From Carryover & New Orders)], F.bcwp AS [BCWP ($K)], F.bcws AS
[BCWS ($K)], F.acwp AS [ACWP ($K)], IIf([bcwp]<>0 And
([bcws])<>0,CDbl([bcwp])/CDbl([bcws])) AS SPI, IIf([bcwp]<>0 And
([acwp])<>0,CDbl([bcwp])/CDbl([acwp])) AS CPI, F.method_of_calculation AS
[Method of Calculation], F.comments AS Comments, F.spi_narrative AS [SPI
Narrative], F.cpi_narrative AS [CPI Narrative], F.carryover_funds AS
[Estimated Carry-over ($k)], F.actual_complete_date AS Completed
FROM project_manager AS PM INNER JOIN (enterprise_executive AS E INNER JOIN
(customer_advocate AS C INNER JOIN (project_execution_metrics AS P INNER JOIN
funding_history AS F ON P.proj_exec_id = F.proj_exec_id) ON C.cust_adv_id =
P.cust_adv_id) ON E.ee_id = P.ee_id) ON PM.proj_mgr_id = P.proj_mgr_id
WHERE (((F.DateID)=(SELECT Max(X.DateID) FROM funding_history AS X Where
X.DateID <= Forms!frmRptQuery!Combo0 AND X.proj_exec_id=F.proj_exec_id
)));
.

Relevant Pages

  • Re: Refining query
    ... "SMT" wrote: ... Jerry Whittle, Microsoft Access MVP ... I have a query which selects the most recent record that works great ... FROM project_manager AS PM INNER JOIN (enterprise_executive AS E INNER ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)