By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,175 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,175 IT Pros & Developers. It's quick & easy.

PLEASE HELP... SQL SERVER Query in Access

P: n/a
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)))

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

<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 "passthrough 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
Nov 13 '05 #2

P: n/a
Lyle Fairfield SM

Nov 13 '05 #3

P: n/a
Oh ...maybe

Lyle Fairfield
Microsoft Access SM

Cool!

Nov 13 '05 #4

P: n/a

<me*****@yahoo.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.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.
Nov 13 '05 #5

P: n/a
"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... :-)
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.