Replace a value with the one above it in a field

[ChuckW]

Hi,

I have a transaction table that has fields such as Date, TransactionNumber,
ItemNumber, Quantity and Amount. It is data taken from a transactional
system and imported in Access. The ItemNumber fields can have values of our
Item Numbers such as 3142 or it will have the value of Discount. What I want
to do is to somehow run a query that will replace Discount with the value
above it. So if there is one record with a value of 3142 in the ItemNumber
field and the next record has Discount in this field I want to change this
value to 3142. Ten records later there may be a value of 2080 in ItemNumber,
and the 11th record may have Discount. I want the Discount value in the 11th
record to be replaced by 2080. We could also run a query that provides this
value in a separate field rather than replacing it in ItemNumber. Can
someone help?

Thanks,

Chuck

 

[Jeff Boyce]

Chuck

If you had a (human) assistant doing this, how would you tell them to know
when a value in the [ItemNumber] field wasn't really an ItemNumber? You'll
need to tell Access how to tell, too.

If you ran a query against your existing data, and used this to "move" a
Discount (see above) to another field, would you still have a way to tie the
record/row that contained the Discount to it's proper "parent" record?

One more thing ... you refer to the value "above" it ... but Access doesn't
think about sequence/order the same way you do. Do you have criteria you
can use for sorting to get the records properly sequenced? If so, can you
use that to help with moving the Discount?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mark J]

provide a table of your data, it would help better visually

 

[ChuckW]

Here is a sample of my transaction table:

Date TxnNum ItemNum Qty Amount
12/1/06 12841 4080 24 $456
12/1/06 12841 Discount -$45.6
12/1/06 12841 7040 24 $618
12/1/06 12841 Discount -$61.8

I am trying to do a report on Item sales for each month. We have case lot
discounts where if a customer buys in Quantities of 24, they get 10 percent
off. There is no way in my data to tie the second record (discount of $45.6)
to the sale of item 4080. The only relationship is that the discount always
comes one record after the item being discounted (and it is always after
multiples of 24) If I run a report on item sales for the month my figures are
inflated. But if I can convert the Discount value into the item number above
it, I can then calculate accurately the amount sold. I could manually go
through and change this but there are about 200,000 total records to go
through.

Thanks,

Chuck

--
Chuck W

Chuck

If you had a (human) assistant doing this, how would you tell them to know
when a value in the [ItemNumber] field wasn't really an ItemNumber? You'll
need to tell Access how to tell, too.

If you ran a query against your existing data, and used this to "move" a
Discount (see above) to another field, would you still have a way to tie the
record/row that contained the Discount to it's proper "parent" record?

One more thing ... you refer to the value "above" it ... but Access doesn't
think about sequence/order the same way you do. Do you have criteria you
can use for sorting to get the records properly sequenced? If so, can you
use that to help with moving the Discount?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have a transaction table that has fields such as Date,
TransactionNumber,
ItemNumber, Quantity and Amount. It is data taken from a transactional
system and imported in Access. The ItemNumber fields can have values of
our
Item Numbers such as 3142 or it will have the value of Discount. What I
want
to do is to somehow run a query that will replace Discount with the value
above it. So if there is one record with a value of 3142 in the
ItemNumber
field and the next record has Discount in this field I want to change this
value to 3142. Ten records later there may be a value of 2080 in
ItemNumber,
and the 11th record may have Discount. I want the Discount value in the
11th
record to be replaced by 2080. We could also run a query that provides
this
value in a separate field rather than replacing it in ItemNumber. Can
someone help?

Thanks,

Chuck

 

[Jeff Boyce]

Chuck

I may be over-simplifying this...

If I ran one query against the data with a criterion of [ItemNumber] =
"Discount", and totaled that up for a date range (e.g., month of January,
2006), I'd get one number.

If I ran a second query ... with criterion of [ItemNumber] <> "Discount",
totaled it for the same date range, I'd get a second number.

Isn't the total sales amount all the plus less all the minus? If so, do a
SINGLE query, a Totals query, on that amount field, since the plus values
are all positive and the discounts are all negative.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a sample of my transaction table:

Date TxnNum ItemNum Qty Amount
12/1/06 12841 4080 24 $456
12/1/06 12841 Discount -$45.6
12/1/06 12841 7040 24 $618
12/1/06 12841 Discount -$61.8

I am trying to do a report on Item sales for each month. We have case lot
discounts where if a customer buys in Quantities of 24, they get 10
percent
off. There is no way in my data to tie the second record (discount of
$45.6)
to the sale of item 4080. The only relationship is that the discount
always
comes one record after the item being discounted (and it is always after
multiples of 24) If I run a report on item sales for the month my figures
are
inflated. But if I can convert the Discount value into the item number
above
it, I can then calculate accurately the amount sold. I could manually go
through and change this but there are about 200,000 total records to go
through.

Thanks,

Chuck

--
Chuck W

Chuck

If you had a (human) assistant doing this, how would you tell them to
know
when a value in the [ItemNumber] field wasn't really an ItemNumber?
You'll
need to tell Access how to tell, too.

If you ran a query against your existing data, and used this to "move" a
Discount (see above) to another field, would you still have a way to tie
the
record/row that contained the Discount to it's proper "parent" record?

One more thing ... you refer to the value "above" it ... but Access
doesn't
think about sequence/order the same way you do. Do you have criteria you
can use for sorting to get the records properly sequenced? If so, can
you
use that to help with moving the Discount?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have a transaction table that has fields such as Date,
TransactionNumber,
ItemNumber, Quantity and Amount. It is data taken from a transactional
system and imported in Access. The ItemNumber fields can have values
of
our
Item Numbers such as 3142 or it will have the value of Discount. What
I
want
to do is to somehow run a query that will replace Discount with the
value
above it. So if there is one record with a value of 3142 in the
ItemNumber
field and the next record has Discount in this field I want to change
this
value to 3142. Ten records later there may be a value of 2080 in
ItemNumber,
and the 11th record may have Discount. I want the Discount value in
the
11th
record to be replaced by 2080. We could also run a query that provides
this
value in a separate field rather than replacing it in ItemNumber. Can
someone help?

Thanks,

Chuck

 

[ChuckW]

Jeff,

In the example below, let say that these are the only transactions for these
item numbers (Items 4080 and 7040). In actually there are several
transactions for all items and only a handful get a discount which is based
on quantity. But for this example the total sales for item 4080 was $410.4
which is $456 - $45.6. The total sales for 7040 is $556.2 which is
$618-$61.6. I am not trying to get one sales figure for all products for the
month. I am looking for a way to get net sales by item. The discount line
always appears after the product that is being discounted. If I can convert
the value discount to equal the value above it, this would solve my problem.

Thanks,
--
Chuck W

Chuck

I may be over-simplifying this...

If I ran one query against the data with a criterion of [ItemNumber] =
"Discount", and totaled that up for a date range (e.g., month of January,
2006), I'd get one number.

If I ran a second query ... with criterion of [ItemNumber] <> "Discount",
totaled it for the same date range, I'd get a second number.

Isn't the total sales amount all the plus less all the minus? If so, do a
SINGLE query, a Totals query, on that amount field, since the plus values
are all positive and the discounts are all negative.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a sample of my transaction table:

Date TxnNum ItemNum Qty Amount
12/1/06 12841 4080 24 $456
12/1/06 12841 Discount -$45.6
12/1/06 12841 7040 24 $618
12/1/06 12841 Discount -$61.8

I am trying to do a report on Item sales for each month. We have case lot
discounts where if a customer buys in Quantities of 24, they get 10
percent
off. There is no way in my data to tie the second record (discount of
$45.6)
to the sale of item 4080. The only relationship is that the discount
always
comes one record after the item being discounted (and it is always after
multiples of 24) If I run a report on item sales for the month my figures
are
inflated. But if I can convert the Discount value into the item number
above
it, I can then calculate accurately the amount sold. I could manually go
through and change this but there are about 200,000 total records to go
through.

Thanks,

Chuck

--
Chuck W

Chuck

If you had a (human) assistant doing this, how would you tell them to
know
when a value in the [ItemNumber] field wasn't really an ItemNumber?
You'll
need to tell Access how to tell, too.

If you ran a query against your existing data, and used this to "move" a
Discount (see above) to another field, would you still have a way to tie
the
record/row that contained the Discount to it's proper "parent" record?

One more thing ... you refer to the value "above" it ... but Access
doesn't
think about sequence/order the same way you do. Do you have criteria you
can use for sorting to get the records properly sequenced? If so, can
you
use that to help with moving the Discount?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have a transaction table that has fields such as Date,
TransactionNumber,
ItemNumber, Quantity and Amount. It is data taken from a transactional
system and imported in Access. The ItemNumber fields can have values
of
our
Item Numbers such as 3142 or it will have the value of Discount. What
I
want
to do is to somehow run a query that will replace Discount with the
value
above it. So if there is one record with a value of 3142 in the
ItemNumber
field and the next record has Discount in this field I want to change
this
value to 3142. Ten records later there may be a value of 2080 in
ItemNumber,
and the 11th record may have Discount. I want the Discount value in
the
11th
record to be replaced by 2080. We could also run a query that provides
this
value in a separate field rather than replacing it in ItemNumber. Can
someone help?

Thanks,

Chuck

 

[Jeff Boyce]

Chuck

Take a look at a Totals query, using GroupBy on the ItemNumber and Sum on
the Amount. Note that this approach will show the total amount of discount,
since that is one of the "ItemNumber" values.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

In the example below, let say that these are the only transactions for
these
item numbers (Items 4080 and 7040). In actually there are several
transactions for all items and only a handful get a discount which is
based
on quantity. But for this example the total sales for item 4080 was
$410.4
which is $456 - $45.6. The total sales for 7040 is $556.2 which is
$618-$61.6. I am not trying to get one sales figure for all products for
the
month. I am looking for a way to get net sales by item. The discount
line
always appears after the product that is being discounted. If I can
convert
the value discount to equal the value above it, this would solve my
problem.

Thanks,
--
Chuck W

Chuck

I may be over-simplifying this...

If I ran one query against the data with a criterion of [ItemNumber] =
"Discount", and totaled that up for a date range (e.g., month of January,
2006), I'd get one number.

If I ran a second query ... with criterion of [ItemNumber] <> "Discount",
totaled it for the same date range, I'd get a second number.

Isn't the total sales amount all the plus less all the minus? If so, do
a
SINGLE query, a Totals query, on that amount field, since the plus values
are all positive and the discounts are all negative.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a sample of my transaction table:

Date TxnNum ItemNum Qty Amount
12/1/06 12841 4080 24 $456
12/1/06 12841 Discount -$45.6
12/1/06 12841 7040 24 $618
12/1/06 12841 Discount -$61.8

I am trying to do a report on Item sales for each month. We have case
lot
discounts where if a customer buys in Quantities of 24, they get 10
percent
off. There is no way in my data to tie the second record (discount of
$45.6)
to the sale of item 4080. The only relationship is that the discount
always
comes one record after the item being discounted (and it is always
after
multiples of 24) If I run a report on item sales for the month my
figures
are
inflated. But if I can convert the Discount value into the item number
above
it, I can then calculate accurately the amount sold. I could manually
go
through and change this but there are about 200,000 total records to go
through.

Thanks,

Chuck

--
Chuck W


:

Chuck

If you had a (human) assistant doing this, how would you tell them to
know
when a value in the [ItemNumber] field wasn't really an ItemNumber?
You'll
need to tell Access how to tell, too.

If you ran a query against your existing data, and used this to "move"
a
Discount (see above) to another field, would you still have a way to
tie
the
record/row that contained the Discount to it's proper "parent" record?

One more thing ... you refer to the value "above" it ... but Access
doesn't
think about sequence/order the same way you do. Do you have criteria
you
can use for sorting to get the records properly sequenced? If so, can
you
use that to help with moving the Discount?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have a transaction table that has fields such as Date,
TransactionNumber,
ItemNumber, Quantity and Amount. It is data taken from a
transactional
system and imported in Access. The ItemNumber fields can have
values
of
our
Item Numbers such as 3142 or it will have the value of Discount.
What
I
want
to do is to somehow run a query that will replace Discount with the
value
above it. So if there is one record with a value of 3142 in the
ItemNumber
field and the next record has Discount in this field I want to
change
this
value to 3142. Ten records later there may be a value of 2080 in
ItemNumber,
and the 11th record may have Discount. I want the Discount value in
the
11th
record to be replaced by 2080. We could also run a query that
provides
this
value in a separate field rather than replacing it in ItemNumber.
Can
someone help?

Thanks,

Chuck