Access Query - Dev Shed

Dev Shed Forums Sponsor:

You don't need a fax machine to get faxes. Get a fax-to-email fax number from CallWave. Try it free.

February 17th, 2004, 05:01 AM

dspangenberg

Registered User

Join Date: Feb 2004

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Access Query

Hi all,

I'm having difficulties getting this query together.

I have a table cashflow containing several fields: ProjectID, Year, Cash_in Cash_out

Now if i have three records from One project eg:
IDprojectNr_FK, Year, Total_Cash_in, Total_CashOut
1, 2000, 200, 300
1, 2001, 400, 200
1, 2002, 400, 400
2, 2001, 2000, 3000
.. ...... ...... .......

Goal is to derive a Payback field that gives me the first year in which the project was payedback. So in the case of ProjectID 1 it would be year 2001
(200 + 400) > (300 + 200)
payback field = sum (Total_cash_in) >= sum(Total_cash_out)

but if i use the sum function it will calculate every record of IDprojectNR_FK 1
it has to stop where the criteria is met.

this is what i have so far:
SELECT tblCashflow.ID_ProjectNr_FK, Min(tblCashflow.Jaar) AS MinVanJaar, (Min([tblCashflow].[Jaar])-Min([tbl2].[jaar]))+1 AS payback
FROM tblCashflow, tblcashflow AS tbl2
WHERE (((tblCashflow.Total_cash_in)>[tblCashFlow].[Total_cash_out]) AND ((tbl2.ID_ProjectNr_FK)=[tblCashFlow].[ID_ProjectNR_FK]))
GROUP BY tblCashflow.ID_ProjectNr_FK, tbl2.ID_ProjectNr_FK;

But this does not work cause it checks if Total_cash_IN > Total_cash_out, Only per record!
and if i use the sum function then it checks it for every record. so that also does not work.
any ideas?

any help is appreciated.

Sincerely Yours,

D. Spangenberg

February 17th, 2004, 07:34 AM

r937

SQL Consultant

Dev Shed God 24th Plane (16500 - 16999 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,745

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 39 m 45 sec
Reputation Power: 870

if this were ms sql server, you would use

Code:

select dt.IDprojectNr_FK
     , min(dt.Jaar)
  from (   
       select tbl1.IDprojectNr_FK
            , tbl1.Jaar
            , sum(tbl2.Total_Cash_in) as SumIn
            , sum(tbl2.Total_CashOut) as SumOut
            , sum(tbl2.Total_Cash_in) 
            - sum(tbl2.Total_CashOut) as SumDiff
         from tblCashflow as tbl1
       inner
         join tblCashflow as tbl2 
           on tbl1.IDprojectNr_FK >= tbl2.IDprojectNr_FK
          and tbl1.Jaar >= tbl2.Jaar
       group 
           by tbl1.IDprojectNr_FK
            , tbl1.Jaar
       having sum(tbl2.Total_Cash_in) 
            > sum(tbl2.Total_CashOut)
       ) as dt
group
    by dt.IDprojectNr_FK

but since it's access you will have to save the subquery as a query, and then run the outer query from the saved query

__________________
r937.com | rudy.ca

February 18th, 2004, 02:32 AM

dspangenberg

Registered User

Join Date: Feb 2004

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Hi r937,

Thanks for the reply, unfortunately this solution does not work for me eg:
if i have the following table

Code:

IDProjectNR_FK,    Year,     Total_Cash_IN,   Total_Cash_out
1                        2000      200                  400
1                        2001       50                    25
1                        2002      400                  100

in this table the first year in which the table was paid back is: 2002 NOT 2001 cause it must be calculated over the cumulative cash_ins and outs

and your code selects the first year which meets the criteria cash_in > cash_out per record..

correct me if i'm wrong.

any other ideas?

Sincerly Yours D.Spangenberg

February 18th, 2004, 07:36 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,745

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 39 m 45 sec
Reputation Power: 870

Quote:

Originally Posted by dspangenberg

Thanks for the reply, unfortunately this solution does not work for me eg

oh, sorry, i made a typo

try this, i tested it:

Code:

select dt.IDprojectNr_FK
     , min(dt.Jaar)
  from (   
       select tbl1.IDprojectNr_FK
            , tbl1.Jaar
            , sum(tbl2.Total_Cash_in) as SumIn
            , sum(tbl2.Total_CashOut) as SumOut
            , sum(tbl2.Total_Cash_in) 
            - sum(tbl2.Total_CashOut) as SumDiff
         from tblCashflow as tbl1
       inner
         join tblCashflow as tbl2 
           on tbl1.IDprojectNr_FK = tbl2.IDprojectNr_FK
          and tbl1.Jaar >= tbl2.Jaar
       group 
           by tbl1.IDprojectNr_FK
            , tbl1.Jaar
       having sum(tbl2.Total_Cash_in) 
            > sum(tbl2.Total_CashOut)
       ) as dt
group
    by dt.IDprojectNr_FK

February 18th, 2004, 08:57 AM

dspangenberg

Registered User

Join Date: Feb 2004

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks a million! works like a charm.

Sincerely Yours

D.Spangenberg

Viewing: Dev Shed Forums > Databases > MS SQL Development > Access Query

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!