Dsum 4th Critieria (can be null)

[namliam]

Some "cleaner" IMHO SQL is to do something like

Select groupbyfield1, groupbyfield2, groupbyfield...., sum(), avg(), ...
From
Group by groupbyfield1, groupbyfield2, groupbyfield....

This may be personal but I find it promotes readability of your code

Also... Big SQLs like this I tend to write like:

mYSQL = mySQL & " SELECT Month([casedate])    as cMonth "
mYSQL = mySQL & "      , HD.Hosp "
mYSQL = mySQL & "      , Sum(HD.Onvent)       as Onvent "
mYSQL = mySQL & "      , Sum(HD.TotalDeath)   as TotalDeath "
mYSQL = mySQL & "      , Sum(HD.TE_Timely)    as TE_Timely " 
...

Again going towards readability

Also this (or simular) syntax will allow you to add/remove where clauses as you require/desire... Simply build a where clause something like:

if not isnull(somevariable) then 
    myWHERE = myWHERE & " AND THISFIELD = '" & somevariable & "' "
end if
etc...

Then in your sql insert/add the where clause... removing the leading AND

mySQL = mySQL & " WHERE " & mid(myWHERE, 4)

If your not sure if there is a where it gets a little more complex but if there is always some you can addapt this to your needs

Then finaly...

 "FROM dbo_HD_Summary HD "

You are still using the linked view in access, instead of using a Pass through query as I suggested in my previous post
Using a PT query will increase performance even more

 

[lcook1974]

Thanks namlaim! I'll clean it up...

When I tried the pass through query it would "create a copy" of the linked table with "1" at the end. I'm sure i was doing something wrong...

I added two screen shots of the pass through query.

Where am I going wrong on finding the

right click your title bar and setup which ODBC to use

?

 

[namliam]

That is assuming your linked view is the same as the table in your uploaded db.
Create a new query in access, just in design view but dont add anything.
Now with the query open go into the menu and got Query > Query Specific > Pass Through
Then right click your title bar and setup which ODBC to use, just like you did for the linked table/view

Guess that last part wasnt the clearest way of saying it....
You need to go into the properties of the PT query. View > Properties in the menu

 

[lcook1974]

Thank you!!! I'll modify it to come straight from there.