What is code inside Dlookup/DSum ?

[hvsummer]

I want to know what is the code inside D-Family function.
Does it use ADO or DAO recordset to perform ?

I am going to write a vba function that let's me manually create output that SQL couldn't give me without decreasing too much performance due to overwhelming complex query.

so can anyone tell me which way to pull data from table fastest (except SQL) ?
SQL good on pull and process basic data, but when thing goes complex, I think let's VBA handle better, am I wrong ?

[mbizup]

What are you using this for?

In forms and reports, you can use the SUM/AVG/MAX etc functions to total records in the header/footer sections.

In VBA, you can do something like this (but I'm not sure what the relative performance would be:

Function AddItUp(strFieldName as string, strTableName as string) as Double()
    dim rs as DAO.recordset
    dim strSQL as string
    strSQL = "SELECT SUM(" & strFieldName & ") AS Total FROM " & strTableName 
    set rs = currentdb.openrecordset(strsql, dbfailonerror)
    AdditUp = rs("Total")

   rs.close
   set rs = nothing

' Add solid error handling, handling for 'no records found', etc

End Function

Also, the domain aggregate functions are not always a bad choice. It depends on HOW you are using them (which is why I asked in my previous post).

They are generally a poor choice in queries... where you may see better performance using sub queries instead.

[Rabbit]

Without any specifics, the answer is, it depends.

[NeoPa]

As the Domain Aggregate query functions are inbuilt it's hard for us to know how they're coded and what they're built on.

My own ** Guess ** would be that they use DAO, but that's just a guess.

[hvsummer]

can we ask mircosoft speakup team bout this ?
I was planning to replace some very complex query structure by 1 UDF vba to simplize those SQL's code and increase performance.

In fact, I have to use statistic function, built-in funcion not enough, so I'll create UDF or write complex query == I'm collecting information to compare whichone will bring me better performance.

[Rabbit]

What is it that you're trying to do? It might be perfectly doable in SQL

[NeoPa]

Something to look at if you're going along that path is Allen Browne's work on similar lines (Getting a value from a table: DLookup()).

[hvsummer]

my SQL code's too complex to show detail here,
I'll split it into 3 simple-version parts/proccesses.

firstly I have 3 tables
table1 = {Distr, Cust, BDate, ItemCode, Qty, Price}
table2 = {ItemCode, PriceMin, PriceMax}
table3 = {ItemCode.Value , P_SDate, P_EDate , PromotionName, PrPrice}

Part/Proccess 1 (Qry1) - Generate Current Price of Item

SELECT ItemCode, 
sum(iif(BDate between P_SDate and P_EDate, PriceMin-PrPrice, 0)) as PMin,
sum(iif(BDate between P_SDate and P_EDate, PriceMax-PrPrice, 0)) as PMax,
FROM Table2 Inner Join Table3 On ItemCode = ItemCode.Value
Group By ItemCode

Part/Proccess 2 (Qry2) - Compare Current Price from Qry1 to Table1 (on November)

SELECT
Distr, Cust, BDate, ItemCode, Qty, Switch(Price < PMin, "Check Bill, Price's too low", Price > PMax, "Check Bill, Price's too high, True, ".") as Status
FROM Table1 Inner Join Qry1 ON Table1.ItemCode = Qry1.Itemcode
WHERE Month(BDate) = 11
ORDER BY Distr, Cust, BDate

Part/Proccess 3 (Qry3) - analysis statistics with result from Qry2

SELECT Distr, ItemCode, Q.Price as PriceMode, P.Price as PriceWeighted
FROM
(
  SELECT Distr, Cust, ItemCode, Price, Count(Price) as Frequency
  FROM Qry2
  WHERE Status <> "."
  GROUP BY Distr, Cust, ItemCode, Price, Month(BDate)
  WHERE Month(BDate) = 11
  ORDER BY Distr, Cust
  HAVING Max(Frequency)
) AS Q INNER JOIN 
(
  SELECT Distr, Cust, ItemCode, Price, Qty, (Qty/Total-MTD) AS %Weighted
  FROM 
  (SELECT Distr, ItemCode, Sum(Qty) as Total-MTD
   FROM Table1
   WHERE Month(BDate) = 11
   GROUP BY Distr, ItemCode ) AS A
   GROUP BY Distr, Cust, ItemCode, Price
   HAVING Max(Qty/Total-MTD)
) AS P ON Q.ItemCode = P.ItemCode AND Q.Cust = P.Cust AND Q.Distr = P.Distr
ORDER BY Distr, ItemCode

There are a lot of condition that I don't put it in here.
Proccess 2 take about 1 mins to finish.
Proccess 3 I think It'll take 5mins to finish (and will be longer since my KPI_ItemCoverag e_Query run over 5mins)
has to mention, my DataBase reached 1.6 million rows.

I plan to use DAO or ADO instead using the complex SQL as qry3..
I think in this situation, VBA can handle better than SQL ==