I have tables to contain the names of the birds of the world.
tGN - Genus scientific (Latin) names -
Key (PK) autonum
-
Ptr->FA (FK) pointer to key in table=FA Not used in this problem
-
Latin (text) Genus scientific name
tSP - Species names -
Key (PK) autonum
-
Ptr->GN (FK) pointer to key in table=GN
-
Latin (text) Species scientific name
-
Common (text) Species English (or common) name
-
[Field 5] ... Not used in this problem
-
[Field 6] ... Not used in this problem
-
...
-
[Field n] ... Not used in this problem
qGN_SP - Saved query -
GNLatin text
-
SPLatin text
-
GN_SPLatin text containing GNLatin & " " & SPLatin
-
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: -
If DCount("*", "qGN_SP", "GNLatin=""alba""") <> 0 _
-
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. - 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?
16 3333
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: - Dim rst As DAO.Recordset
-
Dim strSQL As String
-
Dim qdfTemp As DAO.QueryDef
-
-
strSQL = "Select * From Orders Where [Order ID] > 1;"
-
-
'Create the Temporary QueryDef Object
-
Set qdfTemp = CurrentDb.CreateQueryDef("", strSQL)
-
-
'Create a Recordset based on the Temporary QueryDef
-
Set rst = qdfTemp.OpenRecordset
-
-
'Process Recordset here
-
'...
-
-
Set qdfTemp = Nothing
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? - DCount("*", "qGN_SP", "SPLatin=""alba""") Then ...
would be changed to what?
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).
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.
@OldBirdman - I know that this is probably a stupid question, OldBirdman, but are the Fields being searched Indexed?
- 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.
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?
@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)!
@OldBirdman
Just a thought.
Would it run faster if the whole criteria is passed (if feasible at all) to a single DCount()?
like -
DCount("*", "qGN_SP", "GNLatin=""alba"" Or SPLatin=""alba""")
-
instead of the above ... and so on
@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. - If DCount("*", "qGN_SP", "GNLatin=""alba""") <> 0 Then
-
'Entry exists, no need for the 2nd DCount()
-
Else 'Entry doesn't exist in 1st DCount, does it exist in the 2nd?
-
If DCount("*", "qGN_SP", "SPLatin=""alba""") <> 0 Then
-
'Entry exists in 2nd DCount()
-
Else
-
'Entry does not exist in either Case
-
End If
-
End If
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.
@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. - Actual Input Name: Nutmeg Mannikin
-
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.
I made a mistake generating the sample table. It should be: - 43 Scaly-breasted Munia Nutmeg Mannikin (Lonchura punctulata)
-
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.
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: -
SELECT tblSearchWords.txtSearchWord, Count(...) FROM tbl INNER JOIN tblSearchWords ON tbl.txtSearchField Like '*' & tblSearchWords.txtSearchWord & '*' GROUP BY tblSearchWords.txtSearchWord;
-
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.
Well. A simple example is in the attachment.
@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). - Required Declaration:
- Public Declare Function timeGetTime Lib "winmm.dll" () As Long
- Function Definition:
- Public Function ProcessStringInput(strString As String)
-
Dim varParseString As Variant
-
Dim intCounter As Integer
-
Dim lngStartTime As Long
-
Dim lngEndTime As Long
-
Dim varTmpStr As Variant
-
-
If strString = "" Then Exit Function
-
-
lngStartTime = timeGetTime()
-
-
varParseString = Split(strString, " ")
-
-
For intCounter = LBound(varParseString) To UBound(varParseString)
-
'Must at least remove (, ), [, and [ prior to comparison
-
varTmpStr = Trim(Replace(Replace(Replace(Replace(varParseString(intCounter), "(", ""), ")", ""), "[", ""), "]", ""))
-
If DCount("*", "qGN_SP", "[tGN LatinGenus] = '" & varTmpStr & "'") > 0 Then
-
Debug.Print DCount("*", "qGN_SP", "[tGN LatinGenus] = '" & varTmpStr & "'") & _
-
" count(s) of " & varTmpStr & " found in [tGN LatinGenus]"
-
Else
-
Debug.Print varTmpStr & " not found in [tGN LatinGenus]"
-
End If
-
If DCount("*", "qGN_SP", "[tSP LatinSpecies] = '" & varTmpStr & "'") > 0 Then
-
Debug.Print DCount("*", "qGN_SP", "[tSP LatinSpecies] = '" & varTmpStr & "'") & _
-
" count(s) of " & varTmpStr & " found in [tSP LatinSpecies]"
-
Else
-
Debug.Print varTmpStr & " not found in [tSP LatinSpecies]"
-
End If
-
Debug.Print
-
Next
-
-
lngEndTime = timeGetTime()
-
-
Debug.Print
-
Debug.Print "*************************************************"
-
Debug.Print FormatNumber(DCount("*", "qGN_SP"), 0) & " Records processed in: " & _
-
(lngEndTime - lngStartTime) / 1000 & " sec(s)"
-
Debug.Print "*************************************************"
-
End Function
- Test Function Call:
- Call ProcessStringInput("Green Honeycreeper m Chlorophanes spiza arguta (Malurus cyaneus) Cha-Cha-Cha")
- OUTPUT From Function Call:
- Green not found in [tGN LatinGenus]
-
Green not found in [tSP LatinSpecies]
-
-
Honeycreeper not found in [tGN LatinGenus]
-
Honeycreeper not found in [tSP LatinSpecies]
-
-
m not found in [tGN LatinGenus]
-
m not found in [tSP LatinSpecies]
-
-
1 count(s) of Chlorophanes found in [tGN LatinGenus]
-
Chlorophanes not found in [tSP LatinSpecies]
-
-
1 count(s) of spiza found in [tGN LatinGenus]
-
1 count(s) of spiza found in [tSP LatinSpecies]
-
-
arguta not found in [tGN LatinGenus]
-
arguta not found in [tSP LatinSpecies]
-
-
13 count(s) of Malurus found in [tGN LatinGenus]
-
Malurus not found in [tSP LatinSpecies]
-
-
cyaneus not found in [tGN LatinGenus]
-
3 count(s) of cyaneus found in [tSP LatinSpecies]
-
-
Cha-Cha-Cha not found in [tGN LatinGenus]
-
Cha-Cha-Cha not found in [tSP LatinSpecies]
-
-
-
*************************************************
-
10,416 Records processed in: 0.068 sec(s)
-
*************************************************
- P.S. - Don't feel funny telling me that it was a total waste of time.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mattyboy |
last post by:
Guys
I have built a database with saved queries that runs fine in Access
but when I call it from the web using ASP, an exception occurs. I
have tried multiple ways of testing the databases with...
|
by: Richard Hollenbeck |
last post by:
The following query takes about one second to execute with less than 1,000
records, but the report that's based on it takes from 15-30 seconds to
format and display. That's frustrating for both me...
|
by: Zlatko Matić |
last post by:
Hello.
I'm wondernig what is happennig whith saved pass-through queries nested in
regular JET query if regular JET query just filtrates result by start/end
date...Does pass-through query first...
|
by: MLH |
last post by:
Suppose I have this simple SQL string...
SELECT tblDrivers.DriverID, tblDrivers.DName
FROM tblDrivers
WHERE (((tblDrivers.DName) Like "N*"))
ORDER BY tblDrivers.DriverID;
And suppose that...
|
by: tomlebold |
last post by:
The function or code below creates a saved query used as the record
source for forms and reports. The funtions is called each time a user
clicks on a button to display a form or report.
This works...
|
by: Robert |
last post by:
How do you get an accurate count of the number of records returned from
a query when using linked tables.
I have an access 2003 database as a front end to another access 2003
database that...
|
by: Ron |
last post by:
Hi All,
Using Access2000, winXP.
Table 1 = tblClients displayed on frmClients via qryClients. 2nd table =
tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails
shown on...
|
by: AdmiralXizor |
last post by:
I have written a function that executes action parameter queries given a name and a list of parameters.
It splits a string into an array, passes the parameters to the (already saved) query name,...
|
by: joeyrhyulz |
last post by:
Hi,
I'm trying to make a very simple update statement (in Oracle) in jet
sql that seems much more difficult than it should be.
The root of my problem is that I'm trying to update a field on a...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |