Re: Refining query

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


"Gary Walter" wrote:

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...

"SMT" wrote:>
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?


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

<snip>



.

Relevant Pages

  • Re: Refining query
    ... Have you ever heard the joke about Bob ... so...if I were to create a DISTINCT query of "qryBigSet" on fields ...
    (microsoft.public.access.queries)
  • Re: Refining query
    ... Have you ever heard the joke about Bob ... so...if I were to create a DISTINCT query of "qryBigSet" on fields ...
    (microsoft.public.access.queries)
  • Re: All hail Bob (not)!
    ... casual user to get a correct result for a simple query, you only confirm that you are an embecile. ... Much more curiously though your resort to bombastic posts (whilst a feeble trait IMO for anyone professing intellectual tendencies) seems to have recent CDTzens in thrall given the deafening silence to date. ... What is it with Bob though? ...
    (comp.databases.theory)
  • RE: Summing Fields
    ... query for the order details that match the order shown on the main form. ... shown on the subform. ... "Bob Love" wrote: ... I use a query to find all details that match the order # in the main form. ...
    (microsoft.public.access.gettingstarted)
  • Re: Manipulation of strings query
    ... > I have received the data in an excel spreadsheet which contains the ... The query then returns the names ... So modify the query so it returns the names in separate columns ... ... Bob Barrows ...
    (microsoft.public.inetserver.asp.general)
Loading