How to fill in a column with a number

  • Thread starter Thread starter guidop12
  • Start date Start date
G

guidop12

Please disregard the first post with the same subject(i hit enter to soon).
I have a database that has two columns
out rec# src file#
1 A
B
2 A
B
and so on. I would like to fill in the out rec# so that it would look like
this

out rec# src file
1 A
1 B
2 A
2 B

Any help would be appreciated
 
guidop12 said:
Please disregard the first post with the same subject(i hit enter to
soon). I have a database that has two columns
out rec# src file#
1 A
B
2 A
B
and so on. I would like to fill in the out rec# so that it would look
like this

out rec# src file
1 A
1 B
2 A
2 B

Any help would be appreciated
Click to expand...

Create a new table called [Assign out rec] with two columns:
NewOutRec autonumber
[src file] text

Then run a query to insert the B records into this table:
insert into [Assign out rec] ([src file])
select [src file] from original_table
where [src file] = 'B'

Then delete the B records from original_table and insert the records
from [Assign out rec] after which you can delete the records from
[Assign out rec]

Delete * FROM original_table where [src file] = 'B'

Insert into original_table ([out rec#], [src file])
Select [NewOutRec], [src file] from [Assign out rec]

Delete * from [Assign out rec]

Of course, there is probably more to your scenario than what you've
shown above so this solution might not work for you. I can think of
several what-ifs that would invalidate this solution.
 
I use Excel autofill to do it.
Add a new column to left of Tracking number, add a row under, and enter this
formula in A2 -- =IF(B2="", A1, B2)
A B C
1
2 1 abc
3 cab
4 gef
5 2 ced
6 rad
Then drag to fill column A.
 
This did work great in Excel, but I was wondering if I could do the same
thing in Access. Maybe using the IIF in a query. I'm just not sure on how to
write it.
 
You might be able to put the table in the query twice (Access adds sufix of
'_1' to the second) IF it has an autonumber field, in proper sequence, and no
gaps.
Then add one to the autonumber field of one table to compare with the
adjacent record of the other.
 
Back
Top