Need urgent help for report ... query issue or something else?

D

DGragg

Hello -- This is my first time asking for help, and I think my first post
didn't go through. If it did, please forgive me.

I hope I am making sense here -- I have a form with a combo box for users to
select a reporting date. That date is passed to a report that is based on a
query (using 6 tables). Everything works great, but now the customer has an
additional request for that report. If a user runs a report for reporting
date 10/10/07 and there are project managers who did not enter their data for
10/10, he wants the last data for those particular managers to display on the
report...along with other the other project managers who have 10/10 data. I
don't know what road I need to take to accomplish this - query, macro, etc.

Since there are 6 tables involved, here is the SQL view on the query - any
help you can provide would be great....

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

Thank you in advance -- Debbie
 
D

DGragg

Please disregard this post. I see my original post went through! Sorry!!
-- Debbie

DGragg said:
Hello -- This is my first time asking for help, and I think my first post
didn't go through. If it did, please forgive me.

I hope I am making sense here -- I have a form with a combo box for users to
select a reporting date. That date is passed to a report that is based on a
query (using 6 tables). Everything works great, but now the customer has an
additional request for that report. If a user runs a report for reporting
date 10/10/07 and there are project managers who did not enter their data for
10/10, he wants the last data for those particular managers to display on the
report...along with other the other project managers who have 10/10 data. I
don't know what road I need to take to accomplish this - query, macro, etc.

Since there are 6 tables involved, here is the SQL view on the query - any
help you can provide would be great....

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

Thank you in advance -- Debbie
Click to expand...
 

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

Similar Threads

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

Top