Is it possible to speed up this? SQL

[mikael3432]

Hey

Im wondering if its possible to speed up this code.

Taking ages.

SELECT Tabell1.Pnr, Tabell1.Handk, Tabell1.`From`, Tabell1.lon, Tabell1.Fri, Tabell1.Hsfil
FROM fors Tabell1
WHERE (Tabell1.Hsfil = (SELECT MAX(Tabell2.Hsfil)
FROM fors Tabell2
WHERE Tabell2.From <=20050101 and Tabell1.Handk <> 40 and Tabell1.Pnr = Tabell2.Pnr))

thanks

[NeoPa]

Does fors have some special meaning?

I cannot see how the SQL would work as the FROM clause seems to be invalid.

[mikael3432]

no, fors is an access table.

[mikael3432]

the code picks the max value of Hsfil and filter before and after this max event.

[NeoPa]

Michael3432:
no, fors is an access table.

Of course. My memory.

FROM Fors Tabell1

is just another way of saying :

FROM Fors AS Tabell1

Let me look at this again now I understand what I'm looking at :D

[NeoPa]

Try this. I'm not sure exactly what you're trying to do as your SQL is a little confused, but this should give the same results, only a little more quickly I would hope :

SELECT [Pnr]
     , Handk
     , [From]
     , [lon]
     , [Fri]
     , [Hsfil]
FROM   fors AS Tabell1 INNER JOIN
    (
    SELECT [Pnr]
         , Max(Hsfil) AS MaxHSFil
    FROM   fors
    WHERE  ([From]<=20050101)
    ) AS Tabell2
  ON   Tabell1.Pnr=Tabell2.Pnr
 AND   Tabell1.Hsfil=Tabell2.MaxHSFil
WHERE  (Tabell1.Handk<>40)

[mikael3432]

thanks. I will try this.

[mikael3432]

got an error. Think its this:

SELECT [Pnr] , Max(Hsfil) AS MaxHSFil

cant show both pnr (several values) and Max (one value)

help

[NeoPa]

I did leave out one small, but important, clause from the subquery (Ooops!). Try this instead :

SELECT [Pnr]
     , Handk
     , [From]
     , [lon]
     , [Fri]
     , [Hsfil]
FROM   fors AS Tabell1 INNER JOIN
    (
    SELECT   [Pnr]
           , Max(Hsfil) AS MaxHSFil
    FROM     fors
    WHERE    ([From]<=20050101)
    GROUP BY [Pnr]
    ) AS Tabell2
  ON   Tabell1.Pnr=Tabell2.Pnr
 AND   Tabell1.Hsfil=Tabell2.MaxHSFil
WHERE  (Tabell1.Handk<>40)

[mikael3432]

thanks. I will try this

[mikael3432]

I got this working and its alot faster. Thanks.

Now Im trying to change:
([From]<=20050101)

to ([From]<=?)
using a parameter, but get following error:

parameters are not allowed in queries that can't be displayed graphically

do you know any solution to this?

[NeoPa]

I'm not clear on this Mikael.

It looks to me like a query that can be displayed. I don't know about graphically, but I've not come across that error before. Maybe ? is treated specially. Try [?].

Otherwise there are a few techniques that can be used, but which is best depends on how you are planning on using the query, which we don't know anything about as yet.

[mikael3432]

? or [?] works fine in access, but in MSQuery it dosnt work with JOIN, known error.

Im gonna have several excel sheets (each month), all using this query and it would be much easier if I can change From in the sheet as a parameter instead of going into the code and change From every time.

[NeoPa]

It would help if you responded fully to my query Mikael, rather than dropping incidental info into the conversation and allowing me to deduce what is going on from what you let slip.

My deduction :
You are attempting to run your sql from Excel using MSQuery.

My response :
Wow. How can we have got so far without your even mentioning one of the most important pieces of information. This is an Access / VBA forum. If you're talking about non-Access SQL you need to say that up front (I'm so surprised I need to say this).

If this is the case then the technique I would use is to update the SQL string before you even submit it, to contain the actual value entered as a literal (using the correct delimiters of course). I forget the rules for MS-Query strings, but I suspect that they may require double-quotes (") instead of the single-quotes (') which are correct everywhere else.

See Quotes (') and Double-Quotes (") - Where and When to use them and Literal DateTimes and Their Delimiters (#) for other types of literals.

[mikael3432]

I dont know what you mean by non-Acess SQL.
Im using MSQuery to import an Microsoft Access table into excel.