Efficiency: function vs. query/dlookup

[Brian]

I am trying to find the most efficient way to look up values twice-removed
from the data used to perform the lookup.

Simple example:

Company table
CompanyID (PK)
CompanyName

Client table
ClientID (PK)
CompanyID (FK)
ClientName

Invoice table
InvoiceID (PK)
ClientID (FK)

Now, after creating the obvious relationships, I want to look up the
CompanyName using the InvoiceID.

Method 1: Create a joining query and single function

SELECT Company.CompanyName
FROM Company INNER JOIN (Client INNER JOIN Invoice ON Client.ClientID =
Invoice.ClientID) ON Company.CompanyID = Client.CompanyID
WHERE (((Invoice.InvoiceID)=[Enter invoice ID]));

Public Function GetCompanyName(ID As Long) As String
GetCompanyName = DLookup("[CompanyName]", "[myQuery]", "[ClientID]=" & ID)
End Function

and call it like this (from VBA on an Invoice form):

Company = GetCompanyName(ClientID)

Method #2: Create two functions and nest them

Public Function GetCompanyID(ID As Long) As Long
GetCompanyID = DLookup("[CompanyID]", "[Client]", "[ClientID]=" & ID)
End Function

Public Function GetCompanyName(ID As Long) As String
GetCompanyName = DLookup("[CompanyName]", "[Company]", "[CompanyID]=" & ID)
End Function

Call them like this:

Company = GetCompanyName(GetCompanyID(ClientID)

Here are my questions

1. Would it be more efficient to use the nested functions, each against a
table, or a single function against a joining query?
2. Is there a better method than DLookup to use within the function(s)? The
function could be called from VBA on a form, but is more likely to be called
from within a query where I need the CompanyName for each of many records on
a list (e.g. a RowSource of a combo box).

I understand that it could be more efficient to open a recordset from within
the function, but how can I do that once for my entire query rather than once
for each record? Or is it still more efficient to open the recordset for each
record than to run a DLookup?

 

[Tim Ferguson]

I am trying to find the most efficient way to look up values

Method 1: Create a joining query and single function

Every time. Allows Jet to choose the execution plan, take advantage of
available indexes, etc. Let the server do the work!

SELECT Company.CompanyName
FROM Company INNER JOIN (Client INNER JOIN Invoice ON Client.ClientID
= Invoice.ClientID) ON Company.CompanyID = Client.CompanyID
WHERE (((Invoice.InvoiceID)=[Enter invoice ID]));

Public Function GetCompanyName(ID As Long) As String
GetCompanyName = DLookup("[CompanyName]", "[myQuery]", "[ClientID]=" &
ID) End Function

Bear in mind that this won't work, because it's a parameterised query. You
need to set up the recordset:

With Querydefs("MyQuery")
.Parameter("[Enter invoice ID]")=ID
With .OpenRecordset(dbOpenSnapshot, dbForwardOnly)
GetCompanyName = .Feilds("CompanyName")
End With
End With

The function could be called from VBA on a form, but is
more likely to be called from within a query where I need the
CompanyName for each of many records on a list (e.g. a RowSource of a
combo box).

Huh? If you want to return a set of values, create the recordset yourself.
As far as I am concerned, DLookup is no more than useful shorthand for

jetSQL = "SELECT Something FROM Somewhere WHERE Criterion"
set rs = db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)
Result = rs.Fields(1).Value
rs.Close

If I need lots of values, I change the WHERE clause to a more appopriate
criterion (and add an ORDER BY of course).

Hope that helps


Tim F

 

[Albert D.Kallal]

Using the 'join' approach will work best for speed if you are to make a
function.

So, use your 1st method.

Method 1: Create a joining query and single function

SELECT Company.CompanyName
FROM Company INNER JOIN (Client INNER JOIN Invoice ON Client.ClientID =
Invoice.ClientID) ON Company.CompanyID = Client.CompanyID

--- note how the parameter stuff was removed...you don't need that...

Public Function GetCompanyName(ID As Long) As String
GetCompanyName = DLookup("[CompanyName]", "[myQuery]", "[ClientID]=" & ID)
End Function

2. Is there a better method than DLookup to use within the function(s)?
The
function could be called from VBA on a form, but is more likely to be
called
from within a query where I need the CompanyName for each of many records
on
a list (e.g. a RowSource of a combo box).

Ouch..no!!!. You got this supper query builder, why would you all of a
sudden throw out he query builder, and now use a function in the query?
(using a function inside of that query is going to be SLOW as ice melting in
December). If you are building a combo box, and wanted to include the
company name, you would do the following:

Lets pretend the combo box is for today's date , and based on the invoice
table. However, we *want* to display the company...
(I am just making this up!!).

You would fire up the query builder, and drop in the invoice table. Lets,
see, we would put into the query builder

id InvoiceDate Invoice Description

You then drop in the Client table. If you got relationships setup, you will
need to DELETE the join line, and re-draw the join line from the ClientID in
the invoice table to the client id in the Client table (it is VERY important
which direct you draw this line). I would also double click on the join
line, and make it a LEFT join (that is a join from invoice to client -

Include ALL records from "Invoice" and only those records from
"ClientsInvoices" where the joined fields are equal

Ok, now, we don't need any fields from the client table, but if we do, we
would drop those fields into the query builder. Lets assume we want our
combo box to also show the client. So, we now got

id InvoiceDate InvoiceDescription ClientName

Ok, now drop into the query builder the company table. Again, delete any
join lines that ms-access might draw for you (on the other hand, if you got
your relationships right...it very possible that the "default" lines drawn
will be what you need, but for now I am having you delete them, and re-do
them to make sure they are right...and you learn this).

Draw a join line from CompanyID in the client table to the companyID in the
company table. Again, double click the join line, and make a left join.
(click on the "join type"

Include ALL records from "Clients" and only those records from
"Company" where the joined fields are equal


Now, in the query builder, put in the company

id InvoiceDate InvoiceDescription Company

You are done, You have a query that you can use anywhere, any time, and you
don't need a funny expression, or to write some special code. And, most
imparting while this makes reports easy to write, they will also perform
very very fast. And, it is a snap to include additional fields. What happens
in your design, or report if you all of a sudden need the company name AND
THE phone number? You don't want to start writing a ZILLION little
functions, or even writing those hard to use dlookups. Just site back, relax
drinking your coffee, you fire up that query, and drag the phone number from
the company table into he query grid, and you are done. What could be
easier?

Also, note that for general combo box, or other related tables that supple
values for combo boxes, or just general tables used for "lookup values", you
can sue the above process.

If you got 2,3 or even 10 different fields with a "id", but want to grab the
"description" part from the other tables, then just drop in those additional
tables (be it 2, or 10 extra) tables into the query builder, draw the join
lines as above (they must be left joins), and them simply include the text
description field(s) from the other tables. This is easy to do, and means
your resulting report does NOT need a bunch of funny dlookups.

Use the query builder to solve this stuff. It is faster, easier (no code),
and also much easer to add "additional" fields from any of the tables you
are trying to lookup (fetch) values from.