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)))
