Update query question ...

[angelandgreg]

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

"MonthNo" field in table MP2_ProjDemandMonths (detail table) 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 in the header table (MP2_ProductLineNoOfMonths) with just field "ProductLine" and the details table (MP_ProjDemandMonths) has ProductLine, Item, etc ...

The Item details will supercede the ProductLine header.

It’ll update for all the items in the Product Line for the number of months except when a specific item has a value other then 0 in the number of months field in the details table, then it’ll use that value to update.

What is it updating? It's the value in the "Qty" field for each consecutive month ("MonthNo") based on the number of months value.

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

The update query needs to know what the "Qty" is in the "MonthNo" field (4) which is the quantity 100. Other words, month 4 (April) has quantity of 100.

The update query will take the qty of 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 (from the details table) also has a "NoOfMonths" value, example the user overrides for the specific item as to update 3 months for the month of July. In this case it’ll take the value in the "Qty" field (say 50) for "MonthNo" 7 and update the next consecutive 3 months to 50 instead of the 100 from the ProductLine (header table) number of months.

 

[angelandgreg]

1. determine what the current month is on any given day from the value 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.

 

[angelandgreg]

okay, I have figured out this much thus far
to only display the months based on the number of months value.

so this shows the current month and the 8 consecutive months as 8 is in the number of months field.

SELECT MP2_ProductLineNoOfMonths.ProductLine, MP2_ProductLineNoOfMonths.NoOfMonths AS PLNoOfMonths, MP2_ProjDemandMonths.ItemNumber, MP2_ProjDemandMonths.MonthNo, MP2_ProjDemandMonths.Qty, MP2_ProjDemandMonths.NoOfMonths
FROM MP2_ProductLineNoOfMonths INNER JOIN MP2_ProjDemandMonths ON MP2_ProductLineNoOfMonths.ProductLine = MP2_ProjDemandMonths.ProductLine
WHERE (((MP2_ProductLineNoOfMonths.NoOfMonths)<>0) AND ((MP2_ProjDemandMonths.MonthNo) Between Month(Date()) And Month(Date())+[MP2_ProductLineNoOfMonths].[NoOfMonths]));

How do I write the Update query to use the quantity in the current month to update the Qty field of the consecutive months?


I used this and it's not right:

UPDATE ProjDemandNoOfMonths SET ProjDemandNoOfMonths.Qty = [Qty]
WHERE [MonthNo]=Month(Date());