When are "where conditions" applied by Access?

[kd2017]

This is more of a general question I'd like to discuss as I'm learning MS Access.

Let's say that I'm in the macro builder and I've created a LookupRecord data block.

Look Up A Record In: SavedQuery
   Where Condition = [SavedQuery].[TypeID] = 6

And let's say that SavedQuery is the following SQL that sums one currency field and groups by a "type" field:

SELECT TypeID, Sum(Amount) AS Total
FROM ExampleTable
GROUP BY TypeID;

This ExampleTable could have a few thousand records. And note this SavedQuery doesn't have a where clause.


My Question: In this scenario when I run that macro, will Access first efficiently find all the TypeID 6's and then sum them up
-OR-


will it execute the full query first, processing the full table, then pass that data to the macro to find TypeID 6?

I have a feeling it logically has to execute the slow way... If so does anyone know of a more efficient way to accomplish such a task in a macro? (Pull the TypeID 6's first then sum just the data I need.)

I'm just thinking about processing efficiency. Can anyone recommend some reading material as it pertains to MS Access?

[CJ_London]

with group by queries there are two types of criteria, WHERE and HAVING. WHERE is applied to the initial recordset so only those records are returned. HAVING is applied to the 'grouped' recordset and should only be used on fields that are summed, counted etc

this is most efficient because only records where fld1=6 are returned and then summed

SELECT fld1, sum(fld2)
FROM myTable
WHERE fld1=6
GROUP BY fld1
HAVING sum(fld2)>30

this is not

SELECT fld1, sum(fld2)
FROM myTable
GROUP BY fld1
HAVING fld1=6 sum(fld2)>30

because all the records need to be returned, grouped and summed and then subsequently ignored if fld1<>6

note that the docmd.openform and openreport functions have a WHERE parameter. This is misleading, it is actually a filter - a HAVING if you like. This is easily demonstrated if you leave the navigation box displayed on the form. You will see the filter option can be clicked to display all the records.

So to answer your question 'will Access first efficiently find all the TypeID 6's and then sum them up',- in the context of how you are obtaining the data, No. However for small amounts of data (a few thousand records) the difference in performance will be barely noticeable.

You would be better to use parameters in your query which you then set in the querydef before running it. I don't use macros so cannot advise whether this is even possible with a macro. If you are planning to use access a lot, you would be better to use VBA.

[kd2017]

I am fine with using VBA, but at the moment I'm studying how to use "Data Macros" for data validation at the table level. As far as I know I can't use VBA here.

The WHERE parameter is actually a filter... ahah! This is the kind of info I'm lookin for!

I'm trying to find if I can use a parameter in my query via a macro but so far it doesn't look promising.

[CJ_London]

data macros are quite limited in what you can do with them. And you need to do data validation before you save or update the record. Normally you would do this in your form (to handle before saving), or you can use more limited validation in the field validation property (to prevent saving, then handle the error generated).