update query

[carol]

need to setup an update query that adds the following data
into a table.

fields: store#, qty

table currently includes a record for each store# with a
blank qty field.
the next record after each of these store# records has a
bogus value in the store# field and a valid value in the
qty field so that consecutive records appear in the
following sequence:

store# qty
01 blank
bogus 2
02 blank
bogus 4

here's what i need to update: populate the preceding
store blank qty with the next record, so that blank qty in
record #1 is replaced with value of qty in record #2 and
so on.

is there a simple way to accomplish this? thx for your
help.

 

[Bas Cost Budde]

store# qty
01 blank
bogus 2
02 blank
bogus 4

here's what i need to update: populate the preceding
store blank qty with the next record, so that blank qty in
record #1 is replaced with value of qty in record #2 and
so on.

I think you're off track, because records in a table have no fixed
order. Unless you can maintain the order you sketch, there is no link
between those records, and it will not be possible to update anything.

 

[Jane Graves]

I think you'd be further ahead to do that in Excel and re-
import the data into Access.

In Excel, you'd put a formula in a new column. If Store
is in Column A and Qty is in B, the formula might be
something like =if(b2="",b3,b2). Copy that all the way
down. Copy the column and paste special as values. Sort
by store and delete the bogus entries. Then you can delete
the old Qty column and make your new column Qty.

Jane
jane.graves @ infores.com

 

[carol]

yes, i realize that but the records were imported and are
in the exact order necessary to retrieve the correct qty.
thx.

 

[Bas Cost Budde]

yes, i realize that but the records were imported and are
in the exact order necessary to retrieve the correct qty.
thx.

Okay. Then, I can think of a procedural solution. That means: code!

I'll write a procedure that steps through the records, starting at the
last. You have an even number of records, right? So the assumption is
safe that the last record contains the qty for the before-last one?

AIR CODE will become propercased when it compiles.

sub squeezeTable(cTable as string)
dim rs as recordset
dim nQty as long'change into Double if your datatype requires
set rs=currentdb.openrecordset(cTable, dbopendynaset)
rs.movelast
do until rs.eof
nQty = rs!qty
rs.moveprevious
rs.edit
rs!qty = nQty
rs.update
rs.moveprevious
loop
rs.close
set rs=nothing
end sub

After running this, you may delete all bogus records.

 

[carol]

knew this could be coded but didn't know how in access.
thx!