Need urgent help with report...or other method may be necessary...

D

DGragg

Hello -- I have a form where a user enters a Reporting Date from a combo box.
That date is fed to a report that is run from a query. The report works
fine except now, the customer wants the report to run as normal (print data
from selected reporting date), but if there are project managers who did not
turn in data (that is in the database) for that selected date (from combo
box), then he would like those managers' last 'reporting date' data printed
in the report ... Would I somehow change the query, run some kind of
procedure or macro?? I don't know enough about Access yet to know how to
accomplish this. (I hope I'm not confusing the situation more by my
explanation!!)

Since there are 6 tables involved, here is the SQL code from the query if
that will be helpful:

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)],
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
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.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;

Thank you very much in advance!
Debbie
 
D

DGragg

Please close this question. I found the answer from another post. Thank you.

DGragg said:
Hello -- I have a form where a user enters a Reporting Date from a combo box.
That date is fed to a report that is run from a query. The report works
fine except now, the customer wants the report to run as normal (print data
from selected reporting date), but if there are project managers who did not
turn in data (that is in the database) for that selected date (from combo
box), then he would like those managers' last 'reporting date' data printed
in the report ... Would I somehow change the query, run some kind of
procedure or macro?? I don't know enough about Access yet to know how to
accomplish this. (I hope I'm not confusing the situation more by my
explanation!!)

Since there are 6 tables involved, here is the SQL code from the query if
that will be helpful:

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)],
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
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.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;

Thank you very much in advance!
Debbie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top