PLEASE HELP... SQL SERVER Query in Access

[meyvn77]

I wrote a bunch of SQL statement in SQL SERVER and now im trying to do
the same thing in access. OMG what a pain that Access can't handle
updates or CAST() the way SQL server can. OK I thought I wrote
everything in Ansi standard SQL statement so it should be compatable?
right?
The problem is i'm going from a relational format to a flat file fomat
and this is only a 15th of the accual query. Please tell me their is a
way to use this type of update query in Access.


UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT MIN(HARMFULEVENT)
FROM HARMFULEVENT AS A
WHERE ((GIS_EVENTS_TEMP.CASEID =
A.CRASHNUMBER) AND (A.UNITID = 1 AND A.LISTORDER = 0))),

SNDHARM1 =
(SELECT MIN(HARMFULEVENT)
FROM HARMFULEVENT AS A
WHERE ((GIS_EVENTS_TEMP.CASEID =
A.CRASHNUMBER) AND (A.UNITID = 1 AND A.LISTORDER = 1))),

FSTHARM2 =
(SELECT MIN(HARMFULEVENT)
FROM HARMFULEVENT AS A
WHERE ((GIS_EVENTS_TEMP.CASEID =
A.CRASHNUMBER) AND (A.UNITID = 2 AND A.LISTORDER = 0))),

SNDHARM2 =
(SELECT MIN(HARMFULEVENT)
FROM HARMFULEVENT AS A
WHERE ((GIS_EVENTS_TEMP.CASEID =
A.CRASHNUMBER) AND (A.UNITID = 2 AND A.LISTORDER = 1))),

FSTHARM3 =
(SELECT MIN(HARMFULEVENT)
FROM HARMFULEVENT AS A
WHERE ((GIS_EVENTS_TEMP.CASEID =
A.CRASHNUMBER) AND (A.UNITID = 3 AND A.LISTORDER = 0))),

SNDHARM3 =
(SELECT MIN(HARMFULEVENT)
FROM HARMFULEVENT AS A
WHERE ((GIS_EVENTS_TEMP.CASEID =
A.CRASHNUMBER) AND (A.UNITID = 3 AND A.LISTORDER = 1)))

 

[Chris2]

I wrote a bunch of SQL statement in SQL SERVER and now im trying to do
the same thing in access. OMG what a pain that Access can't handle
updates or CAST() the way SQL server can. OK I thought I wrote
everything in Ansi standard SQL statement so it should be compatable?
right?
The problem is i'm going from a relational format to a flat file fomat
and this is only a 15th of the accual query. Please tell me their is a
way to use this type of update query in Access.

meyvn77,

AFAIK, you may not put a subquery after the = operator on the SET
clause of the UPDATE statement in MS Access.

(Oh, and in place of CAST, use the VBA convert functions. CStr(),
CInt(), etc.)

Try:

UPDATE <table-name>
<your join type>
<table-name>
ON <column-name> = <column-name>
SET <column-name> = <expression>
WHERE <criteria>


Sincerely,

Chris O.