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(HARMFULEVEN T)
FROM HARMFULEVENT AS A
WHERE ((GIS_EVENTS_TE MP.CASEID =
A.CRASHNUMBER) AND (A.UNITID = 1 AND A.LISTORDER = 0))),

SNDHARM1 =
(SELECT MIN(HARMFULEVEN T)
FROM HARMFULEVENT AS A
WHERE ((GIS_EVENTS_TE MP.CASEID =
A.CRASHNUMBER) AND (A.UNITID = 1 AND A.LISTORDER = 1))),

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

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

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

SNDHARM3 =
(SELECT MIN(HARMFULEVEN T)
FROM HARMFULEVENT AS A
WHERE ((GIS_EVENTS_TE MP.CASEID =
A.CRASHNUMBER) AND (A.UNITID = 3 AND A.LISTORDER = 1)))

[Larry Linson]

<me*****@yahoo. com> wrote in

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?

It _is_ frustrating, isn't it, when we don't do our homework and expect
something to be something that it is not? Most any research at all would
have revealed that Access has its own dialect of SQL, but the same is true
to a greater or lesser extent, of all the "oh-so-standard" server databases.
Who told you that Access was "just a smaller edition of SQL server"? If you
believed them, I have a dull orange bridge over San Francisco Bay that I can
get you a great _deal_ on.

Which approache to using Access with SQL Server are you using? If you are
using an MDB linking tables via ODBC, you can use the Query Builder or use
the Access "flavor" of queries -- the only queries that need be in SQL
understandable by SQL Server are "passthroug h queries". DAPs are another
matter.

There is the neatest Query Builder in Access, that writes SQL for you. I am
not the only one who tends to consider those who begin by writing SQL from
scratch in Access to be "SQL masochists". Then, if you can't quite express
your desires with the visual UI, you can _modify_ the basic SQL that the
Query Builder has constructed.

Larry Linson
Microsoft Access MVP

[lylefair]

Lyle Fairfield SM

[lylefair]

Oh ...maybe

Lyle Fairfield
Microsoft Access SM

Cool!

[Chris2]

<me*****@yahoo. com> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.com...

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 [...]

meyvn77,

But not nearly as painful as multiposting.
Sincerely,

Chris O.

[Larry Linson]

"lylefair" <ly***********@ aim.com> wrote

Oh ...maybe

Lyle Fairfield
Microsoft Access SM

Cool!

Oh, well... we have tried our best to save you from yourself, but it
obviously didn't work. <G,D,&R> On the other hand, perhaps you can start a
cult and get rich... :-)