SQL string v stored query (1 Viewer)

[John Sh]

I have created a series of sql strings from stored queries.
The object being to reduce the number of queries doing essentially the same thing with a different table.
The sql strings utilise " " & sTable & " as A " construct.
In the code below,the functions getMFamily, getMGenus and getMColl work as expected.
The functions getMSpecies and getMLocal do not return a result.

If I replace the function calls in "setsoures" with the stored queries everything works.

The functions are in a module and "setsources" is in a form.
Where have I gone wrong?

Option Compare Database
Option Explicit

Private Sub setSources()
    Dim str  As String
    Me.RecordSource = sTable
    Me.cboFamily.RowSource = getMFamily(sTable)
    Me.cboGenus.RowSource = getMGenus(sTable)
    Me.cboEpithet.RowSource = getMSpecies(sTable)
    Me.cboCollector.RowSource = getMColl(sTable)
    Me.cboLocality.RowSource = getMLocal(sTable)
End Sub

Public Function getMFamily(sTable As String) As String
    Dim sQry As String
    sQry = "SELECT A.Family " & _
            "FROM " & sTable & " as A " & _
            "GROUP BY A.Family " & _
            "HAVING ((Not (A.Family) Is Null)) " & _
            "ORDER BY A.Family;"
    getMFamily = sQry
End Function

Public Function getMGenus(sTable As String) As String
    Dim sQry As String
    sQry = "SELECT A.Genus, A.Family " & _
            "FROM " & sTable & " as A " & _
            "GROUP BY A.Genus, A.Family " & _
            "HAVING (((A.Family) ALike [Forms]![Multi-search]![cboFamily] & ""%"")) " & _
            "ORDER BY A.Genus;"
    getMGenus = sQry
End Function

Public Function getMSpecies(sTable As String) As String
    Dim sQry As String
    sQry = "SELECT Herbarium_Collection.SpeciesEpithet " & _
            "FROM " & sTable & " as A " & _
            "GROUP BY A.SpeciesEpithet, A.Genus " & _
            "HAVING (((A.SpeciesEpithet)>"") AND ((A.Genus) ALike [forms]![Multi-search]![cboGenus] & ""%"")) " & _
            "ORDER BY A.SpeciesEpithet, A.Genus;"
    getMSpecies = sQry
End Function

Public Function getMColl(sTable As String) As String
    Dim sQry As String
    sQry = "SELECT A.Collector " & _
            "FROM " & sTable & " as A " & _
            "GROUP BY A.Collector, A.Genus, A.SpeciesEpithet " & _
            "HAVING (((A.Genus) ALike [forms]![Multi-search]![cboGenus] & ""%"") " & _
            "AND ((A.SpeciesEpithet) ALike [forms]![Multi-search]![cboEpithet] & ""%"")) " & _
            "ORDER BY A.Collector, A.Genus;"
    getMColl = sQry
End Function

Public Function getMLocal(sTable As String) As String
    Dim sQry As String
      sQry = "SELECT A.Locality " & _
             "FROM " & sTable & " as A " & _
             "GROUP BY A.Locality, A.Collector, A.Genus, A.SpeciesEpithet " & _
             "HAVING (((A.Collector) ALike [Forms]![Multi-search]![cboCollector] & ""%"") " & _
             "AND ((A.Genus) ALike Nz([forms]![Multi-search]![cboGenus],"") & ""%"") " & _
             "AND ((A.SpeciesEpithet) ALike Nz([forms]![Multi-search]![ccboEpithet],"") & ""%"")) " & _
             "ORDER BY A.Locality, A.Genus, A.SpeciesEpithet;"
    getMLocal = sQry
End Function

 

[plog]

getMSpecies has a tablename hard coded in the SELECT (Herbarium_Collection). If that's not the same as sTable it's gonna choke.

getMLocal references a combo box that doesn't exist (ccboEpithet).


Those are your SQL issues, but I question the whole methodology:

The object being to reduce the number of queries doing essentially the same thing with a different table.

Why do you have so many similarly structured tables? It sounds like you're data isn't properly structured. Looking at the one table I know of your database --Herbarium_Collection -- it seems like you've incorrectly stored data in the table name. Shouldn't 'Herbarium' be a value in the table and not part of the table name? If you make a field for that you probably eliminate all your tables and can get your data into one, thus bypassing this code.

Lastly and least important because it has no practical effect--your HAVING clauses should be WHERE clauses. HAVING is criteria on the aggregate (COUNT, SUM, AVG, etc.) and WHERE is on individual records. Access is very lenient with this which is why your code works.

 

[John Sh]

Thanks for the reply Plog.
The similar table structures are due to a requirement to keep the data from separate sources in their own tables and the data differs greatly from one source to the next, albeit there are a few common fields.
--Herbarium_Collection -- I don't know where you got the idea there is date stored in the table name or how one could do that.
The table is "Herbarium_Collection" with some 90 fields.
"Having" v "Where". The sql that I used is a direct translation, by Access, from the stored query to the sql equivalent so I went with that. I'm happy to be corrected and will make the changes.
It was attributed to Watson, of Sherlock Holmes fame, "that the obvious is the hardest to see", this precludes finding errors in your own text.
Thanks again.
John

 

[John Sh]

Lastly and least important because it has no practical effect--your HAVING clauses should be WHERE clauses. HAVING is criteria on the aggregate (COUNT, SUM, AVG, etc.) and WHERE is on individual records. Access is very lenient with this which is why your code works.

Point of interest here.
I replaced the "HAVING" clauses to "WHERE" and nothing works, changed them back again and it's all good.
John

 

[KitaYama]

Is sTable a public variable?

if not, Shouldn't this

Me.RecordSource = sTable

be

sTable = Me.RecordSource

?

 

[CJ_London]

HAVING and WHERE appear in different places in the sql string

 

[John Sh]

Is sTable a public variable?

if not, Shouldn't this

Me.RecordSource = sTable

be

sTable = Me.RecordSource

?

It seems wrong but the sub is called "ssetSources" not "GetSources". It is as it should be. sTable is a private variable with form scope.

 

[cheekybuddha]

Why are you using aggregated queries? None of your HAVING clauses utilise an aggregate function, so a they should be WHERE clauses (placed before GROUP BY instead of after)

I'm guessing you can probably remove all the GROUP BY clauses altogether.

If you have duplicates, then use DISTINCT in your SELECT clause.

 

[plog]

1st, cheekys comment about DISTINCT is a great comment. Mostly because your GROUP BY's aren't doing what you think they are doing. Here's the SQL of just one function to demonstrate it:

SELECT A.Collector FROM sTable as A GROUP BY A.Collector, A.Genus, A.SpeciesEpithet...

You are using GROUP BY to eliminate duplicates in Collector, but the above SQL doesn't do that since the GROUP BY has more than just the Collector field in it. Suppose this is the data in sTable:

ID, Collector, Genus, SpeciesEpithet
1, Plog, GenusA, XYZ
2, Plog, GenusA, XYZ
3, Plog, GenusB, PQR
4, Plog, GenusC, XYZ

The query will return 'Plog' 3 times because of all the fields in the GROUP BY. If you just GROUP By Collector and not the other fields it will return just 1 record. If you don't use GROUP BY but DISTINCT instead you avoid that error.

Herbarium_Collection -- I don't know where you got the idea there is date stored in the table name or how one could do that.

I got the idea that you are storing data (not date) from 'Herbarium' being in your table name. It is a technical term related to your organization which is a red flag. Table and field names should be generic and easily understood by everyone. Technical terms should be values in tables, not table nor field names. 2nd red flag is it used as a prefix to a noun (Collection) which makes it seems like you have multiple collections and have a table for each, with each named similar to X_Collection.

The table is "Herbarium_Collection" with some 90 fields.

That is a new red flag. No table I have ever encountered that was properly normalized had that many fields. My guess is that a lot of those fields are storing data in the field names or are numerated (e.g. SomeDate1, SomeDate2, SomeDate3 etc.).

The similar table structures are due to a requirement to keep the data from separate sources in their own tables

And that's a quote I've seen from a lot of people working with Access who are in over their heads. Data isn't cranberry sauce on a Thanksgiving dinner plate--it's not going to bleed into the turkey and mashed potatoes and get all mixed up and unrecoverable. If you properly set up your tables the data is separate and you can isolate the Herbarium collection data and any other collection by itself. Why not seperate Access files for them? Or seperate computers for each set of data? Just to be sure because all those 1's and 0's my mix together if they are on the same hard drive?

 

[John Sh]

Plog.
Firstly, normalised, or not. Separate tables, or not. It is what it is and will remain that way as it is the way I was instructed to do it.
I understand normalisation and how it works and if you're a purist you will go to any lengths to accomplish this.
There are, however, many ways to skin the proverbial cat and if all the different ways achieve a "skun" cat, what the hey.
Second. I actually need to return more than one collector, or the same collector more than once. You seem to be somewhat familiar with plants so will know that each Family has many genera and each genus has multiple species and may well have been collected by more than one collector or by the same collector from different places and at different times.
Third. Field names such as Family, Genus, Species, Latitude, Longitude, Collector, Location & etc are not generic enough for you? All of which are field names in the table called "Herbarium_Collection", which is exactly what it is. The collection belonging to this particular herbarium and the "Ourimbah_Collection" belongs to the campus at Ourimbah. While there are similar fields in both collections there are also fields in one that are not in the other.
I know you're going to say "They must be all in one table". Tell that to the people who want them kept separate!
Fourth. In this particular table there are no such fields as (e.g. SomeDate1, SomeDate2, SomeDate3 etc.), you guessed wrongly. Each of the 90 odd fields has a sole, specific, purpose.
Last. I am not trained in software development or engineering so would happily qualify as a novice. I am self taught and make mistakes from time to time. I do, however, have a fully working system that I am constantly improving and updating as requests for other functionality come in. So I do take umbrage at the suggestion that I am "in over my head".
Thank you.
John

 

[MarkK]

It is what it is and will remain that way as it is the way I was instructed to do it.

Horse is thirsty. Horse is led to water. Horse was instructed not to drink. Horse remains thirsty.

 

[GPGeorge]

Spoiler:

Plog.
Firstly, normalised, or not. Separate tables, or not. It is what it is and will remain that way as it is the way I was instructed to do it.
I understand normalisation and how it works and if you're a purist you will go to any lengths to accomplish this.
There are, however, many ways to skin the proverbial cat and if all the different ways achieve a "skun" cat, what the hey.
Second. I actually need to return more than one collector, or the same collector more than once. You seem to be somewhat familiar with plants so will know that each Family has many genera and each genus has multiple species and may well have been collected by more than one collector or by the same collector from different places and at different times.
Third. Field names such as Family, Genus, Species, Latitude, Longitude, Collector, Location & etc are not generic enough for you? All of which are field names in the table called "Herbarium_Collection", which is exactly what it is. The collection belonging to this particular herbarium and the "Ourimbah_Collection" belongs to the campus at Ourimbah. While there are similar fields in both collections there are also fields in one that are not in the other.
I know you're going to say "They must be all in one table". Tell that to the people who want them kept separate!
Fourth. In this particular table there are no such fields as (e.g. SomeDate1, SomeDate2, SomeDate3 etc.), you guessed wrongly.

Each of the 90 odd fields has a sole, specific, purpose.

Spoiler:

Last. I am not trained in software development or engineering so would happily qualify as a novice. I am self taught and make mistakes from time to time. I do, however, have a fully working system that I am constantly improving and updating as requests for other functionality come in. So I do take umbrage at the suggestion that I am "in over my head".
Thank you.
John

Can you show us a sample of 20 or so of those fields to prove your point?