copying records

  • Thread starter Thread starter DD
  • Start date Start date
D

DD

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
 
DD,

This doesn't look like a job for a macro. I guess it would take some VBA
coding, to do it through a recordset operation. Frankly, I wouldn't
bother if the table has no more than 65536 records, though, I would just
get it accross in Excel and do it there.

The idea is this:

* export or copy/paste the table in an Excel sheet
* assuming row 1 is headers and the data are in columns A through D, put
this formula in E3: =IF(Len(B3) >=1, B3, E2)
* copy the formula down to the end of the data; this will give you what
you want for column NAME
* copy the E column cells from row 3 down to the end, and paste special
value on cells B3 down to the end
Click to expand...
* delete column E, which you no longer need
* import or copy /paste back into Access

HTH,
Nikos
 
As a one off, you could run an update query using the autonumber as the
criteria where it is greater than or less than a certain number (being the
records you need changing). This is all very manual though.

If this is an ongoing issue you may want to change the way the data is
entered to the table.

Regards
Michael
 
Back
Top