Refining query

[SMT]

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

 

[Jerry Whittle]

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.

 

[SMT]

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

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.

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

 

[Gary Walter]

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

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

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.

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

 

[SMT]

Hello,

Thanks for the reponse. I tried the top code. I selected 3/28 in my
Forms!frmRptQuery!Combo0 and it only pulled two records which had a
completed date of 4/11/08. I need to show every project (record) and for
those that do not have any input for 3/28 I need to still show the latest
record for that project and for those that have a completed date filled in I
want to show the latest record for those that have a completed date but not
more than 14 days past the date chosen in the Forms!frmRptQuery!Combo0. Does
that help?

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

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

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.


:

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

 

[Gary Walter]

Hi SMT,

{meaning no offense -- rough week}

Have you ever heard the joke about Bob
who came into the room where his two buds
were laughing hysterically.

One would say "Three" and they both would
explode in laughter briefly.

Then the other would say "Fourteen" and they'd burst
out again.

This went on for awhile as each would spurt out a different
number.

Finally, Bob asked what was so funny.

They explained that they had told the same jokes
so many times that finally they decided to just give
each one a number to save time.

So, wanting to join in the fun, Bob shouted out "Ten."

They sobered up and just looked at him.

Then one said to other shaking his head,

"Bob just can't tell a joke."

SMT...(again -- meaning no offense)...
you just can't tell a problem.

Or maybe I'm too stupid to understand it.

Each time I try to read this, I feel myself
going around in "logical circles."

If you take away the WHERE clause, this
is the "big set" you want to pull records from right?

qryBigSet

SELECT
P.proj_exec_id,
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;

{note: I added "P.proj_exec_id" to qryBigSet}


"I need to show every project"

so...if I were to create a DISTINCT query of "qryBigSet" on fields
from P (and maybe PM) and create a report based on this query,
this would show every project as you wanted?

"and for those that do not have any input for 3/28 I need to still show the
latest
record for that project "

so...if I were to create a subreport based on query on "qryBigSet" that gave
every P.proj_mgr_id with its "non P/PM" data using (say) your original WHERE
clause, and you "Master/Child" on P.proj_mgr_id, that subreport would show
you
latest data for that project on or before 3/28?

"and for those that have a completed date filled in I want to show
the latest record for those that have a completed date but not
more than 14 days past the date chosen in the Forms!frmRptQuery!Combo0"

I'm sorry, this is where I "cannot get your joke."

I might create one more subreport based on query on "qryBigSet" that gave
every P.proj_mgr_id with its "non P/PM" data using (maybe) the WHERE clause
I
gave you, again "Master/Child" on P.proj_mgr_id, make sure it can shrink,
then stop and print out report, and discuss with my boss.

AGAIN -- meant no offense by joke...

Thanks for the reponse. I tried the top code. I selected 3/28 in my
Forms!frmRptQuery!Combo0 and it only pulled two records which had a
completed date of 4/11/08. I need to show every project (record) and for
those that do not have any input for 3/28 I need to still show the latest
record for that project and for those that have a completed date filled in I
want to show the latest record for those that have a completed date but not
more than 14 days past the date chosen in the Forms!frmRptQuery!Combo0. Does
that help?

<snip>

 

[Gary Walter]

sorry, somehow in the typing I misspoke
on the Master/Child link... meant

P.proj_exec_id

which I assume was a main key to a project?

{meaning no offense -- rough week}

Have you ever heard the joke about Bob
who came into the room where his two buds
were laughing hysterically.

One would say "Three" and they both would
explode in laughter briefly.

Then the other would say "Fourteen" and they'd burst
out again.

This went on for awhile as each would spurt out a different
number.

Finally, Bob asked what was so funny.

They explained that they had told the same jokes
so many times that finally they decided to just give
each one a number to save time.

So, wanting to join in the fun, Bob shouted out "Ten."

They sobered up and just looked at him.

Then one said to other shaking his head,

"Bob just can't tell a joke."

SMT...(again -- meaning no offense)...
you just can't tell a problem.

Or maybe I'm too stupid to understand it.

Each time I try to read this, I feel myself
going around in "logical circles."

If you take away the WHERE clause, this
is the "big set" you want to pull records from right?

qryBigSet

SELECT
P.proj_exec_id,
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;

{note: I added "P.proj_exec_id" to qryBigSet}


"I need to show every project"

so...if I were to create a DISTINCT query of "qryBigSet" on fields
from P (and maybe PM) and create a report based on this query,
this would show every project as you wanted?

"and for those that do not have any input for 3/28 I need to still show the
latest
record for that project "

so...if I were to create a subreport based on query on "qryBigSet" that gave
every P.proj_mgr_id with its "non P/PM" data using (say) your original WHERE
clause, and you "Master/Child" on P.proj_mgr_id, that subreport would show
you
latest data for that project on or before 3/28?

"and for those that have a completed date filled in I want to show
the latest record for those that have a completed date but not
more than 14 days past the date chosen in the Forms!frmRptQuery!Combo0"

I'm sorry, this is where I "cannot get your joke."

I might create one more subreport based on query on "qryBigSet" that gave
every P.proj_mgr_id with its "non P/PM" data using (maybe) the WHERE clause
I
gave you, again "Master/Child" on P.proj_mgr_id, make sure it can shrink,
then stop and print out report, and discuss with my boss.

AGAIN -- meant no offense by joke...

Thanks for the reponse. I tried the top code. I selected 3/28 in my
Forms!frmRptQuery!Combo0 and it only pulled two records which had a
completed date of 4/11/08. I need to show every project (record) and for
those that do not have any input for 3/28 I need to still show the latest
record for that project and for those that have a completed date filled

in

I
want to show the latest record for those that have a completed date but not
more than 14 days past the date chosen in the Forms!frmRptQuery!Combo0. Does
that help?

<snip>

 

[SMT]

Good morning, so I will try again

I am using this query to export directly to an excel file. The original
query worked perfectly until I had an additional requirement to limit the
projects to those that had an actual_complete_date of no more than 14 days
past the reporting date that was chosen in Forms!frmRptQuery!Combo0.


The user choses reporting date from Forms!frmRptQuery!Combo0.
The query shows the latest data for projects on or before the date chosen in
the Forms!frmRptQuery!Combo0.
One exception - If a project has an actual_complete_date greater than 14
days after the date selected in Forms!frmRptQuery!Combo0, I no longer want to
show that project in my query (export file).

I hope I am improving

Hi SMT,

{meaning no offense -- rough week}

Have you ever heard the joke about Bob
who came into the room where his two buds
were laughing hysterically.

One would say "Three" and they both would
explode in laughter briefly.

Then the other would say "Fourteen" and they'd burst
out again.

This went on for awhile as each would spurt out a different
number.

Finally, Bob asked what was so funny.

They explained that they had told the same jokes
so many times that finally they decided to just give
each one a number to save time.

So, wanting to join in the fun, Bob shouted out "Ten."

They sobered up and just looked at him.

Then one said to other shaking his head,

"Bob just can't tell a joke."

SMT...(again -- meaning no offense)...
you just can't tell a problem.

Or maybe I'm too stupid to understand it.

Each time I try to read this, I feel myself
going around in "logical circles."

If you take away the WHERE clause, this
is the "big set" you want to pull records from right?

qryBigSet

SELECT
P.proj_exec_id,
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;

{note: I added "P.proj_exec_id" to qryBigSet}


"I need to show every project"

so...if I were to create a DISTINCT query of "qryBigSet" on fields
from P (and maybe PM) and create a report based on this query,
this would show every project as you wanted?

"and for those that do not have any input for 3/28 I need to still show the
latest
record for that project "

so...if I were to create a subreport based on query on "qryBigSet" that gave
every P.proj_mgr_id with its "non P/PM" data using (say) your original WHERE
clause, and you "Master/Child" on P.proj_mgr_id, that subreport would show
you
latest data for that project on or before 3/28?

"and for those that have a completed date filled in I want to show
the latest record for those that have a completed date but not
more than 14 days past the date chosen in the Forms!frmRptQuery!Combo0"

I'm sorry, this is where I "cannot get your joke."

I might create one more subreport based on query on "qryBigSet" that gave
every P.proj_mgr_id with its "non P/PM" data using (maybe) the WHERE clause
I
gave you, again "Master/Child" on P.proj_mgr_id, make sure it can shrink,
then stop and print out report, and discuss with my boss.

AGAIN -- meant no offense by joke...

Thanks for the reponse. I tried the top code. I selected 3/28 in my
Forms!frmRptQuery!Combo0 and it only pulled two records which had a
completed date of 4/11/08. I need to show every project (record) and for
those that do not have any input for 3/28 I need to still show the latest
record for that project and for those that have a completed date filled in I
want to show the latest record for those that have a completed date but not
more than 14 days past the date chosen in the Forms!frmRptQuery!Combo0. Does
that help?

<snip>

 

[Gary Walter]

I am using this query to export directly to an excel file. The original
query worked perfectly until I had an additional requirement to limit the
projects to those that had an actual_complete_date of no more than 14 days
past the reporting date that was chosen in Forms!frmRptQuery!Combo0.


The user choses reporting date from Forms!frmRptQuery!Combo0.
The query shows the latest data for projects on or before the date chosen in
the Forms!frmRptQuery!Combo0.
One exception - If a project has an actual_complete_date greater than 14
days after the date selected in Forms!frmRptQuery!Combo0, I no longer want to
show that project in my query (export file).

Hi SMT,

There may be a better way, but..

with "comments":
{you'll need to delete them of course, but
if I get this wrong, maybe you'll see it}

WHERE
( 'start WHERE
( 'start first AND
F.DateID =
( 'start first subquery
SELECT Max(X.DateID)
FROM funding_history AS X
Where
X.DateID <= Forms!frmRptQuery!Combo0
AND
X.proj_exec_id=F.proj_exec_id
) 'end first subquery
) 'end first AND
AND
( 'start second AND
( 'start EQUAL stmt
F.proj_exec_id =
( 'start second subquery
SELECT Max(NZ(T.proj_exec_id, 0))
FROM funding_history AS T
WHERE
T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14
AND
T.proj_exec_id = F.proj_exec_id
) 'end second subquery
) 'end EQUAL stmt
= False
) 'end second AND
); 'end WHERE

====================

Why not just use?
{I'm 99.9% sure this would not be a good idea}

....
AND
F.proj_exec_id NOT IN
(SELECT T.proj_exec_id
FROM funding_history AS T
WHERE
T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14)


short answer -- NULL

For example, what if there are no projects
w/completedate 15+ days out when you run query,
so you have equivalent of

....
AND
F.proj_exec_id NOT IN NULL

I'm pretty sure that would evaluate to "False"
so your query would not return any records.

=========================

I do not use "form dates" in queries, so the following
piece from above may be wrong

T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14

it may have to be

T.actual_complete_date> CDate(Forms!frmRptQuery!Combo0) + 14

or

T.actual_complete_date> DateAdd("d",14,Forms!frmRptQuery!Combo0)

or

T.actual_complete_date> DateAdd("d",14,CDate(Forms!frmRptQuery!Combo0))

I don't know for sure because I always test if I
have valid form data, then assign to a variable(s),
then recreate SQL using variable(s),
then assign new SQL to stored query,
then use that stored query.

==============================
Are you running a report, then saving the report
to an Excel file? You can use SQL to create
a new Excel "table"

{from Jamie}


To create a new Excel table:

SELECT
MyCol1 AS ColA, MyCol2 AS ColB
INTO
[Excel 8.0;database=C:\Tempo\db.xls;].MyNewTable
FROM MyTable



Or create new sheet in existing xls:

SELECT
KeyCol AS MyKeyColumn,ValueCol AS MyDataColumn
INTO
[Excel 8.0;database=C:\MyWorkbook.xls].MyNewSheet
FROM MyTable

would add a new worksheet "MyNewSheet" to existing
"C:\MyWorkbook.xls" (if it not open).

 

[Gary Walter]

I am using this query to export directly to an excel file. The original
query worked perfectly until I had an additional requirement to limit the
projects to those that had an actual_complete_date of no more than 14 days
past the reporting date that was chosen in Forms!frmRptQuery!Combo0.


The user choses reporting date from Forms!frmRptQuery!Combo0.
The query shows the latest data for projects on or before the date chosen in
the Forms!frmRptQuery!Combo0.
One exception - If a project has an actual_complete_date greater than 14
days after the date selected in Forms!frmRptQuery!Combo0, I no longer want to
show that project in my query (export file).

Hi SMT,

further musings...

One reason to use aggregating, correlated subqueries
in the WHERE clause is to allow the query to be editable.
Otherwise, they are "expensive" in the sense that they have
to be evaluated for every record created by your JOIN's.

It appears to me that you do not wish to edit the query,
so, if "proj_exec_id" does indeed determine each
distinct project, I would like to propose a "divide-and-conquer"
method that uses the true power of relational dbs.

{untested!!}

qryNotCompletedInRange

SELECT
T.proj_exec_id
FROM funding_history AS T
WHERE
T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14
GROUP BY
T.proj_exec_id
HAVING
Count(T.proj_exec_id) = 0;

My thinking may be fuzzy (I'm not usually
up at this hour but could not sleep), but I
think the above query should identify all
projects that do not have a completion date
greater than 14 days after the combo date,
i.e., these are the projects you want in your
results.

So joining this query to your original query
on proj_exec_id should give results your boss
wants.

Likewise, you could also get the latest date
less than or equal to combo date for each
project.

qryLatestProjDateInRange

SELECT
X.proj_exec_id,
Max(X.DateID) AS LatestDate
FROM funding_history AS X
Where
X.DateID <= Forms!frmRptQuery!Combo0
GROUP BY
X.proj_exec_id;

So, if you eliminated the WHERE clause
in your original query, and instead added the
two "divide-and-conquer" queries (joining
the last query on proj_exec_id and date fields),
you will be "slicing-and-dicing" in the JOIN's
where the true power of relational db's dwell
(especially if id and date fields are indexed).

In fact, I might save your original query w/o
the WHERE clause, then combine these three
saved queries into your final "rpt" query.

well...done musing...

good luck,

gary

 

[Gary Walter]

well..I warned you that my thinking
might be fuzzy...and it was...


qryNotCompletedInRange

SELECT
T.proj_exec_id,
Min(T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14)
FROM funding_history AS T
GROUP BY
T.proj_exec_id
HAVING
Min(T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14)
= 0;

how do I "think" this works?

you have a group of records for a specific id.

if within that group, any record has a complete
date that is > combo date + 14, then the min
of that expression for that group will be -1.

if the expression evaluates to false for all
the records in that group for that id, then
the min will be 0.

those are the id's your boss wants.

It's still not very efficient though...
it has to evaluate the expression over
every single record in funding_history.

Again, further "divide-and-conquer"
would make it more efficient.

qryAllProjIDs

SELECT DISTINCT proj_exec_id
FROM funding_history;

qryCompletedInRange

SELECT
T.proj_exec_id
FROM funding_history AS T
WHERE
T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14;

qryNotCompletedInRange

SELECT
A.proj_exec_id
FROM
qryAllProjIDs AS A
LEFT JOIN
qryCompletedInRange AS C
ON
A.proj_exec_id = C.proj_exec_id
WHERE
C.proj_exec_id IS NULL;

of course all above untested and made
assumption that proj_exec_id determines
distinct projects.

good luck,

gary

 

[SMT]

Thank you and yes proj_exec_id determines distinct projects. I will give you
latest a try.