Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / November 2007

Tip: Looking for answers? Try searching our database.

Expression Help

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
nazzoli - 26 Nov 2007 14:12 GMT
I need help building an expression within a query or report.
I have 2 queries I am utilizing to create a new query.
The first query (Paid) houses this information:
    Month      Sum of Paid
      1                100
      2                 450
      3                 700
      .
      .
      12                200
Months are from 1-12.
The second query (Balance) houses this information
    Month        Balance of Account
     1                    1000
     2                    1000
     3                     5000
     .
     .
     12                   1000
Months are from 1-12.

I am looking to create a new query utilizing the above queries to create an
expression that will do the following by month
   Month               Percent
    1                       month1 balance/month 1 paid
    2                     sum of month1 and 2 balance/month 2 paid
    3                     sum of month 1,2 and 3 balance/month 3 paid
    .
    .
    12                  sum of months 1-12 balance/month 12 paid

Any help would be appreciated.
John Spencer - 26 Nov 2007 16:39 GMT
The SQL of the query should look something like the following.

SELECT Paid.Month
, (SELECT Sum({Balance Of Account])
  FROM Balance
  WHERE Balance.Month <= Paid.Month) as RunningBalance
,  (SELECT Sum({Balance Of Account])
  FROM Balance
  WHERE Balance.Month <= Paid.Month) / Paid.[Sum of Paid] as  Calculation
FROM Paid

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I need help building an expression within a query or report.
> I have 2 queries I am utilizing to create a new query.
[quoted text clipped - 29 lines]
>
> Any help would be appreciated.
nazzoli - 26 Nov 2007 19:40 GMT
I messed up on my calculation.  I need it to divide the Paid by the balance.

[sum of paid]/[balance of account]

> The SQL of the query should look something like the following.
>
[quoted text clipped - 40 lines]
> >
> > Any help would be appreciated.
John Spencer - 26 Nov 2007 19:57 GMT
The SQL then might look like the following

SELECT Balance.Month
, (SELECT Sum([Sum of Paid])
  FROM Paid
  WHERE Paid.Month <=Balance.Month)  /
        Balance.[Balance of Account]  as Calculation
FROM Balance

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I messed up on my calculation.  I need it to divide the Paid by the
>balance.
[quoted text clipped - 47 lines]
>> >
>> > Any help would be appreciated.
nazzoli - 26 Nov 2007 20:37 GMT
The SQL you wrote before but I explained myself backwards.  Here is How I
need it written.

Month        Percent
1                 month 1 paid/ sum of month 1-12 balance
2                 month 2 paid/ sum of monht 1-11 balance
3                 month 3 paid/ sum of month 1-10 balance
.
.
12               month 12 paid/ month 1 balance

Any help would be appreciated.
Thanks.

> The SQL then might look like the following
>
[quoted text clipped - 56 lines]
> >> >
> >> > Any help would be appreciated.
John Spencer - 26 Nov 2007 20:45 GMT
This is my last try.  Good Luck

SELECT Paid.Month
, (SELECT Sum({Balance Of Account])
  FROM Balance
  WHERE Balance.Month <= 13-Paid.Month) as RunningBalance
,   Paid.[Sum of Paid] /
  (SELECT Sum({Balance Of Account])
  FROM Balance
  WHERE Balance.Month <= 13-Paid.Month)as  Calculation
FROM Paid

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> The SQL you wrote before but I explained myself backwards.  Here is How I
> need it written.
[quoted text clipped - 71 lines]
>> >> >
>> >> > Any help would be appreciated.
nazzoli - 26 Nov 2007 21:54 GMT
Perfect
Thank you.

> This is my last try.  Good Luck
>
[quoted text clipped - 83 lines]
> >> >> >
> >> >> > Any help would be appreciated.
Dale Fye - 26 Nov 2007 17:14 GMT
How would you use such a statistic?  

I don't understand the utility of a statistic that sums all of the previous
months balances divided by the current months payment?  I could understand
doing it the other way around, all of the previous months payments, divided
by the current balance.

Dale
Signature

Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

> I need help building an expression within a query or report.
> I have 2 queries I am utilizing to create a new query.
[quoted text clipped - 30 lines]
>
>              
nazzoli - 26 Nov 2007 18:28 GMT
Actually yes, it should be that way.  I mistyped the formula I was looking
for.  It should be Payments/Balance.

> How would you use such a statistic?  
>
[quoted text clipped - 39 lines]
> >
> >              
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.