access query to sql server HELP!!!

Question

Post reply

access query to sql server HELP!!!

chrisc

SSC Enthusiast

Points: 162
More actions
January 8, 2004 at 12:15 pm

#106832

I have a simple access query but I can't seem to find a way to convert it to sql server. Will someone please help?

select nbr, type, last(location) as Lastoflocation from loadplans group by nbr, type having ((type) = 'X')) order by nbr;

This is for a conversion project that is going into production in a couple of days.

Thanks, Chris

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

SQLBill

SSC Guru

Points: 51440

January 8, 2004 at 12:22 pm

#489196

Try replacing LAST with MAX.

-SQLBill

chrisc SSC Enthusiast Points: 162 More actions · Answer 2

Thanks Bill but I tried max and min with the output not being the same as using Last in Access. Any other ideas?

Chris

SJTerrill SSCrazy Points: 2938 More actions · Answer 3

If this is the error you're getting:

Column '<column_name>' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

then try replacing your HAVING clause with a WHERE clause before your GROUP BY. Y'know: WHERE type = 'X' instead of HAVING type = 'X'.

Regards,

--SJTerrill

chrisc SSC Enthusiast Points: 162 More actions · Answer 4

I still can't get this to work. I am not getting any errors. The data is not reporting right. I could really use any advice. Could I use a row id or something?

Chris

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Basically if I remember correct, there is no equivalent to Last or First in T-SQL. This is simply like returning the last or first row in a resultset. And unless you don't specify an ORDER BY clause, there is absolutely no guarantee (especially in SQL Server) that each time same row is returned.

Your best bet is to experiment with MAX as SQLBill already suggested

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ridok SSC Veteran Points: 290 More actions · Answer 6

try with this...

1.- add a new column in an access table

Name : RowID

Type : Autonumeric

2.- Load the data in your SQLServer Table, with the same new structure

3.- and try with this query in sql server

select a.nbr, a.type, a.location as lasttoflocation

from loadplans a,

(select nbr, type, max(rowid) as rowid from loadplans where type = 'X' group by nbr, type ) as b

where a.nbr = b.nbr and a.type = b.type and a.rowid = b.rowid order by a.nbr

i'm sure that my suggestion isn't the best, but, considerig the time and the urgent...

i hope this help you

:.::.:.::

gljjr SSChampion Points: 10080 More actions · Answer 7

As Frank stated I don't think there is a LAST or FIRST but you can certainly use the ORDER BY with TOP 1

select nbr, type, last(location) as Lastoflocation from loadplans group by nbr, type having ((type) = 'X')) order by nbr;

to

SELECT TOP 1 nbr, type, location FROM loadplans WHERE type = 'X' ORDER BY nbr DESC

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Ridok SSC Veteran Points: 290 More actions · Answer 8

but... what happens if the location column type is text? and the order of insert is c, a, d, g, f, e? the results of query must be 'e' no 'g' or 'a'.

:.::.:.::

gljjr SSChampion Points: 10080 More actions · Answer 9

Then I would hope you have a datetime column or identity column that you could do the order by on.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.