Re: Refining query

Hi SMT,

- a reporting period of (combo0) to (combo0 +14) ?
- only completed records (actual_complete_date is not null)?

WHERE
((
(F.DateID)=
(SELECT Max(X.DateID)
FROM funding_history AS X
Where
(X.DateID >= Forms!frmRptQuery!Combo0
AND
X.DateID < Forms!frmRptQuery!Combo0 + 15)
AND
X.actual_complete_date IS NOT NULL
AND
X.proj_exec_id=F.proj_exec_id )
));

or do you no longer want just the single latest record
for a project?

WHERE
((
(F.DateID) IN
(SELECT X.DateID
FROM funding_history AS X
Where
(X.DateID >= Forms!frmRptQuery!Combo0
AND
X.DateID < Forms!frmRptQuery!Combo0 + 15)
AND
X.actual_complete_date IS NOT NULL
AND
X.proj_exec_id=F.proj_exec_id )
));

"SMT" wrote:
Hello thanks for the fast response. I still dont think I am explaining
this
in the best way. The original query allows the user to pull the project
status by the dateid using the Forms!frmRptQuery!Combo0 (which is a list
of
all the submission dates). If say April 11 was chosen and there was no
input
for April 11 then the query choses the lastest record for that project,
say
March 28. The reports are suppose to be done every two weeks but that
often
doesnt happen so as in the case above the lastest entry is shown and that
existing query (copied below) performs this well. I had to add an
actual_complete_date and now my supervisor wants the completed records to
show for 1 reporting period or 14 days after the date selected in the
Forms!frmRptQuery!Combo0. That is the piece I cant seem to get added to
the
query. I appreciate any suggestions.
Thanks

"Jerry Whittle" wrote:

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: Max Query
    ... When I try to use this query I get the message that DocumentTitle is not ... FROM (tblDocument INNER JOIN tblTraining ... INNER JOIN tblPerson ON tblTraining.PersonID = tblPerson.PersonID ... Jerry Whittle, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • RE: Refining query
    ... I still dont think I am explaining this ... The original query allows the user to pull the project ... Jerry Whittle, Microsoft Access MVP ... FROM project_manager AS PM INNER JOIN (enterprise_executive AS E INNER JOIN ...
    (microsoft.public.access.queries)
  • RE: Blank Columns Everytime
    ... SELECT [Brokerage Bookeeping 2006].Account, ... INNER JOIN ... Jerry Whittle, Microsoft Access MVP ... Here is the SQL code from the last Query. ...
    (microsoft.public.access.queries)