I inherited an in house built program that is being migrated from Access 2003 / SQL 2003 to Access 2010 and SQL 2012. There are queries that are breaking due to changes in SYNTAX etc...
Here is my current problem, please help!
original query that works on 2003 -
"LEFT(DATENAME(month, dbo.Months.FirstOfMonth), 3) + N', ' + RIGHT(DATENAME( dbo.Months.Year, dbo.Months.FirstOfMonth), 2) AS ShipMonth, dbo.Months.YearDotMonth
The problem is that the column within the dbo.Months table is called "Year" which breaks the ability to query it on the newer Access/SQL. I understand that Year has SQL Usage now and that is why I am getting the error "dbo.Months.Year" is not a recognized datename option. When I run the update query to change it to dbo.Months.[Year] within the visual studio database editor, it wont complete the command and still gives me the error.
I did change that, but the results set is organized incorrectly then. The organization is by the column data, so the query needs to utilize the column data.
From what I can read, you want to "calculate" the ShipMonth, and the format should be "MMM, YY" - correct?
The "datename(month,.....)" extracts the month name and "datename(year,......) will extract the year.
Now - which field contains the shipdate? That's the field name you should put after the comma in the datename function.
Hope this makes sence.
I am getting all of the correct data, the issue is that the result set is ordered by Alphabetical Month rather than most recent ship date to oldest ship date. change in bold. Basically, I am wanting the ordering to be the same as the result from the old query.
Thank you in advance!!
/* old query */
CREATE VIEW dbo.qry_Production_Values_3
AS
SELECT TOP 100 PERCENT dbo.qry_Production_Values_2.TotalValue, dbo.qry_Production_Values_2.Casework, dbo.qry_Production_Values_2.Custom,
dbo.qry_Production_Values_2.Buyout, dbo.qry_Production_Values_2.BoxTotal, dbo.qry_Production_Values_2.BoxRemaining,
dbo.qry_Production_Values_2.BoxTotal / dbo.Months.WorkDaysInMonth AS BoxPerWDay, dbo.qry_Production_Values_2.HrsTotal,
dbo.qry_Production_Values_2.HrsRemaining, dbo.qry_Production_Values_2.HrsTotal / dbo.Months.WorkDaysInMonth AS HrsPerWDay,
LEFT(DATENAME(month, dbo.Months.FirstOfMonth), 3) + N', ' + RIGHT(DATENAME(dbo.Months.year, dbo.Months.FirstOfMonth), 2) AS ShipMonth,
dbo.Months.YearDotMonth
FROM dbo.Months LEFT OUTER JOIN
dbo.qry_Production_Values_2 ON dbo.Months.YearDotMonth = dbo.qry_Production_Values_2.YearDotMonth
WHERE dbo.qry_Production_Values_2.TotalValue IS NOT NULL AND dbo.qry_Production_Values_2.TotalValue <> 0
ORDER BY dbo.Months.FirstOfMonth DESC
/* new query */
CREATE VIEW dbo.qry_Production_Values_3
AS
SELECT TOP 100 PERCENT dbo.qry_Production_Values_2.TotalValue, dbo.qry_Production_Values_2.Casework, dbo.qry_Production_Values_2.Custom,
dbo.qry_Production_Values_2.Buyout, dbo.qry_Production_Values_2.BoxTotal, dbo.qry_Production_Values_2.BoxRemaining,
dbo.qry_Production_Values_2.BoxTotal / dbo.Months.WorkDaysInMonth AS BoxPerWDay, dbo.qry_Production_Values_2.HrsTotal,
dbo.qry_Production_Values_2.HrsRemaining, dbo.qry_Production_Values_2.HrsTotal / dbo.Months.WorkDaysInMonth AS HrsPerWDay,
LEFT(DATENAME(month, dbo.Months.FirstOfMonth), 3) + N', ' + RIGHT(DATENAME( yyyy, dbo.Months.FirstOfMonth), 2) AS ShipMonth,
dbo.Months.YearDotMonth
FROM dbo.Months LEFT OUTER JOIN
dbo.qry_Production_Values_2 ON dbo.Months.YearDotMonth = dbo.qry_Production_Values_2.YearDotMonth
WHERE dbo.qry_Production_Values_2.TotalValue IS NOT NULL AND dbo.qry_Production_Values_2.TotalValue <> 0
ORDER BY dbo.Months.FirstOfMonth DESC
I will make the noted changes and see what happens. Thanks again for the quick responses.
