Thanks for your help I sure do appreciate it. Below is the sQL view
SELECT project_manager.proj_mgr_branch AS [Project Manager Branch],
project_execution_metrics.project_title AS Project,
project_manager.proj_mgr_name AS [Proj Mgr Name],
project_execution_metrics.proj_risk_level AS [Project Risk Level],
customer_advocate.cust_adv_name AS [Customer Advocate],
enterprise_executive.ee_description AS [Enterprise Executive (EE)],
funding_history.planned_funding AS [Funding to spend in FY07($K) (From
Carryover & New Orders)], funding_history.bcwp AS [BCWP ($K)],
funding_history.bcws AS [BCWS ($K)], funding_history.acwp AS [ACWP ($K)],
[bcwp]/[bcws] AS SPI, [BCWP]/[ACWP] AS CPI,
funding_history.method_of_calculation AS [Method of Calculation],
funding_history.comments AS Comments, funding_history.spi_narrative AS [SPI
Narrative], funding_history.cpi_narrative AS [CPI Narrative],
funding_history.carryover_funds AS [Estimated Carry-over ($k)]
FROM tblDateSubmitted INNER JOIN (project_manager INNER JOIN
((enterprise_executive INNER JOIN (customer_advocate INNER JOIN
project_execution_metrics ON customer_advocate.cust_adv_id =
project_execution_metrics.cust_adv_id) ON enterprise_executive.ee_id =
project_execution_metrics.ee_id) INNER JOIN funding_history ON
project_execution_metrics.proj_exec_id = funding_history.proj_exec_id) ON
project_manager.proj_mgr_id = project_execution_metrics.proj_mgr_id) ON
tblDateSubmitted.DateID = funding_history.DateID
GROUP BY project_manager.proj_mgr_branch,
project_execution_metrics.project_title, project_manager.proj_mgr_name,
project_execution_metrics.proj_risk_level, customer_advocate.cust_adv_name,
enterprise_executive.ee_description, funding_history.planned_funding,
funding_history.bcwp, funding_history.bcws, funding_history.acwp,
funding_history.method_of_calculation, funding_history.comments,
funding_history.spi_narrative, funding_history.cpi_narrative,
funding_history.carryover_funds, funding_history.DateID,
tblDateSubmitted.DateReport, funding_history.date_created,
project_execution_metrics.cust_adv_id, project_execution_metrics.ee_id,
project_manager.proj_mgr_id, funding_history.funding_recd,
funding_history.last_modified_date, project_execution_metrics.proj_exec_id,
customer_advocate.cust_adv_id, enterprise_executive.ee_id,
funding_history.fund_history_id, funding_history.proj_exec_id,
project_execution_metrics.proj_mgr_id, funding_history.last_modified_date,
project_execution_metrics.proj_exec_id
HAVING (((tblDateSubmitted.DateReport)=(SELECT Max(X.DateReport) FROM
tblDateSubmitted As X WHERE X.DateReport <= Forms!frmRptQuery!Combo0 )));
SMT wrote:
Thanks so much for the help. I put that in my query in the criteria under the
DateReport field of the tblDateSubmitted. I do get the records that match the
input from the combo box but for those records that do not have a matching
date I do not get the latest record (max) that is <= to the combo box. Do
you have any suggestion where I should look for my errors?
:
SMT wrote:
I have a database that tracks all active programs and their funding status,
which is suppose to be reported every two weeks.
I have a query that is dependent upon a report date selection in Combo0
which works perfectly but now I need the query to pull those reports that not
only match the date selection in the combo box but list the latest record
for those that did not have input to match that datereport
So in my query I have the below statement under the criteria of the
datereport field and that works great to capture the selected date from the
combo box.
[Forms]![frmRptQuery]![Combo0] (SQL view - HAVING
(((tblDateSubmitted.DateReport)=[Forms]![frmRptQuery]![Combo0]));_
What do I need to add so that if a project manager did not input his
information for the selected date it exports the latest information for that
project?
This is a more complex question than just matching a date.
I think your problem can be rephrased as Find the latest
records with a date less than or equal to the date in the
combo box.
This then can be done by using a subquery:
SELECT T.*
FROM tblDateSubmitted As T
WHERE T.DateReport = (
SELECT Max(X.DateReport)
FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0
)