You should be able to use VBA functions and expressions in the ON clause,
sometimes you *need* to add parenthesis to help the parser which is known to
fail, sometime, on complex expressions left without parentheses to
delimitate the various parts of the query.
Now, for the not trivial part: what is faster. There is no real answer, out
of context, since, as example,
------------------
SELECT x, DSUM("y", "tableName", "x<=" & x)
FROM myTable
-------------------
can be AND slower AND faster than
--------------------
SELECT z.x, (SELECT SUM(t.y) FROM tableName AS t WHERE t.x<= z.x)
FROM myTable AS z
--------------------
which, itself, can be and slower and faster than
--------------------
SELECT z.x, SUM(t.y)
FROM myTable AS z INNER JOIN myTable AS t
ON t.x<=z.x
GROUP BY z.x
---------------------
while the three queries return the same result (if there is no duplicated
value under column x) and operates one the same table!
The first query can be NOTICEABLY slower if you display, say, 30 records at
the same time on a form, than any of the others two queries, BUT, if you
display just one record, out of 1000, the first one can be NOTICEABLY
faster, even if you make a movelast and then a movefirst on the recordset
that use the query (to be sure that all records of the result got
generated). The reason is that the VBA function, in the first query, since
it appears only in the SELECT clause, that VBA function evaluation is
differed, it is NOT evaluated UNTIL it is required to be displayed (or to be
'the current record'), while the last query will perform the whole
computation, for all the records of the result. So, the first query, in some
case, is faster, but in some other almost identical situations, it is the
slower solution.
It is not surprising that you can get different contradictory comments:
sometimes, a small subtle difference can make a whole world of difference.
Vanderghast, Access MVP
Hi Michel;
TX so much for your reply...
Granted, the examples I gave are vastly over simplified queries,
intended to communicate the concepts.
In fact, the application they represent is not able to join on the
ID's, because they differ between tables.
My question was partly intended as a "which is truly the fastest
solution" type of question.
However - your second query, (access wouldn't accept for syntactical
reasons), was a new idea to me - I didn't know you could join like
that. I will explore that further....
But on the "big picture" side of my question - all other things being
equal - do you know:
Which would be faster - 2 separate queries, or a single query with a
sub-query?
(clearly, with your example, that query would easily be fastest, but
still curious)
as it relates to a form's performance - controls may reference a table
directly, or a query -
is 1 faster than the other? (considering, time to open form, as well
as time for pulldown to access information)
I've heard conflicting arguments, especially for how to get the best
form performance....
TIA! - Bob
Well, ... eh... in this case ... use one query, without any sub-query.
SELECT *, T_names.[first] & " " & T_names.[last] AS full_name
FROM T_fullnames INNER JOIN T_names
ON T_names.id = T_fullnames.id;
(note that technically, your two solutions were not, in general,
producing
the same result, since one join on id and the other solution join on a
computed expression. So, it can be that the required solution is:
SELECT *, T_names.[first] & " " & T_names.[last] AS full_name
FROM T_fullnames INNER JOIN T_names
ON T_names.[first] & " " & T_names.[last]= T_fullnames.fullName;
if the join has to be on the fullName, rather than on the id.
In general, when you ask yourself which solution is faster... you can
simply
test the alternatives and see if there is any human-perceptible
difference
at all.
Vanderghast, Access MVP
On Feb 15, 12:29 pm, (e-mail address removed) wrote:
using A2k;
seen LOTS of posts about this topic, but none seem to address the
heart of my question.
Let's break this into 2 parts: queries (stand-alone); and form data
sources (and control data sources)
queries - stand alone:
Let's say I have a query (#1) that relates information in 3 tables.
Now I want another query (#2) that uses that same information, and
relates it to more tables.
Would query #2 be faster if it uses query #1, and relates that to more
tables;
OR would it be faster if it had all the table relations in query #1
built into it (not use a query inside a query) ?
---
form (and control) data sources:
Is a form going to be faster if it references a table directly, OR if
it uses a query (select * from table) that does the same thing?
What about a control on a form? faster if it uses a query or a table?
I've been creating queries for my control data sources, and it appears
to allow forms to open faster... just want to know the truth....
As a followup question: what about sub-queries? Which of these 2
examples would be faster?
Let's say i've got 2 tables:
names (which contains field for first and field for last names)
fullnames (which contains a field with first and last name
concatenated with a space)
If I want to relate these tables, I could:
A) use 2 queries:
Q1 = SELECT T_names.*, [first] & " " & [last] AS full_name FROM
T_names;
Q2 = SELECT Q1.*, T_fullnames.* FROM Q1 INNER JOIN T_fullnames ON
Q1.full_name = T_fullnames.full_name;
OR
B) use 1 query with a sub-query:
Q1 = SELECT Q_names_plus.*, T_fullnames.* FROM
(SELECT T_names.*, [first] & " " & [last] AS full_name FROM
T_names) AS Q_names_plus
INNER JOIN T_fullnames ON Q_names_plus.id = T_fullnames.id;
I'm guessing that sub-queries would be slower, but they sure are A LOT
cleaner... would I be right?
mainly if you have a WHERE clause, which you