By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,286 Members | 1,711 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,286 IT Pros & Developers. It's quick & easy.

When is a Saved Query run?

100+
P: 675
I have tables to contain the names of the birds of the world.
tGN - Genus scientific (Latin) names
Expand|Select|Wrap|Line Numbers
  1. Key       (PK) autonum
  2. Ptr->FA   (FK) pointer to key in table=FA   Not used in this problem
  3. Latin     (text) Genus scientific name
tSP - Species names
Expand|Select|Wrap|Line Numbers
  1. Key       (PK) autonum
  2. Ptr->GN   (FK) pointer to key in table=GN
  3. Latin     (text) Species scientific name
  4. Common    (text) Species English (or common) name 
  5. [Field 5]  ... Not used in this problem
  6. [Field 6]  ... Not used in this problem
  7. ...
  8. [Field n]   ... Not used in this problem
qGN_SP - Saved query
Expand|Select|Wrap|Line Numbers
  1. GNLatin      text
  2. SPLatin      text
  3. GN_SPLatin   text containing GNLatin & " " & SPLatin
  4. Common       text
When I reference qGN_SP, for example in a DLookup or DCount function, does Access run the query qGN_SP each time? If so, how do I create a semi-permanent query which would only change if either table (tGN or tSP) changed?

I am attempting to match picture captions to these tables, and the caption has no specific format. To parse, I need to first determine which words are Latin. I am looking for the best way to determine:
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "qGN_SP", "GNLatin=""alba""") <> 0 _
  2.     Or DCount("*", "qGN_SP", "SPLatin=""alba""") Then ...
As I search for valid or invalid words, Latin or English, I will be calling DCount multiple times. Normally, when processing keyboard input I don't worry about speed or efficiency, but I am getting considerable time lag. I sometimes wait several seconds if I am testing 7 or more words.
I tried making a second saved query, qShortGN_SP. I did a UNION of each Latin field (GNLatin and SPLatin) using DISTINCT to get a shorter list.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Latin FROM tGN UNION SELECT DISTINCT Latin FROM tSP
This seemed to increase the time.
Any suggestions for making this project run more quickly?
Jun 19 '09 #1
Share this Question
Share on Google+
16 Replies


ADezii
Expert 5K+
P: 8,679
You could create a Temporary QueryDef Object. Temporary QueryDef Objects are useful if you want to repeatedly use Dynamic SQL statements without having to create any new permanent objects in the QueryDefs collection. I'll post some sample code below:
Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2. Dim strSQL As String
  3. Dim qdfTemp As DAO.QueryDef
  4.  
  5. strSQL = "Select * From Orders Where [Order ID] > 1;"
  6.  
  7. 'Create the Temporary QueryDef Object
  8. Set qdfTemp = CurrentDb.CreateQueryDef("", strSQL)
  9.  
  10. 'Create a Recordset based on the Temporary QueryDef
  11. Set rst = qdfTemp.OpenRecordset
  12.  
  13. 'Process Recordset here
  14. '...
  15.  
  16. Set qdfTemp = Nothing
Jun 20 '09 #2

100+
P: 675
Thanks for the reply. But I'm more unsure now than I was.

1) I'm trying to decrease time (increase execution speed). How does this help?

2) It appears as if I would have to run the query each time I wanted to do a DLookup or DCount. This is what I am trying to avoid. If this were in the Form.Load event, and qdfTemp were global, would the query persist through the entire execution? Would it update if either of my tables changed?

3) Why statement #16? If qdfTemp is a local variable, it goes away when the procedure ends, and this would assure that the query must be run for each DCount. If it is global, it would go away when the project closed.

4) How is this temporary object refered to in DCount?
Expand|Select|Wrap|Line Numbers
  1. DCount("*", "qGN_SP", "SPLatin=""alba""") Then ...
would be changed to what?
Jun 20 '09 #3

ADezii
Expert 5K+
P: 8,679
I was thinking more along the lines of creating a Temporary QueryDef Object based on the Criteria defined in the DCount()/DLookup() Functions, then retrieving a Recordset Count on the Result Set as sort of a Pseudo Domain Function. I'm probably way off base on this one, so forget everything I just said (LOL).
Jun 20 '09 #4

100+
P: 675
Thank you for your responses though. I try to understand what is being said, even when it may appear off-base at first. Often there is an answer there, or a learning opportunity.

My problem here is that the time is getting way too long for my impatience tolerance. I paste an entire name into a text/search box because to type each letter and see if it can fill with a complete, unique name takes 5-7 seconds. As bird names can be non-unique for at least the first 20 characters, I'm talking 1.5 to 2 minutes. My suspicion is that Access is doing things it doesn't have to.
Jun 20 '09 #5

ADezii
Expert 5K+
P: 8,679
@OldBirdman
  1. I know that this is probably a stupid question, OldBirdman, but are the Fields being searched Indexed?
  2. Any possibility of E-Mailing me a Copy/Subset of the Database for me to take a gander at? You have peaked my interest in this matter.
Jun 20 '09 #6

100+
P: 675
I'm just out the door 'til Monday. I'll see if I can pick this project apart to send a subset. I condensed it considerably just to present it in the forum.

Yes, the fields are indexed in the tables. Does the index carry forward through queries?
Jun 21 '09 #7

ADezii
Expert 5K+
P: 8,679
@OldBirdman
Does the index carry forward through queries?
Yes, and it significantly reduces the Execution Time of Queries if the Fields used in Criteria are Indexed. See you next week, OldBirdman. We may be able to adapt some Incremental Search Logic for your particular situation. A Subset of the data would be nice, actually, the more the merrier (LOL)!
Jun 21 '09 #8

FishVal
Expert 2.5K+
P: 2,653
@OldBirdman
Just a thought.

Would it run faster if the whole criteria is passed (if feasible at all) to a single DCount()?

like

Expand|Select|Wrap|Line Numbers
  1. DCount("*", "qGN_SP", "GNLatin=""alba"" Or SPLatin=""alba""")
  2.  
instead of the above ... and so on
Jun 21 '09 #9

ADezii
Expert 5K+
P: 8,679
@FishVal
Hello FishVal, unless I am reading this thing incorrectly, wouldn't the more efficient approach be the following? It may eliminate the need for Multiple Criteria and DCount() Functions.
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "qGN_SP", "GNLatin=""alba""") <> 0 Then
  2.   'Entry exists, no need for the 2nd DCount()
  3. Else    'Entry doesn't exist in 1st DCount, does it exist in the 2nd?
  4.   If DCount("*", "qGN_SP", "SPLatin=""alba""") <> 0 Then
  5.     'Entry exists in 2nd DCount()
  6.   Else
  7.     'Entry does not exist in either Case
  8.   End If
  9. End If
Jun 21 '09 #10

100+
P: 675
Attached is a DB with 2 tables, tGN and tSP, from my species heirarchy. There is one query, the one I test against. My original question was whether this query is run prior to each DCount or DLookup function or whether Access only runs this query if either of the underlying tables changes.

Wanted: A function with a string argument that returns a string containing the common and scientific names of a bird species. The table tSampleInOut contains a field [Actual Input Name] with examples of the argument that would be passed to this function. Field [Desired Output] shows the string that should be returned. Most of these were from actual email received in April09.

Notes:
1) In my actual project, these tables are linked, so changes could occur from other projects at any time
2) The tables contain non-bird species, which can be removed by including 3 more tables (tCL {class}, tOR {order}, tFA {family}) in the query qGN_SP. Currently there are only about 300 records not birds, but this could change as I take up Butterfly watching and add ALL N.American butterflies to the tables (800 rows). To remove would require tPHY {Phylum} be added to the query.
3)Tables are not completely normalized. I have put 2 common names into the [tSP Name] field occasionally, and also created new species records with sub-species. I'm not sure how to avoid some of this, but I can't start until I get some other issues out of the way first
4) The function will not work all the time, and must return a "NOT FOUND" string sometimes. When this happens, I need another function that has as arguments the "Search Name" and ""Search Name Type" where name type is 'Scientific Name', 'Genus', 'Species', 'Common Name', 'Common LastPart', etc. This function will fill a combobox with 0 to n rows containing all matches to the partial name. I would then manually (mentally?) select the correct name or try again. At least, this is how I currently see the solution.
5) Some names will never be resolved correctly. A photo labeled as taken in India with a non-migratory N.American bird name would be an error that the functions cannot detect. I will always have to look at each answer.
Attached Files
File Type: zip NameSearch.zip (513.7 KB, 81 views)
Jun 24 '09 #11

ADezii
Expert 5K+
P: 8,679
@OldBirdman
Hello OldBirdman, I'm a little dense these days, and just cannot seem to figure out how some of the Output is derived in tSampleInOut from qGN_SP. Hope that you can give me a simple Demo as to the logic.
Expand|Select|Wrap|Line Numbers
  1. Actual Input Name: Nutmeg Mannikin
  2. Desired Output: Scaly-breasted Munia (Lonchura punctulata)
What Steps did you take to arrive at Scaly-breasted Munia (Lonchura punctulata). Thanks, I know I'm must be missing something quite simple.
Jun 24 '09 #12

100+
P: 675
I made a mistake generating the sample table. It should be:
Expand|Select|Wrap|Line Numbers
  1. 43 Scaly-breasted Munia  Nutmeg Mannikin (Lonchura punctulata)
  2. 44 Spicy Finch           Nutmeg Mannikin (Lonchura punctulata)
The point here is that I will be doing several DCount() function calls on query qGN_SP, which needs 2 tables to generate (or 5 or 6 if more efficient to eliminate all but birds).

qGN_SP need only be rerun if I change either tGN or tSP while also working with pictures. This may happen when a photographer writes that there is a change to an officially recognized list. In which case, a table row(s) may change and should be done and qGN_SP rerun before the picture is named.

I have considered using the Split() function to parse the input into words, and calling DCount to determine if each word is Latin or not. I could then know which words might be the Scientific Name, and which the Common Name. More DCount for a match, or failing that, a short list of possibilities.

When is a Saved Query run? Or, How to I control when qGN_SP is run? I've tried to determine differences in time by executing this in a loop of 10,000, but the results are inconclusive. Virus checking, updating, and other stuff going on in background probably make such test meaningless.

What Steps did you take to arrive at Scaly-breasted Munia (Lonchura punctulata). Thanks, I know I'm must be missing something quite simple.
This example was included to show that no function can succeed 100% of the time, given the nature of the problem. I got the answer using sources on the Web. But this is slow also, and I try to avoid using this method.
Jun 25 '09 #13

FishVal
Expert 2.5K+
P: 2,653
Hello. OldBirdman.

What about using SQL to get keywords' match count.

Let us say, you have [tblSearchWords] where each record is word to search in your [tbl].[txtSearchField].

A simple query will return count of matches:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblSearchWords.txtSearchWord, Count(...) FROM tbl INNER JOIN tblSearchWords ON tbl.txtSearchField Like '*' & tblSearchWords.txtSearchWord & '*' GROUP BY tblSearchWords.txtSearchWord;
  2.  
Jun 25 '09 #14

100+
P: 675
Well, that goes right over my head.

I can write and understand code, and with luck it works. I use the Query Design Grid or an occasional Wizard to get me started with any SQL needed. I don't understand your SELECT statement.

I don't understand what tblSearchWords is. What goes in Count(...)? What is tbl.txtSearchField? Which relate to my tables? I can't even paste this into my database because I can't edit it to my names because I don't understand.
Jun 25 '09 #15

FishVal
Expert 2.5K+
P: 2,653
Well. A simple example is in the attachment.
Attached Files
File Type: zip CountMatches.zip (117.6 KB, 72 views)
Jun 25 '09 #16

ADezii
Expert 5K+
P: 8,679
@OldBirdman
Hello OldBirdman, I wrote a little Algorithm that hopefully will help you in finding Matches, or at least point you in the right direction. If it is of no use, please feel free to toss it in the Trash Can! (LOL).
  1. Required Declaration:
    Expand|Select|Wrap|Line Numbers
    1. Public Declare Function timeGetTime Lib "winmm.dll" () As Long
  2. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function ProcessStringInput(strString As String)
    2. Dim varParseString As Variant
    3. Dim intCounter As Integer
    4. Dim lngStartTime As Long
    5. Dim lngEndTime As Long
    6. Dim varTmpStr As Variant
    7.  
    8. If strString = "" Then Exit Function
    9.  
    10. lngStartTime = timeGetTime()
    11.  
    12. varParseString = Split(strString, " ")
    13.  
    14. For intCounter = LBound(varParseString) To UBound(varParseString)
    15.   'Must at least remove (, ), [, and [ prior to comparison
    16.   varTmpStr = Trim(Replace(Replace(Replace(Replace(varParseString(intCounter), "(", ""), ")", ""), "[", ""), "]", ""))
    17.     If DCount("*", "qGN_SP", "[tGN LatinGenus] = '" & varTmpStr & "'") > 0 Then
    18.       Debug.Print DCount("*", "qGN_SP", "[tGN LatinGenus] = '" & varTmpStr & "'") & _
    19.              " count(s) of " & varTmpStr & " found in [tGN LatinGenus]"
    20.     Else
    21.       Debug.Print varTmpStr & " not found in [tGN LatinGenus]"
    22.     End If
    23.     If DCount("*", "qGN_SP", "[tSP LatinSpecies] = '" & varTmpStr & "'") > 0 Then
    24.       Debug.Print DCount("*", "qGN_SP", "[tSP LatinSpecies] = '" & varTmpStr & "'") & _
    25.                   " count(s) of " & varTmpStr & " found in [tSP LatinSpecies]"
    26.     Else
    27.       Debug.Print varTmpStr & " not found in [tSP LatinSpecies]"
    28.     End If
    29.       Debug.Print
    30.   Next
    31.  
    32. lngEndTime = timeGetTime()
    33.  
    34. Debug.Print
    35. Debug.Print "*************************************************"
    36. Debug.Print FormatNumber(DCount("*", "qGN_SP"), 0) & " Records processed in: " & _
    37.             (lngEndTime - lngStartTime) / 1000 & " sec(s)"
    38. Debug.Print "*************************************************"
    39. End Function
  3. Test Function Call:
    Expand|Select|Wrap|Line Numbers
    1. Call ProcessStringInput("Green Honeycreeper m Chlorophanes spiza arguta (Malurus cyaneus) Cha-Cha-Cha")
  4. OUTPUT From Function Call:
    Expand|Select|Wrap|Line Numbers
    1. Green not found in [tGN LatinGenus]
    2. Green not found in [tSP LatinSpecies]
    3.  
    4. Honeycreeper not found in [tGN LatinGenus]
    5. Honeycreeper not found in [tSP LatinSpecies]
    6.  
    7. m not found in [tGN LatinGenus]
    8. m not found in [tSP LatinSpecies]
    9.  
    10. 1 count(s) of Chlorophanes found in [tGN LatinGenus]
    11. Chlorophanes not found in [tSP LatinSpecies]
    12.  
    13. 1 count(s) of spiza found in [tGN LatinGenus]
    14. 1 count(s) of spiza found in [tSP LatinSpecies]
    15.  
    16. arguta not found in [tGN LatinGenus]
    17. arguta not found in [tSP LatinSpecies]
    18.  
    19. 13 count(s) of Malurus found in [tGN LatinGenus]
    20. Malurus not found in [tSP LatinSpecies]
    21.  
    22. cyaneus not found in [tGN LatinGenus]
    23. 3 count(s) of cyaneus found in [tSP LatinSpecies]
    24.  
    25. Cha-Cha-Cha not found in [tGN LatinGenus]
    26. Cha-Cha-Cha not found in [tSP LatinSpecies]
    27.  
    28.  
    29. *************************************************
    30. 10,416 Records processed in: 0.068 sec(s)
    31. *************************************************
  5. P.S. - Don't feel funny telling me that it was a total waste of time.
Jun 25 '09 #17

Post your reply

Sign in to post your reply or Sign up for a free account.