Wendy:
Before we look at your specific problem it might help if we examine a few
general concepts. Firstly what do we mean by NULL? Well, the first thing to
understand is that NULL is not a value, so to talk of a NULL value is really
a contradiction in terms, although, just to muddy the waters, its true that
the Value property of an object can be NULL. A NULL is really the absence
of a value. About the nearest we can get to saying what its means is that
its an 'unknown'. Zero on the other hand is most definitely a value.
Because a NULL is not a value it behaves rather strangely. We say that NULL
'propagates' for instance. This means that any arithmetical expression
invoving a NULL will always result in NULL whatever the other value are, so
10 + NULL = NULL, 100 + NULL = NULL, 25 * NULL = NULL and so on. Often we
want to use another value if something is NULL, which is where the Nz
function comes in. This returns a value in place of a NULL or the actual
value if something is not NULL, e.g. if MyField is NULL then Nz([MyField],0)
returns 0, but if MyField is 42 then Nz([MyField],0) returns 42.
NULLs also behave a little strangely in comparative operations if we have a
criterion of MyField < 123 say and MyField is NULL then the answer is not
True or False, but, you've guessed it, NULL. This makes sense oif you think
about it because, if NULL is an 'unknown', then the answer if we compare NULL
with any value must also be 'unknown', i.e. NULL. The field which is NULL
could be less than 123, more than 123 or it could equal 123; we just don't
know. This does mean we need to be careful with NULLS. What would a NULL
credit limit for a customer mean? Zero credit? Unlimited credit? There is
no way of knowing. With currency data its more often than not best not to
allow NULLs and to give fields a DefaultValue of 0.
So much for NULLs. Turning to table design in a relational database like
Access, your statement: "I have two fields.. One is this months data and the
other is the previous data." does set my antennae twitching a bit. It sounds
to me that you might, by having separate columns for each month, be doing
what's known as 'encoding data as column headings'. This is against a
fundamental principle of relational database design which requires that data
only be stored as values at column positions in rows in tables. Having
columns for each month is storing two data values 'this month' and 'last
month' as the column headings. A correctly design table would have the
values for each month in separate rows, with columns to indicate the month
and the amount. I'll come back to this below.
In a relational database tables rep[resent 'entity types' and their columns
represent 'attributes types' of the entity type. FirstName and LastName
might be attribute types of a Customers entity type for instance. The
columns in a table should represent attribute types specific to that entity
type, so as to avoid any redundancy. OrderNumber would not be an attribute
type of a Customers entity type for example, as you'd have to have separate
rows in the Customers table for every order placed by that customer, so there
would be a lot of repetition of FirstName, LastName etc. and the possibility
of inconsistencies. Instead you'd have an Orders table with a CustomerID
foreign key column which references the primary key of Customers. This is a
one-to-may relationship type but sometimes relationship types can be
many-to-many, e.g. Orders to products as each order can be for more than one
product, and ech product can be included in more than one order. In a case
like this the relationship type is represented by another table, OrderDetails
with columns OrderID, and ProductID, these being foreign keys referencing the
primary keys of Orders and Products, along with columns such as UnitPrice,
Quantity etc which are specific to each order detail.
Applying these general principles to your scenario, lets assume a simple
hypothetical situation where you have accounts identified by unique AccountID
values and columns such as AccountName, Closed (a Yes/No column to indicate
when an account is closed etc. So you'd have an Accounts table with columns
for these attribute types. Each month an amount is returned in relation to
each account, so the attribute types for this are the year, the month and the
amount so you'd have an AccountReturns table with columns ReturnYear,
ReturnMonth and Amount, all numbers (the last as currency) as this makes it
easy to work with the months and it’s a simple matter to get the name of the
month from the number if required. Accounts and AccountReturns.
When a return is made a new row for the account is inserted into the
AccountsReturned table. One things this means of course is that there will
never be a NULL amount as no row will exist until an amount is returned.
To list all returns for each account is simple a matter of joining the two
tables in a query like so:
SELECT Accounts.AccountID, AccountName,
Closed, ReturnYear, ReturnMonth, Amount
FROM Acounts INNER JOIN AccountReturns
ON AccountReturns.AccoutID = Accounts.AccountID;
However, you want to return the latest returns per account only, and to
return a zero if the account is closed. To do this you need to restrict the
rows returned to those where the ReturnYear and ReturnMonth values represent
the latest return for each account. This done by means of a subquery, which
is 'correlated' to the main 'outer' query on the AccountID values. It would
go something like this:
SELECT Accounts.AccountID, AccountName, Closed,
FORMAT(ReturnYear & "-" & ReturnMonth& "-" & 1,"mmm yyyy")
As MonthReturned,
IIF(Closed,0,Amount) AS AmountReturned
FROM Acounts INNER JOIN AccountReturns AS AR1
ON AR1.AccoutID = Accounts.AccountID
WHERE AR1.ReturnYear & FORMAT(AR1.ReturnMonth,"00") =
(SELECT MAX(AR2.ReturnYear & FORMAT(AR2.ReturnMonth,"00"))
FROM AccountReturns AS AR2
WHERE AR2.AccountID = AR1.AccountID);
To explain this:
1. FORMAT(ReturnYear & "-" & ReturnMonth& "-" & 1,"mmm yyyy") As
MonthReturned shows the year/month in a format such as May 2008.
2. IIF(Closed,0,Amount) AS AmountReturned shows a zero if an account is
closed.
3. AR1.ReturnYear & FORMAT(AR1.ReturnMonth,"00") tacks the year and month
values together in a format like 200805.
4. MAX(AR2.ReturnYear & FORMAT(AR2.ReturnMonth,"00")) finds the highest
value of the year/month in the same format as above, i.e. the latest month.
5. WHERE AR2.AccountID = AR1.AccountID correlates the subquery with the
outer query on AccountID. Note how the AccountReports table is given aliases
of AR1 and AR2 to distinguish between the two instances of the table in the
outer and subquery.
The query should consequently give the latest amounts returned per account,
regardless of when the latest return was. In the case of closed accounts the
amount will be given as zero.
I realize this won't match your scenario exactly, or maybe not even closely,
but I hope it will give you some insight into how this sort of situation can
be handled.
Ken Sheridan
Stafford, England
