Need help with an Update query statement ...

[aellis2007]

I need a little help (maybe a lot J) … I am stuck with this statement.

This is in an update query
Two linked tables by the ProductLine
How can I fix this statement to work? And where does it need to be
entered/added?

([MonthNo]=Month(Date())=IIf([MP2_ProjDemandMonths].[NoOfMonths]=0,
[MP2_ProductLineNoOfMonths].[NoOfMonths],[MP2_ProjDemandMonths].[NoOfMonths])


I’ll explain a little more …
MonthNo field in table MP2_ProjDemandMonths has months 1-24 representing a 24
month period January of current year to December of Next Year.

NoOfMonths field is in both tables. One at the top level by just Product Line
and another specifically by Item.

The Item level will supercede the ProductLine level.

It’ll update for all the items in the product line for the number of months
except when an specific item has number of months, then it’ll use that value
to update.

What is it updating? It's the value in the Qty field for each month (MonthNo)



The end result:
Today is the current month 4
The user entered 8 months for the NoOfMonths to change / update.

The update query needs to know what the value is in the MonthNo 4 which is
100.

The update query will take the qty 100 from MonthNo 4 and update the next 8
consecutive months. MonthNo’s 5-12 and change the qty to 100 regardless of
the qty in there now.

Except if the Item also has a NoOfMonths value from the Item level, in above
example it’s 3 months. In this case it’ll take the value in MonthNo 7 and
update the next consecutive 3 months to 50 instead of the 100 from the
ProductLine level number of months.

 

[Michel Walsh]

Hum, you have three tables? using an outer join seems preferable.

Somethnig like:



UPDATE (tableNameToUpdate AS a LEFT JOIN ProductLine AS b
ON a.monthNo=b.MonthNo AND a.ProductID=b.ProductID) LEFT JOIN
ProjDemandLine AS c
ON a.monthNo=c.MonthNo AND a.ProductID=c.PRoductID

SET a.producLevel = Nz((Nz(b.productLevel, c.productLevel), 0)



What that does? the table to be updated get the productLevel from table
ProductLine (record with the same monthNo and same ProductID), but if no
such record exists, then it get it from table ProjDemanLine. If it still
fail to get a match, same monthNo and ProductID, it gets a 0.




Hoping it may help,
Vanderghast, Access MVP

 

[aellis2007 via AccessMonster.com]

Hi Michel,
thank you.

Now, it's actually 2 tables linked.
"Header" table is the "MP2_ProductLineNoOfMonths"
"Detail" table is the "MP2_ProjDemandMonths"


Are you saying that ProductID = Item?

I don't get this part, what's the productlevel?
SET a.producLevel = Nz((Nz(b.productLevel, c.productLevel), 0)


again, thank you for your assistance.

Hum, you have three tables? using an outer join seems preferable.

Somethnig like:

UPDATE (tableNameToUpdate AS a LEFT JOIN ProductLine AS b
ON a.monthNo=b.MonthNo AND a.ProductID=b.ProductID) LEFT JOIN
ProjDemandLine AS c
ON a.monthNo=c.MonthNo AND a.ProductID=c.PRoductID

SET a.producLevel = Nz((Nz(b.productLevel, c.productLevel), 0)

What that does? the table to be updated get the productLevel from table
ProductLine (record with the same monthNo and same ProductID), but if no
such record exists, then it get it from table ProjDemanLine. If it still
fail to get a match, same monthNo and ProductID, it gets a 0.

Hoping it may help,
Vanderghast, Access MVP

I need a little help (maybe a lot J) . I am stuck with this statement.

[quoted text clipped - 43 lines]

update the next consecutive 3 months to 50 instead of the 100 from the
ProductLine level number of months.

 

[aellis2007 via AccessMonster.com]

btw - sorry, but I'm not getting how it's suppose:
1. determine the what the current month is on any given day in "MonthNo"
(consists of month numbers 1-24)
2. update the Qty field for each month based on the number of months entered
into the "NoOfMonths" field which is an input by the user and exists in both
the Header and Detail tables.

it'll need to see that if there is a value in the "NoOfMonths" in the
"MP2_ProjDemandMonths" (detail table), this will override the value in the
"NoOfMonths" from the "MP2_ProductLineNoOfMonths" (header table). BUT the
value could entered in for another "MonthNo"

example:
current month = 4 from the "MonthNo" field.
"NoOfMonths" has the value 8 from the "MP2_ProductLineNoOfMonths" (header
table)

it would take the value in the "Qty" field from "MP2_ProjDemandMonths"
(detail table) and update the same value into the next 8 consecutive months.
"MonthNo"s 5-12 (May through December).
If the current month "Qty" is 100, it'll update the May-December with the
quantity of 100.


BUT if the "NoOfMonths" field from "MP2_ProjDemandoOfMonths" (detail table)
is other then 0 (it'll never have a null value in it) it'll need to use that
number to use to update the consecutive months.
example:
the "NoOfMonths' field from "MP2_ProjDemandOfMonths" is 3 and is entered for
"MonthNo" 7 it'll still update the quantity of 100 from the above but only
for months 5 and 6 (May and June) as for July it say to use the quantity for
July and then update the next consecutive 3 months using the quantity in July
(say 50). So it'll update Aug, Sep, and Oct to 50 and the remainder Nov and
Dec from the above to quantity of 100.

Hi Michel,
thank you.

Now, it's actually 2 tables linked.
"Header" table is the "MP2_ProductLineNoOfMonths"
"Detail" table is the "MP2_ProjDemandMonths"

Are you saying that ProductID = Item?

I don't get this part, what's the productlevel?
SET a.producLevel = Nz((Nz(b.productLevel, c.productLevel), 0)

again, thank you for your assistance.

Hum, you have three tables? using an outer join seems preferable.

[quoted text clipped - 20 lines]

 

[Michel Walsh]

Make TWO update, the most generic first, the most specific last. Running two
UPDATE query will simply solve the problem, otherwise, trying to do it in
one query would imply we have to marshal a much more complex case.


If the table to be updated, u, has fields:

itemID, monthNo, qty


and if the table having the specs, s, has the fields

itemID, fromMonthNo, forHowManyMonth, qty


then (untested)


UPDATE u
SET qty = Nz(DLookup("qty", "s", "itemID=" & u.itemID & AND " &
u.monthNO & " BETWEEN fromMonthNo AND fromMonthNo + forHowManyMonth "), qty)




should do


Hoping it may help,
Vanderghast, Access MVP

btw - sorry, but I'm not getting how it's suppose:
1. determine the what the current month is on any given day in "MonthNo"
(consists of month numbers 1-24)
2. update the Qty field for each month based on the number of months
entered
into the "NoOfMonths" field which is an input by the user and exists in
both
the Header and Detail tables.

it'll need to see that if there is a value in the "NoOfMonths" in the
"MP2_ProjDemandMonths" (detail table), this will override the value in the
"NoOfMonths" from the "MP2_ProductLineNoOfMonths" (header table). BUT the
value could entered in for another "MonthNo"

example:
current month = 4 from the "MonthNo" field.
"NoOfMonths" has the value 8 from the "MP2_ProductLineNoOfMonths" (header
table)

it would take the value in the "Qty" field from "MP2_ProjDemandMonths"
(detail table) and update the same value into the next 8 consecutive
months.
"MonthNo"s 5-12 (May through December).
If the current month "Qty" is 100, it'll update the May-December with the
quantity of 100.


BUT if the "NoOfMonths" field from "MP2_ProjDemandoOfMonths" (detail
table)
is other then 0 (it'll never have a null value in it) it'll need to use
that
number to use to update the consecutive months.
example:
the "NoOfMonths' field from "MP2_ProjDemandOfMonths" is 3 and is entered
for
"MonthNo" 7 it'll still update the quantity of 100 from the above but only
for months 5 and 6 (May and June) as for July it say to use the quantity
for
July and then update the next consecutive 3 months using the quantity in
July
(say 50). So it'll update Aug, Sep, and Oct to 50 and the remainder Nov
and
Dec from the above to quantity of 100.

Hi Michel,
thank you.

Now, it's actually 2 tables linked.
"Header" table is the "MP2_ProductLineNoOfMonths"
"Detail" table is the "MP2_ProjDemandMonths"

Are you saying that ProductID = Item?

I don't get this part, what's the productlevel?
SET a.producLevel = Nz((Nz(b.productLevel, c.productLevel), 0)

again, thank you for your assistance.

Hum, you have three tables? using an outer join seems preferable.

[quoted text clipped - 20 lines]

update the next consecutive 3 months to 50 instead of the 100 from the
ProductLine level number of months.