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.

 

[angelandgreg]

I should ad the Extract query that pulls the existing data from their back office database.

SELECT IM1_InventoryMasterfile.ProductLine, MP2_ProjectedDemands.ForecastJanuary AS Jan, MP2_ProjectedDemands.ForecastFebruary AS Feb, MP2_ProjectedDemands.ForecastMarch AS Mar, MP2_ProjectedDemands.ForecastApril AS Apr, MP2_ProjectedDemands.ForecastMay AS May, MP2_ProjectedDemands.ForecastJune AS Jun, MP2_ProjectedDemands.ForecastJuly AS Jul, MP2_ProjectedDemands.ForecastAugust AS Aug, MP2_ProjectedDemands.ForecastSeptember AS Sep, MP2_ProjectedDemands.ForecastOctober AS Oct, MP2_ProjectedDemands.ForecastNovember AS Nov, MP2_ProjectedDemands.ForecastDecember AS [Dec], MP2_ProjectedDemands.ForecastJanuaryNextYear, MP2_ProjectedDemands.ForecastFebruaryNextYear, MP2_ProjectedDemands.ForecastMarchNextYear, MP2_ProjectedDemands.ForecastAprilNextYear, MP2_ProjectedDemands.ForecastMayNextYear, MP2_ProjectedDemands.ForecastJuneNextYear, MP2_ProjectedDemands.ForecastJulyNextYear, MP2_ProjectedDemands.ForecastAugustNextYear, MP2_ProjectedDemands.ForecastSeptemberNextYear, MP2_ProjectedDemands.ForecastOctoberNextYear, MP2_ProjectedDemands.ForecastNovemberNextYear, MP2_ProjectedDemands.ForecastDecemberNextYear INTO MP2
FROM IM1_InventoryMasterfile INNER JOIN MP2_ProjectedDemands ON IM1_InventoryMasterfile.ItemNumber = MP2_ProjectedDemands.ItemNumber


So if today falls into the ForecastOctober and the user selects 6 months to change the qty ... it needs to update the ForecastNovember up to ForecastAprilNextYear columns.

 

[PHV]

I think it's easier to maintain rows than columns.
Have a look here:

http://r937.com/relational.html

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

 

[angelandgreg]

ok, that's a start and yes, i agree but the way the data comes out of the back office system is by columns!
go figure ... a large database software company and they designed it that way.

then what in regards to how the query (i guess a function) will update the 6 following consecutive months when the user selects to update 6 months of data?

 

[angelandgreg]

even before getting to the actual function ...

how do I add the data to the Month field for the rows of data?

I formatted the table for us by the rows to have the months.


here's my Append query that extracts the column data from the back office system.
INSERT INTO MP2_ProjDemand ( ProductLine, ItemNumber, Whsecode, [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month], [Month] )
SELECT IM1_InventoryMasterfile.ProductLine, IM1_InventoryMasterfile.ItemNumber, IM1_InventoryMasterfile.DefaultWhse, MP2_ProjectedDemands.ForecastJanuary AS Jan, MP2_ProjectedDemands.ForecastFebruary AS Feb, MP2_ProjectedDemands.ForecastMarch AS Mar, MP2_ProjectedDemands.ForecastApril AS Apr, MP2_ProjectedDemands.ForecastMay AS May, MP2_ProjectedDemands.ForecastJune AS Jun, MP2_ProjectedDemands.ForecastJuly AS Jul, MP2_ProjectedDemands.ForecastAugust AS Aug, MP2_ProjectedDemands.ForecastSeptember AS Sep, MP2_ProjectedDemands.ForecastOctober AS Oct, MP2_ProjectedDemands.ForecastNovember AS Nov, MP2_ProjectedDemands.ForecastDecember AS [Dec], MP2_ProjectedDemands.ForecastJanuaryNextYear, MP2_ProjectedDemands.ForecastFebruaryNextYear, MP2_ProjectedDemands.ForecastMarchNextYear, MP2_ProjectedDemands.ForecastAprilNextYear, MP2_ProjectedDemands.ForecastMayNextYear, MP2_ProjectedDemands.ForecastJuneNextYear, MP2_ProjectedDemands.ForecastJulyNextYear, MP2_ProjectedDemands.ForecastAugustNextYear, MP2_ProjectedDemands.ForecastSeptemberNextYear, MP2_ProjectedDemands.ForecastOctoberNextYear, MP2_ProjectedDemands.ForecastNovemberNextYear, MP2_ProjectedDemands.ForecastDecemberNextYear
FROM IM1_InventoryMasterfile LEFT JOIN MP2_ProjectedDemands ON (IM1_InventoryMasterfile.DefaultWhse = MP2_ProjectedDemands.WarehouseNumber) AND (IM1_InventoryMasterfile.ItemNumber = MP2_ProjectedDemands.ItemNumber)


Once the existing data is added into the work file in Access, this is when I'll need to do the number of months to update controlled by the user.

nix the ProjectedDemand qty field, the control field would be the current month.

So if the back office database has a qty in the current month, that is the control the user will use and if the user saws update the current qty in the current month field(which can be changed by the user) it'll update the following consecutive months with the current month field of data. HOW can I make this happen?

 

[angelandgreg]

oh yea, it won't let me append to the Month field more then one time per Append query??

 

[PHV]

Your MP2_ProjDemand table should have the following columns:
ProductLine, ItemNumber, Whsecode, MonthNumber (1 .. 24) and Forecast

Then you'll create 24 append queries:
INSERT INTO MP2_ProjDemand (ProductLine, ItemNumber, Whsecode, MonthNumber, Forecast)
SELECT M.ProductLine, M.ItemNumber, M.DefaultWhse, 1, P.ForecastJanuary
FROM IM1_InventoryMasterfile AS M LEFT JOIN MP2_ProjectedDemands AS P ON M.DefaultWhse = P.WarehouseNumber AND M.ItemNumber = P.ItemNumber

...
INSERT INTO MP2_ProjDemand (ProductLine, ItemNumber, Whsecode, MonthNumber, Forecast)
SELECT M.ProductLine, M.ItemNumber, M.DefaultWhse, 24, P.ForecastDecemberNextYear
FROM IM1_InventoryMasterfile AS M LEFT JOIN MP2_ProjectedDemands AS P ON M.DefaultWhse = P.WarehouseNumber AND M.ItemNumber = P.ItemNumber

And now to update the 6 months:
UPDATE MP2_ProjDemand SET ...
WHERE (MonthNumber-Month(Date())) Between 0 And 5

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

 

[angelandgreg]

ok, well, i can make the field name in my work table to be numeric ... that's easy enough.

that sucks I have to make 24 queries ... i was trying to avoid that.

The update part ... where and what does it know what the current month is and then dynamically know the consecutive six months?

sorry, not getting it :-(

 

[PHV]

WHERE (MonthNumber-[!]Month(Date())[/!]) Between 0 And 5
in red: current month
in purple: consecutive six months

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

 

[angelandgreg]

ah, didn't realize that they would be known that way.
thank you for your help ...


and the Between 0 and 5 is not user driven.
it won't always be 6 months, the user can decide to only update 3 months.

or what if the user wants to update certain months and not necessarily the consecutive months?

 

[PHV]

I gave you one starting point.
It's now up to you to make the update parametized.

 

[angelandgreg]

well, this won't work. i will have to add a Qty field to store the qty that is coming from the ForecastJanuary, etc ... and assign the 1 to Month and so on to the other 23 queries ...

is there another to add these without creating a query for each month?

 

[angelandgreg]

this does not work.
i get an error

The expression you entered has an invalid .(dot) or ! operator or invalid parentheses.

when i put it in a query as the criteria or under the Update field.

where in the query is this statement suppose to go then?


i really am not (as noted in my other posts) good at codes and where they should go. I am a basic user and learning little by little.

 

[PHV]

What is your actual SQL code ?

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

 

[angelandgreg]

the work table in Access now has data ...

ProductLine ItemNumber Whsecode MonthNo Qty Override? OverideDate
0005 05-00416B 010 1 0 No
0006 06-31123 010 1 0 No
0008 08-98702 010 1 0 No
0008 08-99009 010 1 0 No
0008 10-33100A 010 1 0 No
0010 10-62100B 010 1 0 No
0010 10-62110A 010 1 0 No
0010 10-82100B 010 1 0 No
0012 12-04014A 010 1 0 No
0012 12-36025B 010 1 0 No

MonthNo of 1 equates to month of January.
The qty field is the data from Forecast January.

 

[angelandgreg]

can't copy that because of the error ...

it will need to use the NoOfMonths field.

there's a form for the user to enter the number of months to change (the field NoOfMonths). the "header" table that just contains item and noofmonths

the user would enter numbers from 1 to 24. for 1 month or 24 months. although i suppose you can't change 24 months if unless you start at month 1 (January).

the table with the of the months and qty is my "details" table.

 

[PHV]

Your details table should have 230 more rows (10 with MonthNo=2, ..., 10 with MonthNo=24).

Again, what is your actual SQL code for the update ?

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

 

[angelandgreg]

i get an error where ever I put the code provided so without that here is my update query:

UPDATE MP2_ProjDemandMonths INNER JOIN MP2_ProjDemand ON MP2_ProjDemandMonths.ItemNumber = MP2_ProjDemand.ItemNumber SET;


Header and Details tables linked.

 

[PHV]

What are the header and the details table ?
Which expression you entered had an invalid .(dot) or ! operator or invalid parentheses ?
From where should the new Qty comes ?

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

 

[angelandgreg]

The header table is just:
Item
NoOfMonths

The detail table is:
Item
MonthNo (1-24)
Qty - this is the qty that comes from the ForecastJanuary, etc from the MP2_ProjectedDemands file from the back office database.

I take the data in that database to do what I'm trying set-up for a user in Access. By selecting the number of months to update based on the current month data.

Which noted the example would be:
CurrentMonth = the data in ForecastOctober

so that would translate as such from the detail table:
Item 123
MonthNo = 10
Qty = 100 that was "downloaded" from the ForecastOctober field in the MP2_ProjectedDemands file into the detail table in Access.


the header table is so the number of months is used to enter the number of months to change by the user per item regardless of which month.

I have a form with the header and then a subform of the details.

The user would enter, say, 6 for item 123.
We know that the current month (say ForecastOctober) and the qty (projected demand) is 100.

the user has an option to manually change the item's projected demand. Either way, say the user changes it to 200. Then with the number of months to update is 6 months to 200.

I need a function or query (a button to the user on the form) that'll update the subsequent months in the detail table as selected above.