DD said:
i have a table that contains the following data. there are numerous entries
in the table as follows
AUTO NUMBER NAME HOURS RATE
1 smith 1 50
2 3.0 500
3 4 750
4 jones 2 75
5 4 630
6 1 50
i would like to run a query or something that would make the table look like
this. basically it copies the previous record if null
AUTO NUMBER NAME HOURS RATE
1 smith 1 50
2 smith 3.0 500
3 smith 4 750
4 jones 2 75
5 jones 4 630
6 jones 1 50
is there a way to do this.
i appreciate any help and please let me know if more clarification is
necessary.
thanks
This isn't very elegant but here goes. I changed your [AUTO NUMBER] to
a field called ID that is of type AutoNumber. I changed NAME to
EmpName since Name is a reserved word. Hours and Rate are of type Double.
tblTimeTickets
ID EmpName Hours Rate
1 smith 1 50
2 3 500
3 4 750
4 jones 2 75
5 4 630
6 1 50
qryPrevEmpNotNull:
SELECT tblTimeTickets.ID, (SELECT EmpName FROM tblTimeTickets AS A WHERE
A.ID IN (SELECT Last(B.ID) FROM tblTimeTickets AS B WHERE B.ID <=
tblTimeTickets.ID AND B.EmpName IS NOT NULL)) AS PrevEmpNotNull FROM
tblTimeTickets;
! qryPrevEmpNotNull:
ID PrevEmpNotNull
1 smith
2 smith
3 smith
4 jones
5 jones
6 jones
I suppose to implement this I would change this query to a Make Table
query (creating tblUpdateEmp):
SELECT tblTimeTickets.ID, (SELECT EmpName FROM tblTimeTickets AS A WHERE
A.ID IN (SELECT Last(B.ID) FROM tblTimeTickets AS B WHERE B.ID <=
tblTimeTickets.ID AND B.EmpName IS NOT NULL)) AS PrevEmpNotNull INTO
tblUpdateEmp FROM tblTimeTickets;
Then I would run an update query like:
UPDATE tblTimeTickets INNER JOIN tblUpdateEmp ON tblTimeTickets.ID =
tblUpdateEmp.ID SET tblTimeTickets.EmpName = [PrevEmpNotNull] WHERE
tblTimeTickets.EmpName Is Null;
which should replace the Null EmpName values with the last prior
non-null value. Suppose tblTimeTickets had smith on line 2 instead of
line 1. qryPrevEmpNotNull would simply have a Null value in its first
record. Be sure to test these queries out before using them. It
wouldn't hurt to try them on a backup table either. Also, don't forget
to run the Make Table query each time before running the Update query.
If I think of an easier way to do this without writing code I'll post
back. It's not usually a good idea to let anything depend on the order
the records are stored in a table. You should feel much better about a
form that always puts the EmpName into each record.
James A. Fortune
