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:
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.
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:
Code:
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.