Explain why this works better?

[Fred Boer]

Hello!
I use the following expression as the control source for a control: #cass:
nz([NumberofCassettes],"N/A") .
I have always placed this expression directly in the control source
property, and there was always a slight delay as it resolved. However, this
morning I tried creating an expression using this expression (is my
terminology correct here?!) in the underlying query of the form. I was
surprised to see that the delay vanished when I scrolled through records on
the form.

Could someone explain why there is no delay if the expression is part of the
query, and there *is* a delay if it is used as the control source? Is it
because the query resolves this for all records at once?

Thanks!
Fred

 

[Fred Boer]

Oh, and another related question:

Can I somehow work the same magic with the following expression in the
query?

=IIf(DCount("*","Qry_Transaction","[Book_ID]=" &
[Form].[Book_ID])=0,"Yes","No")

I've tried this and Access returns a prompt for a BookID parameter... but I
wonder if there is a way to make it work...

Thanks!
Fred Boer

 

[Rick B]

I believe that your assumption is correct. The query has done all the work
before the form is ever displayed.

Rick B


Hello!
I use the following expression as the control source for a control: #cass:
nz([NumberofCassettes],"N/A") .
I have always placed this expression directly in the control source
property, and there was always a slight delay as it resolved. However, this
morning I tried creating an expression using this expression (is my
terminology correct here?!) in the underlying query of the form. I was
surprised to see that the delay vanished when I scrolled through records on
the form.

Could someone explain why there is no delay if the expression is part of the
query, and there *is* a delay if it is used as the control source? Is it
because the query resolves this for all records at once?

Thanks!
Fred

 

[Michel Walsh]

Hi,

The query does not resolve computation for all records, would be
potentially too long, but, probably, uses a local cache and solve the
computation for all those in the "cache", which has, as end effect, the
described effect (or another similar technique).

The syntax is FORMS!FormName!ControlName

FORMS is a keyword to be type like it is, other are to be replaced with your
real names. [ ] are required only around illegal names (like those having
a space in them, or the minus sign in them, etc.)

You can probably just use, in that particular context:

=iif(0=DCOUNT("*", "Qry_Transaction", "Book_ID=" & Book_ID ), "Yes", "No")




Hoping it may help,
Vanderghast, Access MVP

 

[Dirk Goldgar]

Oh, and another related question:

Can I somehow work the same magic with the following expression in the
query?

=IIf(DCount("*","Qry_Transaction","[Book_ID]=" &
[Form].[Book_ID])=0,"Yes","No")

I've tried this and Access returns a prompt for a BookID parameter...
but I wonder if there is a way to make it work...

Michel Walsh and Rick B have answered your question, but I thought I'd
add that it's possible that this expression could be rewritten, in the
query, to avoid using the DCount expression at all. If that should
prove possible, it would probably improve performance. However, I
believe it would result in the query's becoming nonupdatable, so if the
form is to be used for adding or editing data you can't do it that way.

 

[Wayne Morgan]

Did you notice if it took longer to move between records with the expression
in the query? With it in the form, you would get all of the data to the form
and displayed then the form would calculate. With it in the query, you
wouldn't receive anything from the query until it had finished the
calculation, so all of the data would appear at one time. As Michel
mentioned, the query may also be able to cache some of this data and "work
ahead", so it may be quicker overall also.

 

[Fred Boer]

Dear Wayne:

There appears to be no lag when the expression is in the query. Of course,
this is a very small database, (20,000) records, and so if there is caching
going on, the whole thing might fit in the cache... It is *much* better than
it was; before, if I scrolled through the records, the control would be
blank until I stopped on a record for more than a second...


Cheers!
Fred

Did you notice if it took longer to move between records with the expression
in the query? With it in the form, you would get all of the data to the form
and displayed then the form would calculate. With it in the query, you
wouldn't receive anything from the query until it had finished the
calculation, so all of the data would appear at one time. As Michel
mentioned, the query may also be able to cache some of this data and "work
ahead", so it may be quicker overall also.

--
Wayne Morgan
Microsoft Access MVP

Hello!
I use the following expression as the control source for a control: #cass:
nz([NumberofCassettes],"N/A") .
I have always placed this expression directly in the control source
property, and there was always a slight delay as it resolved. However, this
morning I tried creating an expression using this expression (is my
terminology correct here?!) in the underlying query of the form. I was
surprised to see that the delay vanished when I scrolled through records on
the form.

Could someone explain why there is no delay if the expression is part of the
query, and there *is* a delay if it is used as the control source? Is it
because the query resolves this for all records at once?

Thanks!
Fred

 

[Fred Boer]

Thanks!

Fred

P.S. [OT] I've been curious for some time... Your posts use two names:
"Michel Walsh" and "Vanderghast". It's none of my business, and please feel
free to ignore me if you wish, but I see you listed by Microsoft as "Michel
Walsh", so, who/what is "Vanderghast"?

Hi,

The query does not resolve computation for all records, would be
potentially too long, but, probably, uses a local cache and solve the
computation for all those in the "cache", which has, as end effect, the
described effect (or another similar technique).

The syntax is FORMS!FormName!ControlName

FORMS is a keyword to be type like it is, other are to be replaced with your
real names. [ ] are required only around illegal names (like those having
a space in them, or the minus sign in them, etc.)

You can probably just use, in that particular context:

=iif(0=DCOUNT("*", "Qry_Transaction", "Book_ID=" & Book_ID ), "Yes", "No")




Hoping it may help,
Vanderghast, Access MVP

Oh, and another related question:

Can I somehow work the same magic with the following expression in the
query?

=IIf(DCount("*","Qry_Transaction","[Book_ID]=" &
[Form].[Book_ID])=0,"Yes","No")

I've tried this and Access returns a prompt for a BookID parameter...

but

I
wonder if there is a way to make it work...

Thanks!
Fred Boer

 

[Fred Boer]

Dear Dirk:

I spend a fair bit of time hacking away at this earlier today, without
success. The query is already nonupdatable, so that isn't an issue. I tried
a few variations, with no success. I also tried creating a series of
queries, which I thought might work. To this point, I'm still stumped. I'm
going to try to take another crack at it tonight, if I get a chance.
However, if I don't make any progress, would you be willing to check back
here? I'll post back to this thread with a more detailed and complete
description of the situation....

Thanks!
Fred

 

[Dirk Goldgar]

Dear Dirk:

I spend a fair bit of time hacking away at this earlier today, without
success. The query is already nonupdatable, so that isn't an issue. I
tried a few variations, with no success. I also tried creating a
series of queries, which I thought might work. To this point, I'm
still stumped. I'm going to try to take another crack at it tonight,
if I get a chance. However, if I don't make any progress, would you
be willing to check back here? I'll post back to this thread with a
more detailed and complete description of the situation....

Sure, no problem. I'd expect a query along these lines to work:

SELECT
Book_ID, AnotherField, YetAnotherField,
If(EXISTS(SELECT TransactionID FROM Qry_Transaction
WHERE Qry_Transaction.Book_ID=MyTable.Book_ID),
"No",
"Yes")
As NoTrans
FROM MyTable

It may be that you can replace the subquery "SELECT ... FROM
Qry_Transaction ..." with a modified form of the SQL of Qry_Transaction
itself.

 

[Fred Boer]

Whoo hoo! I think I've got it working! I have two queries: Qry_All, which is
the mother of all queries in my app, and Qry_Transaction, which shows books
which are on loan. Basically, I realized that I was being boneheaded
(really!) <g>. All I had to do was add Qry_Transaction to Qry_All, and do an
IIF on TimeOut. If it was null, then book wasn't on loan, if not null, it
was in circulation. This seems to work, and the form shows no lag for
calculation in the control.

Dirk, this may be what your suggested query would have done.. I haven't
looked at it closely. (I really wanted to try and solve it myself if I
could...)

Also, I can try, tomorrow, to simply incorporate the sql from
Qry_Transaction into Qry_All, as you suggested might be possible..

Of course, I need to do some testing tomorrow, but, yippee! I think I got
it!

Thanks!
Fred

Tables:

Tbl_Library: Book_ID, Title, Dewey, Copy#, MediaFormat_ID, etc.
Tbl_Author: Author_ID, AuthorLastName, etc.
Tbl_BookAuthor: Book_ID, Author_ID
Tbl_Subject: Subject_ID, SubjectHeading, etc.
Tbl_BookSubject: BookID, Subject_ID
Tbl_Series: Series_ID, Series
Tbl_MediaFormat: MediaFormat_ID, Format
etc...

Tbl_Transaction: Book_ID, TimeOut, TimeIn, Patron_ID
Tbl_Patrons: Patron_ID, Lastname, etc.



SELECT Tbl_Library.*, Tbl_Author.AuthorFirstName,
Tbl_Author.AuthorMiddleName, Tbl_Author.AuthorLastName,
Tbl_Location.Location, Tbl_Status.Status, Tbl_PubPlace.PubPlace,
Tbl_Binding.Binding, Tbl_MediaFormat.MediaFormat, Tbl_Publisher.Publisher,
Tbl_Series.Series, [Tbl_Author].[AuthorLastName] & (",
"+[Tbl_Author].[AuthorFirstName]) & " " & [Tbl_Author].[AuthorMiddleName] AS
Author, Tbl_Subject.Subject, nz([NumberofCassettes],"N/A") AS [#cass],
Qry_Transaction.TimeIn, IIf(IsNull([TimeOut]),"YES","No") AS Available
FROM Tbl_Subject RIGHT JOIN (Tbl_Status INNER JOIN (Tbl_PubPlace INNER JOIN
(Tbl_Publisher INNER JOIN (Tbl_MediaFormat INNER JOIN (Tbl_Location INNER
JOIN ((((Tbl_Binding INNER JOIN (Tbl_Library LEFT JOIN Qry_Transaction ON
Tbl_Library.Book_ID = Qry_Transaction.Book_ID) ON Tbl_Binding.Binding_ID =
Tbl_Library.Binding_ID) LEFT JOIN (Tbl_Author RIGHT JOIN Tbl_BookAuthor ON
Tbl_Author.Author_ID = Tbl_BookAuthor.Author_ID) ON Tbl_Library.Book_ID =
Tbl_BookAuthor.Book_ID) LEFT JOIN (Tbl_BookSeries LEFT JOIN Tbl_Series ON
Tbl_BookSeries.Series_ID = Tbl_Series.Series_ID) ON Tbl_Library.Book_ID =
Tbl_BookSeries.Book_ID) LEFT JOIN Tbl_BookSubject ON Tbl_Library.Book_ID =
Tbl_BookSubject.Book_ID) ON Tbl_Location.Location_ID =
Tbl_Library.Location_ID) ON Tbl_MediaFormat.MediaFormat_ID =
Tbl_Library.MediaFormat_ID) ON Tbl_Publisher.Publisher_ID =
Tbl_Library.Publisher_ID) ON Tbl_PubPlace.PubPlace_ID =
Tbl_Library.PubPlace_ID) ON Tbl_Status.Status_ID = Tbl_Library.Status_ID) ON
Tbl_Subject.Subject_ID = Tbl_BookSubject.Subject_ID
ORDER BY Tbl_Library.Book_ID;


SELECT Tbl_Transaction.*, Tbl_Library.Title, [Tbl_Patrons.LastName] & ", " &
[Tbl_Patrons.FirstName] AS Fullname
FROM Tbl_Patrons INNER JOIN (Tbl_Library INNER JOIN Tbl_Transaction ON
Tbl_Library.Book_ID = Tbl_Transaction.Book_ID) ON Tbl_Patrons.Patron_ID =
Tbl_Transaction.Patron_ID
WHERE (((Tbl_Transaction.TimeIn) Is Null))
ORDER BY Tbl_Transaction.Book_ID;

 

[Michel Walsh]

Hi,


That is my email address, at msn dot com, among other things...



Hoping it may help,
Vanderghast, Access MVP