Subquery question?

  • Thread starter Thread starter Robert Suffecool
  • Start date Start date
R

Robert Suffecool

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
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
 
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
 
Back
Top