Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Subquery question?

Discussion in 'Information Technology' started by Robert Suffecool, Jul 28, 2006.

  1. Robert Suffecool

    Robert Suffecool
    Expand Collapse
    Guest

    Hi everyone... I'm not sure if I'm asking the correct question here or not
    but here goes anything...

    I have a table in Access with the following columns: ID, Month, Total,
    Allez, R900. ID is the primary key. Here is the sample of the data:

    temp ID Month Total Allez R900
    1 2006-02 157.2
    157.2
    2 2006-03 224.7
    224.7
    3 2006-04 401.6 235.6 166
    4 2006-05 508.8 27.8 481
    5 2006-06 251
    251


    I would like to create a query that calculates a running total of the
    "total" column. For example, a running total for the first record is
    157.2... but for the 2nd, it would be 381.9 (157.2 + 224.7)... the third
    record would be 783.5, etc...etc...etc...

    I was told I should use subqueries but I'm not sure how to do that. Could
    someone help?

    Thanks,

    Rob
     
  2. Loading...

    Similar Threads Forum Date
    Question About GGS Questions and Answers Aug 28, 2016
    Marriage And Discrimination Related Questions Blogs Mar 21, 2016
    Sikhi Questioning Faith Sikh Sikhi Sikhism Mar 3, 2016
    Entering into Sikhism: Questions on Reht Maryada. Blogs Oct 22, 2015
    A question about kes/hair? Blogs Oct 21, 2015

  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Rob:

    What you do is use the subquery to sum the value of the Total column in all
    rows where the value of the ID column is less than or equal to the current
    row's ID value. The subquery goes in the outer query's SELECT clause and the
    two instances of the table are distinguished by giving each a different
    alias, e.g.

    SELECT *,
    (SELECT SUM(Total)
    FROM YourTable AS T2
    WHERE T2.ID <= T1.ID) AS [Running Total]
    FROM YourTable AS T1
    ORDER BY ID;

    This query won't be updatable, however, as in Access any query which
    includes an SQL aggregate function is not updatable. If you need it to be
    updatable you can use the VBA DSum function instead of the subquery:

    SELECT *,
    DSUM("Total","YourTable", "ID <=" & ID) AS [Running Total]
    FROM YourTable
    ORDER BY ID;

    Ken Sheridan
    Stafford, England

    "Robert Suffecool" wrote:

    > Hi everyone... I'm not sure if I'm asking the correct question here or not
    > but here goes anything...
    >
    > I have a table in Access with the following columns: ID, Month, Total,
    > Allez, R900. ID is the primary key. Here is the sample of the data:
    >
    > temp ID Month Total Allez R900
    > 1 2006-02 157.2
    > 157.2
    > 2 2006-03 224.7
    > 224.7
    > 3 2006-04 401.6 235.6 166
    > 4 2006-05 508.8 27.8 481
    > 5 2006-06 251
    > 251
    >
    >
    > I would like to create a query that calculates a running total of the
    > "total" column. For example, a running total for the first record is
    > 157.2... but for the 2nd, it would be 381.9 (157.2 + 224.7)... the third
    > record would be 783.5, etc...etc...etc...
    >
    > I was told I should use subqueries but I'm not sure how to do that. Could
    > someone help?
    >
    > Thanks,
    >
    > Rob
    >
    >
    >
     
  4. rms

    rms
    Expand Collapse
    Guest

    Thanks!

    I have one more question about using subqueries... suppose you have a simple
    checkbook table with the following entries:

    ID Date Who Debit Credit

    1 5/1/06 Paycheck (null) $755.00
    2 6/9/06 Debit Trans $56.89 (null)
    3 6/10/06 ATM With $60.00 (null)

    and I use the following SQL Statement:

    SELECT T1.Date, T1.Who, T1.Debit, T1.Credit, ((SELECT SUM(Credit) FROM
    Table1 AS T2 WHERE T2.Date <= T1.Date)-(SELECT SUM(Debit) FROM Table1 AS T3
    WHERE T3.Date <= T1.Date)) AS Balance
    FROM Table1 AS T1
    ORDER BY T1.Date, T1.Credit DESC;

    why does the query results shown as below in Access (i.e., why is the
    Balance (null) for the paycheck record; shouldn't it be $755.00)?

    Date Who Debit Credit Balance
    5/1/06 Paycheck (null) $755.00 (null)
    6/9/06 Debit Trans $59.89 (null) $698.11
    6/10/06 ATM With $60.00 (null) $638.11

    Sincerely,

    Rob

    "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
    news:AD19988B-79CC-4C32-9B9D-BE670C4AB394@microsoft.com...
    > Rob:
    >
    > What you do is use the subquery to sum the value of the Total column in
    > all
    > rows where the value of the ID column is less than or equal to the current
    > row's ID value. The subquery goes in the outer query's SELECT clause and
    > the
    > two instances of the table are distinguished by giving each a different
    > alias, e.g.
    >
    > SELECT *,
    > (SELECT SUM(Total)
    > FROM YourTable AS T2
    > WHERE T2.ID <= T1.ID) AS [Running Total]
    > FROM YourTable AS T1
    > ORDER BY ID;
    >
    > This query won't be updatable, however, as in Access any query which
    > includes an SQL aggregate function is not updatable. If you need it to be
    > updatable you can use the VBA DSum function instead of the subquery:
    >
    > SELECT *,
    > DSUM("Total","YourTable", "ID <=" & ID) AS [Running Total]
    > FROM YourTable
    > ORDER BY ID;
    >
    > Ken Sheridan
    > Stafford, England
    >
    > "Robert Suffecool" wrote:
    >
    >> Hi everyone... I'm not sure if I'm asking the correct question here or
    >> not
    >> but here goes anything...
    >>
    >> I have a table in Access with the following columns: ID, Month, Total,
    >> Allez, R900. ID is the primary key. Here is the sample of the data:
    >>
    >> temp ID Month Total Allez R900
    >> 1 2006-02 157.2
    >> 157.2
    >> 2 2006-03 224.7
    >> 224.7
    >> 3 2006-04 401.6 235.6 166
    >> 4 2006-05 508.8 27.8 481
    >> 5 2006-06 251
    >> 251
    >>
    >>
    >> I would like to create a query that calculates a running total of the
    >> "total" column. For example, a running total for the first record is
    >> 157.2... but for the 2nd, it would be 381.9 (157.2 + 224.7)... the third
    >> record would be 783.5, etc...etc...etc...
    >>
    >> I was told I should use subqueries but I'm not sure how to do that.
    >> Could
    >> someone help?
    >>
    >> Thanks,
    >>
    >> Rob
    >>
    >>
    >>

    >
     
  5. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Rob:

    This results from the nature of NULL. The key thing to understand about
    NULL is that its not a value, but the absence of a value. About the nearest
    you can get to defining what means is that its an unknown. Arising form this
    NULLs propagate, i.e. any arithmetical expression which includes a NULL will
    evaluate to a NULL. This makes sense as, for instance, 10 + an unknown
    quantity is not 1, but also an unknown quantity as the unknown quantity could
    be anything from –infinity to infinity, so the result could also be anything,
    i.e. NULL.

    With the first row in your table the subquery which sums the debits is a
    summation of the one row, where the value is NULL, so the query returns NULL.
    When this is subtracted from the result of the first subquery, the summation
    of the credits, the result is again NULL because of the propagation of NULL.

    When you come to the second row in the table (I'm speaking loosely here
    because tables are actually sets so have no intrinsic order), however, the
    SUM operator is clever enough to ignore any NULLs in the rows being summed,
    so as the are values for both the credit and debit columns being summed the
    result of the summations are not NULL and you get the correct values for the
    balances.

    The problem stems in your case form the fact that NULLs are allowed in the
    credit and debit columns. With financial data like this NULLs should not be
    allowed. This is done by setting the column's Required property to True in
    the table design (the Required property is the equivalent of the NOT NULL
    constraint in SQLs data definition language (DDL)). The DefaultValue of each
    column should be set to zero. Before you can do this you need to change the
    NULLs to zeros in the table, which can be done with the following UPDATE
    query:

    UPDATE Checkbook
    SET Debit = NZ(Debit,0), Credit = NZ(Credit,0);

    The NZ function returns a zero where the credit or debit is NULL.

    Another feature of NULLs is that any comparative expression involving a NULL
    evaluates to NULL, for the reasons given above. Even NULL = NULL evaluates to
    NULL. For this reason toy can't test for NULL in query with WHERE SomeField
    = NULL. Instead you use WHERE SomeField IS NULL. In VBA you can use the
    IsNull function, e.g. If IsNull(SomeField) Then.

    At the theoretical level of the databse relational model a NULL at any
    column position in any row in a table is in fact prohibited by the model. In
    the relational model all column positions in all tables must contain a
    legitimate value of the attribute type which the column represents. As NULL
    is not a value its use in a column in a table violates this principle. In
    reality NULLs are often used but as they are semantically ambiguous care
    should be exercised in deciding when to allow NULLs. Say you had a
    CreditRating column in a table of Customers. Would a NULL CreditRating mean
    the customer is allowed zero credit, unlimited credit or what? There is
    absolutely no way of knowing this from the NULL per se, it’s a matter of
    interpretation.

    Ken Sheridan
    Stafford, England

    "rms" wrote:

    > Thanks!
    >
    > I have one more question about using subqueries... suppose you have a simple
    > checkbook table with the following entries:
    >
    > ID Date Who Debit Credit
    >
    > 1 5/1/06 Paycheck (null) $755.00
    > 2 6/9/06 Debit Trans $56.89 (null)
    > 3 6/10/06 ATM With $60.00 (null)
    >
    > and I use the following SQL Statement:
    >
    > SELECT T1.Date, T1.Who, T1.Debit, T1.Credit, ((SELECT SUM(Credit) FROM
    > Table1 AS T2 WHERE T2.Date <= T1.Date)-(SELECT SUM(Debit) FROM Table1 AS T3
    > WHERE T3.Date <= T1.Date)) AS Balance
    > FROM Table1 AS T1
    > ORDER BY T1.Date, T1.Credit DESC;
    >
    > why does the query results shown as below in Access (i.e., why is the
    > Balance (null) for the paycheck record; shouldn't it be $755.00)?
    >
    > Date Who Debit Credit Balance
    > 5/1/06 Paycheck (null) $755.00 (null)
    > 6/9/06 Debit Trans $59.89 (null) $698.11
    > 6/10/06 ATM With $60.00 (null) $638.11
    >
    > Sincerely,
    >
    > Rob
     
  6. Robert

    Robert
    Expand Collapse
    Guest

    thanks for the detailed clarification. I've always assumed NULL = 0 ... :-(

    in the future, I'll be sure to test in vba using the isnull function. :)

    rob


    "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
    news:181D413C-87B9-46F0-9E83-48623651EE86@microsoft.com...
    > Rob:
    >
    > This results from the nature of NULL. The key thing to understand about
    > NULL is that its not a value, but the absence of a value. About the
    > nearest
    > you can get to defining what means is that its an unknown. Arising form
    > this
    > NULLs propagate, i.e. any arithmetical expression which includes a NULL
    > will
    > evaluate to a NULL. This makes sense as, for instance, 10 + an unknown
    > quantity is not 1, but also an unknown quantity as the unknown quantity
    > could
    > be anything from -infinity to infinity, so the result could also be
    > anything,
    > i.e. NULL.
    >
    > With the first row in your table the subquery which sums the debits is a
    > summation of the one row, where the value is NULL, so the query returns
    > NULL.
    > When this is subtracted from the result of the first subquery, the
    > summation
    > of the credits, the result is again NULL because of the propagation of
    > NULL.
    >
    > When you come to the second row in the table (I'm speaking loosely here
    > because tables are actually sets so have no intrinsic order), however, the
    > SUM operator is clever enough to ignore any NULLs in the rows being
    > summed,
    > so as the are values for both the credit and debit columns being summed
    > the
    > result of the summations are not NULL and you get the correct values for
    > the
    > balances.
    >
    > The problem stems in your case form the fact that NULLs are allowed in the
    > credit and debit columns. With financial data like this NULLs should not
    > be
    > allowed. This is done by setting the column's Required property to True
    > in
    > the table design (the Required property is the equivalent of the NOT NULL
    > constraint in SQLs data definition language (DDL)). The DefaultValue of
    > each
    > column should be set to zero. Before you can do this you need to change
    > the
    > NULLs to zeros in the table, which can be done with the following UPDATE
    > query:
    >
    > UPDATE Checkbook
    > SET Debit = NZ(Debit,0), Credit = NZ(Credit,0);
    >
    > The NZ function returns a zero where the credit or debit is NULL.
    >
    > Another feature of NULLs is that any comparative expression involving a
    > NULL
    > evaluates to NULL, for the reasons given above. Even NULL = NULL evaluates
    > to
    > NULL. For this reason toy can't test for NULL in query with WHERE
    > SomeField
    > = NULL. Instead you use WHERE SomeField IS NULL. In VBA you can use the
    > IsNull function, e.g. If IsNull(SomeField) Then.
    >
    > At the theoretical level of the databse relational model a NULL at any
    > column position in any row in a table is in fact prohibited by the model.
    > In
    > the relational model all column positions in all tables must contain a
    > legitimate value of the attribute type which the column represents. As
    > NULL
    > is not a value its use in a column in a table violates this principle. In
    > reality NULLs are often used but as they are semantically ambiguous care
    > should be exercised in deciding when to allow NULLs. Say you had a
    > CreditRating column in a table of Customers. Would a NULL CreditRating
    > mean
    > the customer is allowed zero credit, unlimited credit or what? There is
    > absolutely no way of knowing this from the NULL per se, it's a matter of
    > interpretation.
    >
    > Ken Sheridan
    > Stafford, England
    >
    > "rms" wrote:
    >
    >> Thanks!
    >>
    >> I have one more question about using subqueries... suppose you have a
    >> simple
    >> checkbook table with the following entries:
    >>
    >> ID Date Who Debit Credit
    >>
    >> 1 5/1/06 Paycheck (null) $755.00
    >> 2 6/9/06 Debit Trans $56.89 (null)
    >> 3 6/10/06 ATM With $60.00 (null)
    >>
    >> and I use the following SQL Statement:
    >>
    >> SELECT T1.Date, T1.Who, T1.Debit, T1.Credit, ((SELECT SUM(Credit) FROM
    >> Table1 AS T2 WHERE T2.Date <= T1.Date)-(SELECT SUM(Debit) FROM Table1 AS
    >> T3
    >> WHERE T3.Date <= T1.Date)) AS Balance
    >> FROM Table1 AS T1
    >> ORDER BY T1.Date, T1.Credit DESC;
    >>
    >> why does the query results shown as below in Access (i.e., why is the
    >> Balance (null) for the paycheck record; shouldn't it be $755.00)?
    >>
    >> Date Who Debit Credit Balance
    >> 5/1/06 Paycheck (null) $755.00 (null)
    >> 6/9/06 Debit Trans $59.89 (null) $698.11
    >> 6/10/06 ATM With $60.00 (null) $638.11
    >>
    >> Sincerely,
    >>
    >> Rob

    >
     

Share This Page