Auto Column change each month?

[vbahelp07]

I have a query that links two other queries for the final result. one of the query is a crosstab query as management needs to see the month buckets in columns (hence the use of the crosstab query).

but each month it rolls showing the last 12 months. i have to manually delete the column from the 13th month and add the new current month.
is there a way to make it automatically update the months each month?


SQL:

SELECT [OnHand-ADNRs].Item AS ItemNumber, [OnHand-ADNRs].Description AS ItemDescription, [OnHand-ADNRs].ProductLineDescription AS ProductLine, [OnHand-ADNRs].Status, [OnHand-ADNRs].Theme, [OnHand-ADNRs].ReleasedDate, [OnHand-ADNRs].Reserve, [OnHand-ADNRs].PaintingHrs, [OnHand-ADNRs].[Case Pack], [OnHand-ADNRs].QOH, [OnHand-ADNRs].QOP, [OnHand-ADNRs].QOO, [12MonthBookings].[Mar-07], [12MonthBookings].[Apr-07], [12MonthBookings].[May-07], [12MonthBookings].[Jun-07], [12MonthBookings].[Jul-07], [12MonthBookings].[Aug-07], [12MonthBookings].[Sep-07], [12MonthBookings].[Oct-07], [12MonthBookings].[Nov-07], [12MonthBookings].[Dec-07], [12MonthBookings].[Jan-08], [12MonthBookings].[Feb-08], [12MonthBookings].Qty, [OnHand-ADNRs].Available, [OnHand-ADNRs].StdCost, [Available]*[StdCost] AS InvValue, [OnHand-ADNRs].StdPrice, [Available]*[StdPrice] AS WholeSale
FROM [OnHand-ADNRs] LEFT JOIN 12MonthBookings ON [OnHand-ADNRs].Item = [12MonthBookings].Item
WHERE ((([OnHand-ADNRs].Description) Not Like "*Assort*"))
ORDER BY [OnHand-ADNRs].Item;

from this next month i would detel the Mar-07 and add Mar-08 and so on each month.

The 12MonthBookings table is updated automatically on the 1st of each month that contains the updated rolling month.

 

[dhookom]

Use relative months like the solution found at faq703-5466.

Duane
Hook'D on Access
MS Access MVP

 

[vbahelp07]

ok, i am a bit confused on the month naming.
it needs to show the management team Mar08, etc ... and not Mtn1, etc ... as it doesn't really tell them anything WITHOUT having them THINK what Mtn1 could be.
the query actually has the date range using the Between and therefore has no form to enter a date range parameter for the section "Forms!frmA!txtEndDate".

Set your queries Column Headings property to:
   "Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column labels in your report, use text boxes with control sources of:
   =DateAdd("m",0,Forms!frmA!txtEndDate)
   =DateAdd("m",-1,Forms!frmA!txtEndDate)
   =DateAdd("m",-2,Forms!frmA!txtEndDate)
   =DateAdd("m",-3,Forms!frmA!txtEndDate)
   ...
You should be able to substitute other date intervals for months such as "q" for quarter or "d" for day.

 

[dhookom]

I agree users shouldn't have to "[blue]THINK what Mth1 could be[/blue]". This solution assumes you don't expose your users to datasheet views of either tables or queries. You should use either forms or reports where you aren't stuck with the column names assigned in the crosstab.

If you could provide your method for "[blue]date range using the Between[/blue]", I expect someone could show you how to replace the form control reference with something meeting your requirements.

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

Given SOME mnth(n) expressed as a date, the actual month (& year) should be a trivial exercise. e.g. mnth(0) = #3/1/2008# or some approximation, should return the month & year reasonably as :

Mnth = #3/1/2008#
? Format(DateSerial(Year(Mnth), Month(Mnth)-1, 1), "mmmyy")
Feb08

MichaelRed

 

[vbahelp07]

MichaelRed,
i do not understand ... each month changes but always showing 12 months.

This month it shows
Apr07 May07 ... Feb08 Mar08

next month i would like it to automatically delete Apr07 and add Apr08 after Mar08.

 

[dhookom]

This month it shows

What shows? Are you displaying the query datasheet to your users?

Do you have any replies to my most recent posting or am I chasing my tail


Duane
Hook'D on Access
MS Access MVP

 

[vbahelp07]

Hi Duane,
here's the query datasheet view of the SQL query noted at the beginning. this is the result (i only pasted the date columns as the other fields are fields for the items).

Mar-07	Apr-07	May-07	Jun-07	Jul-07	Aug-07	Sep-07	Oct-07	Nov-07	Dec-07	Jan-08	Feb-08
13			8	10	6	5	6	5		5	2
11	2	4	1	6	4	5	4	9	1	8	5

so each month shows the qty booked and it's a "rolling bookings" datasheet.

each month as to roll to the next month.

 

[MichaelRed]

so, given the "date" of Feb08, I assume that you could select a (any?) date within the month and (see above) derive "Feb08"? If so, then the preceding 11 months (which round out the year) are given with (again, see above ... ) the formula which returns the mnth - 1 (or any offset with a different "constant" - or HEAvens!!! VARIABLE) would yield the headings.

So what's to understand? (or not)



MichaelRed

 

[vbahelp07]

MichaelRed,
thanks ... basically for someone like myself that really do not know how to use what you have wrote is where / what I don't understand :-(


Ok, the make-table query that creates the table "12MonthBookingsDetails" which is one of the names in the query listed originally:

SELECT Trim(SO_04SOHistoryDetail.ItemNumber) AS Item, Format(IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE]),"mmm-yy") AS [Month], Sum(SO_04SOHistoryDetail.RevisedOrderQuantity) AS Quantity, Trim(IM1_InventoryMasterfile.ItemDescription) AS Description INTO 12MonthBookingsDetails
FROM ((SO_04SOHistoryDetail INNER JOIN SO_03SOHistoryHeader ON SO_04SOHistoryDetail.SalesOrderNumber = SO_03SOHistoryHeader.SalesOrderNumber) INNER JOIN SO_90_UDF_Sales_Order ON SO_04SOHistoryDetail.SalesOrderNumber = SO_90_UDF_Sales_Order.SalesOrderNumber) INNER JOIN IM1_InventoryMasterfile ON SO_04SOHistoryDetail.ItemNumber = IM1_InventoryMasterfile.ItemNumber
WHERE (((Trim([Category1]))="A" Or (Trim([Category1]))="ADNR" Or (Trim([Category1]))="N" Or (Trim([Category1])) Like "*#" Or (Trim([Category1]))="DNR") AND ((SO_04SOHistoryDetail.ItemDescription) Not Like "*Assort*") AND ((IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE])) Between MonthStart()-365 And PrevMonthStart()+30) AND ((SO_03SOHistoryHeader.Warehouse)="000" Or (SO_03SOHistoryHeader.Warehouse)="005") AND ((IM1_InventoryMasterfile.ProductLine)<>"0020" And (IM1_InventoryMasterfile.ProductLine)<>"0050" And (IM1_InventoryMasterfile.ProductLine)<>"0051" And (IM1_InventoryMasterfile.ProductLine)<>"0052"))
GROUP BY Trim(SO_04SOHistoryDetail.ItemNumber), Format(IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE]),"mmm-yy"), Trim(IM1_InventoryMasterfile.ItemDescription)
ORDER BY Trim(SO_04SOHistoryDetail.ItemNumber), Format(IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE]),"mmm-yy");

this is how it creates the columns of the 12 months each month. This automatically creates the table using AutoMate or Scheduler to launch the Macro.

once the 12MonthBookingsDetails table is created, i then have to go into the results query ("ProductionRptSummary") which is the query noted at the beginning using the months columns.

so how or where do i define that what Month is to use the "relative" months?

and if i have to define what Mth1 is (say Mar07), don't I have to change it each month. Which still does not get me anywhere. I will have to still "define" it somewhere what the Month really means (Mth1=Feb08).

how will Mth1 know next month to display Apr07, etc ...?
i don't get that. %-)

 

[vbahelp07]

MichaelRed,
thanks ... basically for someone like myself that really do not know how to use what you have wrote is where / what I don't understand :-(


Ok, the make-table query that creates the table "12MonthBookingsDetails" which is one of the names in the query listed originally:

SELECT Trim(SO_04SOHistoryDetail.ItemNumber) AS Item, Format(IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE]),"mmm-yy") AS [Month], Sum(SO_04SOHistoryDetail.RevisedOrderQuantity) AS Quantity, Trim(IM1_InventoryMasterfile.ItemDescription) AS Description INTO 12MonthBookingsDetails
FROM ((SO_04SOHistoryDetail INNER JOIN SO_03SOHistoryHeader ON SO_04SOHistoryDetail.SalesOrderNumber = SO_03SOHistoryHeader.SalesOrderNumber) INNER JOIN SO_90_UDF_Sales_Order ON SO_04SOHistoryDetail.SalesOrderNumber = SO_90_UDF_Sales_Order.SalesOrderNumber) INNER JOIN IM1_InventoryMasterfile ON SO_04SOHistoryDetail.ItemNumber = IM1_InventoryMasterfile.ItemNumber
WHERE (((Trim([Category1]))="A" Or (Trim([Category1]))="ADNR" Or (Trim([Category1]))="N" Or (Trim([Category1])) Like "*#" Or (Trim([Category1]))="DNR") AND ((SO_04SOHistoryDetail.ItemDescription) Not Like "*Assort*") AND ((IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE])) Between MonthStart()-365 And PrevMonthStart()+30) AND ((SO_03SOHistoryHeader.Warehouse)="000" Or (SO_03SOHistoryHeader.Warehouse)="005") AND ((IM1_InventoryMasterfile.ProductLine)<>"0020" And (IM1_InventoryMasterfile.ProductLine)<>"0050" And (IM1_InventoryMasterfile.ProductLine)<>"0051" And (IM1_InventoryMasterfile.ProductLine)<>"0052"))
GROUP BY Trim(SO_04SOHistoryDetail.ItemNumber), Format(IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE]),"mmm-yy"), Trim(IM1_InventoryMasterfile.ItemDescription)
ORDER BY Trim(SO_04SOHistoryDetail.ItemNumber), Format(IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE]),"mmm-yy");

this is how it creates the columns of the 12 months each month. This automatically creates the table using AutoMate or Scheduler to launch the Macro.

once the 12MonthBookingsDetails table is created, i then
have to go into the results query ("ProductionRptSummary") which is the query noted at the beginning using the months columns.

so how or where do i define that what Month is to use the "relative" months?

and if i have to define what Mth1 is (say Mar07), don't I have to change it each month. Which still does not get me anywhere. I will have to still "define" it somewhere what the Month really means (Mth1=Feb08).

how will Mth1 know next month to display Apr07, etc ...?
i don't get that. %-)

 

[vbahelp07]

i should note that the MS Access query is used in MS Excel by the team.

they Refresh (MS Query External Link) to pull the updated monthly data into Excel.

In this sheet there are other formula columns and where the Production Manager then decides to overwrite what to order, etc ... in these other columns using the data that is extrapolated from the MS Access query.

don't know if this helps but wanted to mention that and why the query Datasheet view is the only way i know how to get the data in the format the person needs to work within their master sheet that updates each month with the rolling months so they can make the weekly production orders.

 

[dhookom]

You only need to use the very simple solution that I suggested creating Mth0 through Mth11. Use MonthStart() or something similar to create your column headings in your crosstab.

In Excel, you can create column headings with expressions that add a number months to MonthStart(). There is an example in Excel Help that describes how to add months.

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

this " ... Format(IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE]),"mmm-yy") ... As Month ... " (in your statements) is basically creating the month (Duanes' "Mnth" variable for you. The only issue you have here it to "properly" align these in the order desired (oldest to newest). A small confusion is where these ordering is to occur. You mention Excel, but not much detail there other than users it there. I am not a big fan of or commion user of Excel (or spreadsheets in general) as they do not have good data typing, so if the question is with respect to ordering the columns in Excel, I'd rather you discuss the schema with Excel 'experts'. For Ms. A. (a for aged?) the soloution for the datasheet view would be to create the "table" as well as the data on hte monthly basis. You can (reasonably) easily create the table programatically, with a short routine including CreateTableDEf and CreateField, both of which are reasonably well documented in Ms. A.'s Help. Another advantage of this approach is that you may retain the old version(s) as long as there is a use for them and confidently delete them when there is not. A disadvantage is that you need to maintain the process, that is either the users will need a method (naming convention?) for the tables or you will also need to rename old versions so they are easily recognized. In either instance, retrieving the data should be somewhat simpler for the end users.



MichaelRed