NO DATA - Create Zero Values

[Guest]

(To start, I hate the way our database managers setup this database.)

One of our databases is setup such that when a price adjustment is created,
the row of data reads something like --

FROM TO ADJ
2/1/2007 0.50

while it is still active. (The TO field is left blank.) If the adjustment
changes, they alter the record to read --

FROM TO ADJ
2/1/2007 3/1/2007 0.50
3/1/2007 0.75

Normally, I can write queries against the FROM or TO fields if I want, say
all the adjustments from 2/1/2007 to the present.

HOWEVER, if the adjustment changes to ZERO, there is no new record created.
They only change the existing record to read --

FROM TO ADJ
2/1/2007 3/1/2007 0.50

So for example, if today is 3/28/2007, my query will not pick up the ZERO
adjustment post 3/1/2007.

The other problem I run into is when the adjustment is ZERO in between
records. So for instance,

FROM TO ADJ
1/1/2007 2/1/2007 0.50
3/1/2007 3/10/2007 0.75
3/10/2007 1.000

actually means that from 1/1 - 2/1, the adjustment = 0.500. From 2/1 - 3/1,
the adjustment = 0. From 3/1 - 3/10, the adjustment = 0.75. And from 3/10
to present, the adjustment = 1.000.

Writing a query to pull all adjustments with FROM >= 1/1/2007 will lose the
2/1 - 3/1 adjustment of ZERO.

HELP!

How do I setup ZERO values for gaps in the FROM - TO dates? I'm not SQL
trained, however if the solution is only available using SQL, I can ask one
of my co-workers to translate / implement for me.

THANKS

 

[Michel Walsh]

If you let the field [To] as a NULL, you can use a BETWEEN criterion
like:

WHERE value BETWEEN [From] AND Nz( [to], date() )


with the Nz replacing the null value, if any, by today date.


If you need to have zero, when a date fall into hole, use outer join rather
than the WHERE clause, something like:



SELECT *, Nz( adj, 0)
FROM myTable LEFT JOIN adjustments
ON myTable.date BETWEEN [from] AND Nz( [to], date() )



The left join return null, for adj, if no match if found, and, again, we use
Nz to change that null into a zero.


Hoping it may help,
Vanderghast, Access MVP