Access Update Query

[Guest]

I need to convert this SQL statement to a statement that Access 2003 will
run. Any suggestions?

I have two tables. GL_ALL contains all imported records with blank Period
and Week but the Import date is poppulated with a calendar date.

Fiscal_Yr_Table contains the calendar for the fiscal year. It has the
period, week, start date and end date for each period and week

I want to update the GL_ALL table with the proper Period and Week from the
Fiscal_Yr_Table based on the Import date in the GL_ALL table.

UPDATE GL_ALL
SET GL_ALL.Period = Fiscal_Yr_Table.Period, GL_ALL.Week=Fiscal_Yr_Table
FROM Fiscal_Yr_Table,GL_All
WHERE (GL_ALL.Import_Date>=Fiscal_Yr_Table.Start_Date) AND
(GL_ALL.Import_Date<=Fiscal_Yr_Table.End_Date);

This works in SQL server but not in Access

 

[MGFoster]

I need to convert this SQL statement to a statement that Access 2003 will
run. Any suggestions?

I have two tables. GL_ALL contains all imported records with blank Period
and Week but the Import date is poppulated with a calendar date.

Fiscal_Yr_Table contains the calendar for the fiscal year. It has the
period, week, start date and end date for each period and week

I want to update the GL_ALL table with the proper Period and Week from the
Fiscal_Yr_Table based on the Import date in the GL_ALL table.

UPDATE GL_ALL
SET GL_ALL.Period = Fiscal_Yr_Table.Period, GL_ALL.Week=Fiscal_Yr_Table
FROM Fiscal_Yr_Table,GL_All
WHERE (GL_ALL.Import_Date>=Fiscal_Yr_Table.Start_Date) AND
(GL_ALL.Import_Date<=Fiscal_Yr_Table.End_Date);

This works in SQL server but not in Access

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps:

UPDATE GL_ALL AS GL INNER JOIN Fiscal_Yr_Table AS FY
ON GL.Import_Date BETWEEN FY.Start_Date AND FY.End_Date
SET GL.Period = FY.Period,
GL.Week = FY.Week

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjIHMIechKqOuFEgEQLoCgCeLUmlGDvqxidczSXU/MRTu/oiTDUAoKk5
eoKwF8oenNnxinHTBQOKByON
=PyOi
-----END PGP SIGNATURE-----

 

[Guest]

I'm getting an error of "Between operator without And in query expresion
'GL.Import_Date BETWEEN FY.Start_Date'." ?

 

[MGFoster]

You didn't copy the "AND FY.End_Date" part of the line.

 

[Guest]

I copied it straight from your response.

You didn't copy the "AND FY.End_Date" part of the line.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

So maybe Access doesn't "like" an INNER JOIN on an UPDATE, especially w/
a BETWEEN in the ON clause. Try this:

UPDATE GL_ALL AS GL, Fiscal_Yr_Table AS FY
SET GL.Period = FY.Period,
GL.Week = FY.Week
WHERE GL.Import_Date BETWEEN FY.Start_Date AND FY.End_Date

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjJq8oechKqOuFEgEQLXCwCfTOJozPhM8FD3vR4R/iTeR/Un7gQAnj7G
mMJcTVzqWPqEzMOzMfsQE3u5
=BOHP
-----END PGP SIGNATURE-----

 

[Guest]

That worked. Thank you.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

So maybe Access doesn't "like" an INNER JOIN on an UPDATE, especially w/
a BETWEEN in the ON clause. Try this:

UPDATE GL_ALL AS GL, Fiscal_Yr_Table AS FY
SET GL.Period = FY.Period,
GL.Week = FY.Week
WHERE GL.Import_Date BETWEEN FY.Start_Date AND FY.End_Date

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjJq8oechKqOuFEgEQLXCwCfTOJozPhM8FD3vR4R/iTeR/Un7gQAnj7G
mMJcTVzqWPqEzMOzMfsQE3u5
=BOHP
-----END PGP SIGNATURE-----