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.

[mburns08109]

You are using MSQuery to open anAccess databse and get the data into Excel, right?

This is sort of like using a Honda-labelled Hand Truck to lift a pallette of cargo to put into a tractor trailer, when another Honda-labelled forklift is sitting right beside it ready to go.

Do this right from Access and save yourself the needless messing around with MSQuery!

Access will do this with, mmm 1 comand?

docmd.transferspreadsheet

[mikael3432]

I dont know about docmd.transferspreadsheet

but can I use parameter with that?
Can I update with it?

[NeoPa]

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

The syntax rules for using SQL from MSQuery are not the same as for running SQL natively in Access. The Nz() function is an example of something available only when running Jet-SQL in Access. It won't work from Excel (or MSQuery).

[NeoPa]

MBurns08109:
This is sort of like using a Honda-labelled Hand Truck to lift a pallette of cargo to put into a tractor trailer, when another Honda-labelled forklift is sitting right beside it ready to go.

I'm not sure I'm with you on your simile.

DoCmd.TransferSpreadsheet() is certainly available, and may well prove preferable in this case (I don't know enough about the OP's requirements to know), but there are certainly pros and cons to each approach. Sometimes using MSQuery is the most appropriate approach. It depends on many factors. MSQuery can be a very powerful and useful tool for dealing with data from within Excel. You have the added bonus of formatting the data more easily without needing to use Application Automation (which is often a very important consideration).

[NeoPa]

Mikael3432:
Can I update with it?

You can send data to the spreadsheet. You may be able to append to existing data, but I suspect not. I think it replaces any current data but you'd need to check to confirm that. I'm pretty sure you cannot use it as you might an UPDATE query and change data within pre-existing rows.

[mikael3432]

I have tried using export in access. Sure it works fine with 1 or 2 excel files and you can save the export.
But if you have alot of excel files, it takes time to do it. Especially when you need to type in different parameters each time. What is the best way to solve this?

[NeoPa]

Solve what?

What are you asking?

[mikael3432]

what is the best method. export from access or import from excel? Given that you need to do that to several excel files.

[NeoPa]

If you're asking that as an unrelated question, I would say that exporting from Access would be my preferred route.

However, this works best when dealing with straightforward transfers of data. Depending on your requirements beyond that (Merging with existing data; formatting so the data looks nice and is easier for operators to work with), there may be Excel specific work to be done. This is more easily done from within Excel, but can be done from the Access process itself using Application Automation.

Many transfers certainly points towards an Excel approach, but as I say, there may be other factors involved for your requirements.