Converting a MS Access query to SQL
-
I am attempting to move a database from MS Access to SQL Server. For the most part things have gone well, but I have run into some issues in replicating an Access query. In particular I cannot convert the WHERE statement.
Any help would be greatly appreciated.MS Access query:
SELECT qry_Union_Vacation_Quotas.CalendarID, tbl_Dates.Dates, qry_Union_Vacation_Quotas.DayNumber, qry_Union_Vacation_Quotas.Quota
FROM tbl_Dates, qry_Union_Vacation_Quotas
WHERE (((tbl_Dates.Dates)>=[Start_Date] And (tbl_Dates.Dates)<=[End_Date]) AND ((qry_Union_Vacation_Quotas.DayNumber)=Weekday([Dates])))
GROUP BY qry_Union_Vacation_Quotas.AbsenceQuotaTypeID, IIf([AbsenceQuotaTypeID]=1,1,2), tbl_Dates.Dates, qry_Union_Vacation_Quotas.DayNumber, qry_Union_Vacation_Quotas.Quota
ORDER BY qry_Union_Vacation_Quotas.AbsenceQuotaTypeID, tbl_Dates.Dates;SQL Server query that I attempted:
SELECT dbo.View_Quota_Base.CalendarID, dbo.View_Quota_Base.Quota, dbo.View_Quota_Base.DayNumber,dbo.tbl_Dates.DatesFROM dbo.View_Quota_Base CROSS JOIN dbo.tbl_Dates
WHERE (dbo.tbl.Dates.Dates >= dbo.View_Quota_Base.FromDate) AND(dbo.tbl.Dates.Dates <= dbo.View_Quota_Base.ToDate) AND(dbo.View_Quota_Base.DayNumber)=DATEPART(WEEKDAY,[Dates])
ORDER BY dbo.tbl_Dates.Dates
Error Message:
- ordnance1 - Monday, April 23, 2018 9:10 AMI am attempting to move a database from MS Access to SQL Server. For the most part things have gone well, but I have run into some issues in replicating an Access query. In particular I cannot convert the WHERE statement.
Any help would be greatly appreciated.MS Access query:
SELECT qry_Union_Vacation_Quotas.CalendarID, tbl_Dates.Dates, qry_Union_Vacation_Quotas.DayNumber, qry_Union_Vacation_Quotas.Quota
FROM tbl_Dates, qry_Union_Vacation_Quotas
WHERE (((tbl_Dates.Dates)>=[Start_Date] And (tbl_Dates.Dates)<=[End_Date]) AND ((qry_Union_Vacation_Quotas.DayNumber)=Weekday([Dates])))
GROUP BY qry_Union_Vacation_Quotas.AbsenceQuotaTypeID, IIf([AbsenceQuotaTypeID]=1,1,2), tbl_Dates.Dates, qry_Union_Vacation_Quotas.DayNumber, qry_Union_Vacation_Quotas.Quota
ORDER BY qry_Union_Vacation_Quotas.AbsenceQuotaTypeID, tbl_Dates.Dates;SQL Server query that I attempted:
SELECT dbo.View_Quota_Base.CalendarID, dbo.View_Quota_Base.Quota, dbo.View_Quota_Base.DayNumber,dbo.tbl_Dates.DatesFROM dbo.View_Quota_Base CROSS JOIN dbo.tbl_Dates
WHERE (dbo.tbl.Dates.Dates >= dbo.View_Quota_Base.FromDate) AND(dbo.tbl.Dates.Dates <= dbo.View_Quota_Base.ToDate) AND(dbo.View_Quota_Base.DayNumber)=DATEPART(WEEKDAY,[Dates])
ORDER BY dbo.tbl_Dates.Dates
Error Message:
You have a typo in your query. You changed an underscore with a point. Try to use table aliases to keep your queries simpler.
SELECT vqb.CalendarID,
vqb.Quota,
vqb.DayNumber,
d.Dates
FROM dbo.View_Quota_Base AS vqb
JOIN dbo.tbl_Dates AS d ON d.Dates >= vqb.FromDate
AND d.Dates <= vqb.ToDate
AND vqb.DayNumber=DATEPART(WEEKDAY,d.Dates)
ORDER BY Dates -
Thank you so much for your help and the useful tip.
-
By the way, I strongly recommend that you add the week day number column to your Dates table, as well as other relevant attributes (year, month, week, fiscal year, etc)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply