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