Automatically Adding Data from previous field under certain condit

[Jim McC]

I have data as follows:

01/12/07 Fri 242
01/13/07 Sat
01/14/07 Sun
01/15/07 Mon (Holiday)
01/16/07 Tues 116
01/17/07 Wed 271
01/18/07 Thurs 242
01/19/07 Fri
01/20/07 Sat
01/21/07 Sun

I would like for Saturday and Sunday to pull the data from the balance on
Friday. Or if it is a Holiday to pull the data from the previous workday.
Note that in this case there is no data on Fri (01/19), so I will need Sat
and Sunday to show no data as well (or 0).

I will need the output to look like this:

01/12/07 Fri 242
01/13/07 Sat 242
01/14/07 Sun 242
01/15/07 Mon (Holiday) 242
01/16/07 Tues 116
01/17/07 Wed 271
01/18/07 Thurs 242
01/19/07 Fri 0
01/20/07 Sat 0
01/21/07 Sun 0

Thanks,

 

[Michel Walsh]

IF you can put a 0, not a null, when the date is a workday and there is a 0
to be then copied, then it is a much easier SQL statement:

(example based on 01/19/07 with a 0 as data)

--untested--

SELECT a.dateField, Nz(a.dataField, LAST(b.dataField ))
FROM (tableName AS a RIGHT JOIN
(SELECT * FROM tableName WHERE NOT(dataField IS NULL) ) AS b
ON a.dateField >b.dateField) RIGHT JOIN
(SELECT * FROM tableName WHERE NOT(dataField IS NULL) ) AS c
ON a.dateField >c.dateField
GROUP BY a.dateField, b.dateField, a.dataField
HAVING b.dateField = MAX(c.dateField)



The idea of this statement is to get the latest date

HAVING b.dateField = MAX(c.dateField)

among all the date BEFORE the date considered,

ON a.dateField > c.dateField

but the substitution of the actual data for the row considered,

a.dataField

is only replaced by the data associated top the said previous date,

LAST( b.dataField)

in the case where a.dataFIeld IS NULL:

Nz(a.dataField, LAST(b.dataField ))





Hoping it may help,
Vanderghast, Access MVP