Results on Combo Box selection or last update

[Guest]

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?

Thanks

 

[Marshall Barton]

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
)

 

[Guest]

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?

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
)

 

[Marshall Barton]

Post back with a Copy/Paste of your query's SQL view and
I'll take a look at it.
--
Marsh
MVP [MS Access]

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?

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
)

 

[Guest]

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

Post back with a Copy/Paste of your query's SQL view and
I'll take a look at it.
--
Marsh
MVP [MS Access]

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
)

 

[Marshall Barton]

OMG, that's enough to give anyone a headache ;-)

The first thing I would do with that thing is alias all the
tables to a (very) short name just to make it easier to
read.

I think that the problem is caused by your use of the GROUP
BY clause. I can't find anywhere where you use an
aggregate function (Count, Sum, etc) so I see no reason for
you to use GROUP BY. Using GROUP BY also causes the QBE to
put all the criteria in a HAVING clause, which is at worst
incorrect and at best inefficient. In your case it is
incorrect.

Unless you have some convoluted reason for using a Totals
type query, I think you might be ok if you just unclick the
Totals button on the Query Design ToolBar.
--
Marsh
MVP [MS Access]

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

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
)

 

[Guest]

Yes, I certainly agree! I thank you for being patient with me and trying to
help me work this out. I am certainly learning a lot but have a long way to
go.

I tried using the alias and took off the grouping but I still only get the
records that match what was put in the combo box, so I am still missing the
latest records of those projects that dont match the combo box selection.

SQL View
SELECT M.proj_mgr_branch, P.project_title, M.proj_mgr_name,
P.proj_risk_level, C.cust_adv_name, E.ee_description, F.planned_funding,
F.bcwp, F.bcws, F.acwp, [bcwp]/[bcws] AS Expr1, [BCWP]/[ACWP] AS Expr2,
F.method_of_calculation, F.comments, F.spi_narrative, F.cpi_narrative,
F.carryover_funds

FROM tblDateSubmitted AS D INNER JOIN (project_manager AS M INNER JOIN
((enterprise_executive AS E INNER JOIN (customer_advocate AS C INNER JOIN
project_execution_metrics AS P ON C.cust_adv_id = P.cust_adv_id) ON E.ee_id =
P.ee_id) INNER JOIN funding_history AS F ON P.proj_exec_id = F.proj_exec_id)
ON M.proj_mgr_id = P.proj_mgr_id) ON D.DateID = F.DateID

WHERE (((D.DateReport)=(SELECT Max(X.DateReport) FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0 )));

Thanks again

OMG, that's enough to give anyone a headache ;-)

The first thing I would do with that thing is alias all the
tables to a (very) short name just to make it easier to
read.

I think that the problem is caused by your use of the GROUP
BY clause. I can't find anywhere where you use an
aggregate function (Count, Sum, etc) so I see no reason for
you to use GROUP BY. Using GROUP BY also causes the QBE to
put all the criteria in a HAVING clause, which is at worst
incorrect and at best inefficient. In your case it is
incorrect.

Unless you have some convoluted reason for using a Totals
type query, I think you might be ok if you just unclick the
Totals button on the Query Design ToolBar.
--
Marsh
MVP [MS Access]

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
)

 

[Marshall Barton]

Yes, I certainly agree! I thank you for being patient with me and trying to
help me work this out. I am certainly learning a lot but have a long way to
go.

I tried using the alias and took off the grouping but I still only get the
records that match what was put in the combo box, so I am still missing the
latest records of those projects that dont match the combo box selection.

SQL View
SELECT M.proj_mgr_branch, P.project_title, M.proj_mgr_name,
P.proj_risk_level, C.cust_adv_name, E.ee_description, F.planned_funding,
F.bcwp, F.bcws, F.acwp, [bcwp]/[bcws] AS Expr1, [BCWP]/[ACWP] AS Expr2,
F.method_of_calculation, F.comments, F.spi_narrative, F.cpi_narrative,
F.carryover_funds

FROM tblDateSubmitted AS D INNER JOIN (project_manager AS M INNER JOIN
((enterprise_executive AS E INNER JOIN (customer_advocate AS C INNER JOIN
project_execution_metrics AS P ON C.cust_adv_id = P.cust_adv_id) ON E.ee_id =
P.ee_id) INNER JOIN funding_history AS F ON P.proj_exec_id = F.proj_exec_id)
ON M.proj_mgr_id = P.proj_mgr_id) ON D.DateID = F.DateID

WHERE (((D.DateReport)=(SELECT Max(X.DateReport) FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0 )));

Well, that is certainly easier to read, but I don't see
anything wrong with it.

Now I am wondering if all those INNER JOINs are dropping the
records you want to see. Try copying the query to a new
query and removing the criteria. Then check the records it
returns to make sure the records you are looking for are
really there.

 

[Guest]

I copied the query and took out the below criteria. I get 7 total records,
which is all the records. 4 of the records have a date of 9/28/2007 and 3
have a date of 10/10/2007, so when I run the query for 10/10/2007 I would
want to see all 7 records but when I run the query for 9/28/2007 I should see
4 records.


Criteria removed
WHERE (((D.DateReport)=(SELECT Max(X.DateReport)
FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0 )));


Thanks

Yes, I certainly agree! I thank you for being patient with me and trying to
help me work this out. I am certainly learning a lot but have a long way to
go.

I tried using the alias and took off the grouping but I still only get the
records that match what was put in the combo box, so I am still missing the
latest records of those projects that dont match the combo box selection.

SQL View
SELECT M.proj_mgr_branch, P.project_title, M.proj_mgr_name,
P.proj_risk_level, C.cust_adv_name, E.ee_description, F.planned_funding,
F.bcwp, F.bcws, F.acwp, [bcwp]/[bcws] AS Expr1, [BCWP]/[ACWP] AS Expr2,
F.method_of_calculation, F.comments, F.spi_narrative, F.cpi_narrative,
F.carryover_funds

FROM tblDateSubmitted AS D INNER JOIN (project_manager AS M INNER JOIN
((enterprise_executive AS E INNER JOIN (customer_advocate AS C INNER JOIN
project_execution_metrics AS P ON C.cust_adv_id = P.cust_adv_id) ON E.ee_id =
P.ee_id) INNER JOIN funding_history AS F ON P.proj_exec_id = F.proj_exec_id)
ON M.proj_mgr_id = P.proj_mgr_id) ON D.DateID = F.DateID

WHERE (((D.DateReport)=(SELECT Max(X.DateReport) FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0 )));

Well, that is certainly easier to read, but I don't see
anything wrong with it.

Now I am wondering if all those INNER JOINs are dropping the
records you want to see. Try copying the query to a new
query and removing the criteria. Then check the records it
returns to make sure the records you are looking for are
really there.

 

[Marshall Barton]

I copied the query and took out the below criteria. I get 7 total records,
which is all the records. 4 of the records have a date of 9/28/2007 and 3
have a date of 10/10/2007, so when I run the query for 10/10/2007 I would
want to see all 7 records but when I run the query for 9/28/2007 I should see
4 records.

Criteria removed
WHERE (((D.DateReport)=(SELECT Max(X.DateReport)
FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0 )));

I don't know where such a big blind spot came from, but I
completely missed a big part of the subquery's criteria. We
definitely need to add a condition to relate the main query
and subquery records. I'm not sure if the DateID field is
the right one, but try using:

WHERE D.DateReport=(SELECT Max(X.DateReport)
FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0
And X.DateID = D.DateID)

 

[Guest]

I think that did it. It appears to be working properly now. Thank you so much
all your help!

 

[Guest]

I thought this was working properly but it is not. It gives me all the
records now versus just the latest. For instance, if I run that query and
select 10/10/2007, it correctly shows me 2 records from 9/28 (because those
program manager didnt input date for 10/10/2007) and then it shows me two
project records from 10/10/2007 from those program managers who did input
10/10/2007 data but it also shows me their 9/28/2007 submissions as well. So
in the case of the match with the combo box selection here it seems to be
pulling all records versus just the matching record. Below is the SQL. I
appreciate any help you can provide.

SELECT M.proj_mgr_branch AS [Project Manager Branch], P.project_title AS
Project, M.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)], [bcwp]/[bcws] AS SPI, [BCWP]/[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)], D.DateReport
FROM tblDateSubmitted AS D INNER JOIN (project_manager AS M INNER JOIN
((enterprise_executive AS E INNER JOIN (customer_advocate AS C INNER JOIN
project_execution_metrics AS P ON C.cust_adv_id=P.cust_adv_id) ON
E.ee_id=P.ee_id) INNER JOIN funding_history AS F ON
P.proj_exec_id=F.proj_exec_id) ON M.proj_mgr_id=P.proj_mgr_id) ON
D.DateID=F.DateID
WHERE D.DateReport=(SELECT Max(X.DateReport)
FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0
And X.DateID = D.DateID);

 

[Marshall Barton]

I thought this was working properly but it is not. It gives me all the
records now versus just the latest. For instance, if I run that query and
select 10/10/2007, it correctly shows me 2 records from 9/28 (because those
program manager didnt input date for 10/10/2007) and then it shows me two
project records from 10/10/2007 from those program managers who did input
10/10/2007 data but it also shows me their 9/28/2007 submissions as well. So
in the case of the match with the combo box selection here it seems to be
pulling all records versus just the matching record. Below is the SQL. I
appreciate any help you can provide.

SELECT M.proj_mgr_branch AS [Project Manager Branch], P.project_title AS
Project, M.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)], [bcwp]/[bcws] AS SPI, [BCWP]/[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)], D.DateReport
FROM tblDateSubmitted AS D INNER JOIN (project_manager AS M INNER JOIN
((enterprise_executive AS E INNER JOIN (customer_advocate AS C INNER JOIN
project_execution_metrics AS P ON C.cust_adv_id=P.cust_adv_id) ON
E.ee_id=P.ee_id) INNER JOIN funding_history AS F ON
P.proj_exec_id=F.proj_exec_id) ON M.proj_mgr_id=P.proj_mgr_id) ON
D.DateID=F.DateID
WHERE D.DateReport=(SELECT Max(X.DateReport)
FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0
And X.DateID = D.DateID);

All those joins are restricting the query's records far more
than the data in the subquery.

I suggest that you try doing this in two steps. First
remove the entire WHERE clause from your query.

Then create a new query based on your complicated query and
use a similar WHERE clause in the new query:

SELECT yourQuery.*
FROM yourquery
WHERE yourquery.DateReport=(SELECT Max(X.DateReport)
FROM yourquery As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0
And X.DateID = yourquery.DateID)

 

[Guest]

Thank you, I was able to get the query working properly. Actually after
looking at it, I realized I should have been using proj_exec_id and
fund_hist_id to relate versus the date id. Thanks for your help, I wouldnt
have been able to figure it out without the information you provided getting
me on the right track.

I thought this was working properly but it is not. It gives me all the
records now versus just the latest. For instance, if I run that query and
select 10/10/2007, it correctly shows me 2 records from 9/28 (because those
program manager didnt input date for 10/10/2007) and then it shows me two
project records from 10/10/2007 from those program managers who did input
10/10/2007 data but it also shows me their 9/28/2007 submissions as well. So
in the case of the match with the combo box selection here it seems to be
pulling all records versus just the matching record. Below is the SQL. I
appreciate any help you can provide.

SELECT M.proj_mgr_branch AS [Project Manager Branch], P.project_title AS
Project, M.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)], [bcwp]/[bcws] AS SPI, [BCWP]/[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)], D.DateReport
FROM tblDateSubmitted AS D INNER JOIN (project_manager AS M INNER JOIN
((enterprise_executive AS E INNER JOIN (customer_advocate AS C INNER JOIN
project_execution_metrics AS P ON C.cust_adv_id=P.cust_adv_id) ON
E.ee_id=P.ee_id) INNER JOIN funding_history AS F ON
P.proj_exec_id=F.proj_exec_id) ON M.proj_mgr_id=P.proj_mgr_id) ON
D.DateID=F.DateID
WHERE D.DateReport=(SELECT Max(X.DateReport)
FROM tblDateSubmitted As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0
And X.DateID = D.DateID);

All those joins are restricting the query's records far more
than the data in the subquery.

I suggest that you try doing this in two steps. First
remove the entire WHERE clause from your query.

Then create a new query based on your complicated query and
use a similar WHERE clause in the new query:

SELECT yourQuery.*
FROM yourquery
WHERE yourquery.DateReport=(SELECT Max(X.DateReport)
FROM yourquery As X
WHERE X.DateReport <= Forms!frmRptQuery!Combo0
And X.DateID = yourquery.DateID)