Changing number of months - table set-up & query to do this ... 1

[angelandgreg]

I was wondering if someone can assist me with this issue.


This is for projections for inventory items.
There is a file in the company's back office system that stored projections by item number per month.
It will show the 12 periods for the current year and you can also have projections for the next year.


Anyway, I am doing this in MS Access.
I have a table and trying to see if this is the direction I should go or not.


Basically, the user wants to be able to select the number of months to update the projection qty they enter into a field (say ProjectedDemand is the field name).

So if this month (current month) is October and the user selects 6 months to update of the ProjectedDemand qty, I want the query to be able to update that qty (say 100) into the October, November, December, January (of next year), February, and March and so on.


currently the table I have is set-up as such:
ItemNumber
ProjectedDemand
Jan
Feb
Mar, etc ... JanNY, FebNY (NY = NextYear) so there are 24 month buckets along with the ProjectedDemand field.


or I was thinking another table
ItemNumber
ProjectedDemand
Month (this will be filled with Jan, Feb, ... JanNY, FebNY so each ItemNumber will repeat 24 times.


Hope this wasn't too confusing and I made sense?
I am having difficulty as to how I will be able to allow the user to select the number of months they want to update the ProjectDemand qty and how it'll update the following consecutive months of the number they entered.

 

[PHV]

We know that the current month (say ForecastOctober)
How do you know that ?
For me, the current month is actually April, so I'm confused.

 

[angelandgreg]

sorry, i used that to make sure that the following months, when it goes into the new year ...

but this is now probably irrelevant since it's numeric value to identify the months.

Before I wasn't sure how it was going to update the months after Dec to JanNY, etc ... and i wanted to make sure that in this post that part was also covered.


so, yes, it would be true that being April the "current" month would = 4 from the MonthNo field from the detail table.

 

[angelandgreg]

UPDATE MP2_ProductLineNoOfMonths INNER JOIN MP2_ProjDemandMonths ON MP2_ProductLineNoOfMonths.ProductLine = MP2_ProjDemandMonths.ProductLine SET;



since I can't get the SQL statement for you with this ((MonthNumber-Month(Date())) Between 0 And 5) in it because of the error, the above is the SQL statement of the update query.

I tried placing (MonthNo-Month(Date())) Between 0 And 5 under the MonthNo.

and I don't get how this will determine the number of month value the user enters and updates the Qty field to the corresponding consecutive months from the current months quantity??

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmm ... mmmmmmmm ...

difficult to argue with PHV, as he is truly one of the phenoms in these fora ... but then all the 'modern' database abstracts basically say that deep is better than wide (e.g. more columns fewer rows) also most (at least a lot) will say that the 'numbering' of the various columns to represent months is a sure sign of de-normalization.

Since you seems to like the direction PHV has suggested, I'll not go into specifics, but will suggest that you at least review some of the basic database material, particularly the normalization 'rules'.





MichaelRed

 

[angelandgreg]

there must be something I am not describing here ...

I cannot change anything of the back office database.
This database as the table MP2_ProjectedDemands with the columned months.

I have taken that and changed it to rows for the months and the months are numbered 1-24.
However, to do this i have to make 24 Append Queries.

1. how can I do that with one step aside from creating the 24 queries and then a macro?
2. I have the data in the Access table, I now need to have my form and query know that the current month is the 4 from the MonthNo field of the Access table (DETAILS). The code PHV provided does not work. I get the invalid error.
3. Once the current month is dynamically determined each month, I need have the consecutive months based on the user input of number of months to update rather it's 2 or 12 but not past month 24 as that is the last month.


I hope I have clarified any confusion.

 

[angelandgreg]

ah, I realized that there's no brackets [] in the code provided.

I do not get the invalid error anymore.


here's the update query ...

UPDATE MP2_ProductLineNoOfMonths INNER JOIN MP2_ProjDemandMonths ON MP2_ProductLineNoOfMonths.ProductLine = MP2_ProjDemandMonths.ProductLine SET MP2_ProjDemandMonths.Qty = ([MonthNo]=Month(Date())) Between 0 And 5;


The Between 0 and 5 won't always work. it has to be dependant on the value in the NoOfMonths field.

It can be whatever number the user enters.

I tried to change it to this:
(([MonthNo]=Month(Date())),[NoOfMonths])

and of course it's not correct.


thank you!

 

[angelandgreg]

oops - i changed it to this:
([MonthNo]=Month(Date())=[MP2_ProductLineNoOfMonths].[NoOfMonths)

when I preview it I get all records.

 

[angelandgreg]

So this is not receiving an error ...
([MonthNo]=Month(Date())

The Bewteen 0 and 5 is statically going to be always 6 months.

This part needs to be based on what the user enters.
I tried it this way:
([MonthNo]=Month(Date())=IIf([MP2_ProjDemandMonths].[NoOfMonths]=0,[MP2_ProductLineNoOfMonths].[NoOfMonths],[MP2_ProjDemandMonths].[NoOfMonths])

it put everything in Qty to 0 instead of using the value that was in it.

What do I need to change?

 

[angelandgreg]

PHV ...

could you help me finish this please?

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());

 

[PHV]

So, the consecutive month query with the starting point I gave you last week:
SELECT H.ProductLine, H.NoOfMonths AS PLNoOfMonths, D.ItemNumber, D.MonthNo, D.Qty, D.NoOfMonths
FROM MP2_ProductLineNoOfMonths AS H INNER JOIN MP2_ProjDemandMonths AS D ON H.ProductLine = D.ProductLine
WHERE H.NoOfMonths<>0 AND (D.MonthNo-Month(Date())) Between 0 And H.NoOfMonths

Now, the Update query.
I hate the way access chokes on UPDATE instruction with subqueries, so I suggest to use the DLookUp function:

UPDATE ProjDemandNoOfMonths
SET Qty = DLookUp("Qty","ProjDemandNoOfMonths","ProductLine='" & [ProductLine] & "' AND MonthNo=Month(Date())")
WHERE (D.MonthNo-Month(Date())) Between 1 And DLookUp("NoOfMonths","MP2_ProductLineNoOfMonths","ProductLine='" & [ProductLine] & "'")

If ProductLine is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[angelandgreg]

Good evening PHV ...
i was getting really worried.

this is my query result from:

ProductLine Item PLNoOfMonths MonthNo Qty NoOfMonths
0012 12-04014A 8 4 100 0
0012 12-04014A 8 5 0 0
0012 12-04014A 8 6 0 0
0012 12-04014A 8 7 0 2
0012 12-04014A 8 8 0 0
0012 12-04014A 8 9 0 0
0012 12-04014A 8 10 0 0
0012 12-04014A 8 11 0 0
0012 12-04014A 8 12 0 0
0012 12-36025B 8 4 0 0
0012 12-36025B 8 5 0 0
0012 12-36025B 8 6 0 0
0012 12-36025B 8 7 0 0
0012 12-36025B 8 8 0 0
0012 12-36025B 8 9 0 0
0012 12-36025B 8 10 0 0
0012 12-36025B 8 11 0 0
0012 12-36025B 8 12 0 0


on the Update query i replaced with yours:

UPDATE ProjDemandNoOfMonths
SET Qty = DLookUp("Qty","ProjDemandNoOfMonths","ProductLine='" & [ProductLine] & "' AND MonthNo=Month(Date())")
WHERE (D.MonthNo-Month(Date())) Between 1 And DLookUp("NoOfMonths","MP2_ProductLineNoOfMonths","ProductLine='" & [ProductLine] & "'")

and it did update with the quantity of 100 but it seemed to have updated it for the other Items in the same product line.

And skipped the current month and only updated the consecutive months on other Items in the same productline.


Here's the result after running the Update:
PLNoOfMonths ProductLine ItemNumber MonthNo Qty NoOfMonths
8 0012 12-04014A 4 100 0
8 0012 12-04014A 5 100 0
8 0012 12-04014A 6 100 0
8 0012 12-04014A 7 100 2
8 0012 12-04014A 8 100 0
8 0012 12-04014A 9 100 0
8 0012 12-04014A 10 100 0
8 0012 12-04014A 11 100 0
8 0012 12-04014A 12 100 0
8 0012 12-36025B 4 50 0
8 0012 12-36025B 5 100 0
8 0012 12-36025B 6 100 0
8 0012 12-36025B 7 100 0
8 0012 12-36025B 8 100 0
8 0012 12-36025B 9 100 0
8 0012 12-36025B 10 100 0
8 0012 12-36025B 11 100 0
8 0012 12-36025B 12 100 0

Shouldn't it have taken the from the Item 12-36025B and updated the consecutive months with 50?

 

[angelandgreg]

I got it updated. I replaced ProductLine for ItemNumber and it's doing what I needed.


Thanks again!!!


As you can tell I was utterly confused. %-)

 

[angelandgreg]

Hi PHV,
I was trying to edit this:

UPDATE ProjDemandNoOfMonths SET ProjDemandNoOfMonths.Qty = DLookUp("Qty","ProjDemandNoOfMonths","ItemNumber='" & [ItemNumber] & "' AND MonthNo=Month(Date())")
WHERE ((([D].[MonthNo]-Month(Date())) Between 1 And IIf([NoOfMonths]=0,DLookUp("NoOfMonths","MP2_ProductLineNoOfMonths","ProductLine='" & [ProductLine] & "'"),DLookUp("NoOfMonths","MP2_ProjDemandMonths","ItemNumber='" & [ItemNumber] & "'"))));


and basically one item, S16 has a "Qty" of 120 in MonthNo 7 with the NoOfMonths of 2 from the MP2_ProjDemandMonths.
The "Qty" on the current month 4 is 35.

So the change in the code it only skipped when it reached MonthNo 7 for this item but did not update the next 2 months with the Qty of 120. They have the "Qty" 35 from the above.

I know I'm close, now I'm not sure where and what to change so i'll use the Qty when it reaches the Item's NoOfMonth field is >0