SQL string v stored query

[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?

 

[John Sh]

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

I've been away for a while so apologies for the slow response.
Attached is a sample of one of the tables. I have included some data from the beginning and end of the table.
NOTE.
I have changed my email address to segref23@gmail.com.

 

[GPGeorge]

Thank you.

It may take a little while for the normalization gurus to analyze the table. It's a specialized field and some of the nomenclature needs to be clarified.

That said, I'm sure you'll get plenty of feedback in the near future as people have time to work through the different components of the data.

I would also have liked to see sample data for a couple dozens records to aid in figuring out the content.

 

[John Sh]

Tell me what field names need to be clarified and I'm happy to do that.
I have added 60 or so records to the database.
Bear in mind that not all fields are used in every record and some are rarely used but required for consistency, none the less.

It does seem, however, that nobody listens to what I say.
If the "gurus" want to normalise it, go for it but I will not be changing the structure of my system.
The system works in all the many facets that is required of it and normalisation will not make it any better.
Normalisation will, in fact, create the need for many more tables than the system already has. Where is that a plus, other than + more tables?
It is what it is because it is the way others require it to be and that is the end of that.
John

 

[GPGeorge]

Tell me what field names need to be clarified and I'm happy to do that.
I have added 60 or so records to the database.
Bear in mind that not all fields are used in every record and some are rarely used but required for consistency, none the less.

It does seem, however, that nobody listens to what I say.
If the "gurus" want to normalise it, go for it but I will not be changing the structure of my system.
The system works in all the many facets that is required of it and normalisation will not make it any better.
Normalisation will, in fact, create the need for many more tables than the system already has. Where is that a plus, other than + more tables?
It is what it is because it is the way others require it to be and that is the end of that.
John

I see. Well, I had to try to help. In the end, of course, it's your application to manage.

Best of luck going forward.

 

[John Sh]

I see. Well, I had to try to help. In the end, of course, it's your application to manage.

Best of luck going forward.

Thank you for your offer of help and, in particular, for recognising that it is my application to manage.
John

 

[KitaYama]

I will not be changing the structure of my system....that is the end of that.

 

[GaP42]

As someone who is familiar with the taxonomy of living things (but defer to your expertise, especially wrt herbaria and the specific data requirements) and with db design I do have concerns with the apparent flat file db structure (50 field items listed and 6 records) you have provided. I do not know the full structure of your db.

As you have indicated:

“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.”

You also said you understand normalisation, however, there has been no application of the practise of normalisation to the data structure as shown so far.

What does that mean? If we take your sample table as the data structure you actually employ then every time you create a new record you appear to be duplicating the entry of replated data. You are risking data integrity - relying upon typing entries and getting the text exactly the same in all cases to match to existing data.

• You appear to enter “Myrtacae” as the text in the field for Family 3 times in the sample data. Are you typing this each time, or do you rely on a restricted list of the available Families in the Order “Myrtales” to select “Myrtacae” and so on up the classification tree.
• The collector of a specimen is named. They some times belong to a team / expedition that also has a name or identifies the group. Do you rely on typing this data exactly the same on each occasion a new record is added for specimens collected by the same team or can you rely on a reference list? The same person may belong to multiple teams.

These features are to enable you to maintain the quality of your data and to ensure you can retrieve them without having to fudge things to account for mistakes in data entry. If you need to adjust the name of a group - then you can adjust it one place and it is reflected in all records – or do you plan on writing SQL to update it everywhere? Case in point in your limited sample “Common Name Sandpapr Fig” – a typo – it may be trivial and not appropriate to show the real issue but it may mean your query to retrieve records of Sandpaper Figs does not return all expected records.

A specimen will be of a Species belonging to a Genus and so on up the classification tree.

For a specimen you have a Herbarium in which it is stored. The Herbarium will have a catalog id / Accession for the specimen. The accession item will then have details of the location of the specimen in the Herbarium. (Bay Shelf Box)

Associated to the Specimen will be a record of the

Species

– name, first named by, type specimen date …

Collection Details

• Who collected / when collected
• Location where specimen collected (geocoding)
• Soil, Geology, Climate, other observational notes,,,

This is just a cursory view of what has been shown

You are welcome to continue on your path – but

“if the "gurus" want to normalise it, go for it but I will not be changing the structure of my system.
The system works in all the many facets that is required of it and normalisation will not make it any better.
Normalisation will, in fact, create the need for many more tables than the system already has. Where is that a plus, other than + more tables?
It is what it is because it is the way others require it to be and that is the end of that.”

Others require it to be that way because they I presume see a list of data items for specimens collected within the herbarium, and the types of lists they want to produce from that data. They do not have insight into the best ways to facilitate that – to support data quality and integrity, minimising duplication.

 

[The_Doc_Man]

Normalisation will, in fact, create the need for many more tables than the system already has.

Technically, this is a false statement. Normalization identifies how many more tables your data topic requires to isolate the data elements to avoid duplication or excessive convolution of data. Normalization is a type of analysis to optimize data layout given your topic's elements and the general concepts of relational databases. If you don't want to normalize, fine. But at some point you might as well use spreadsheets. Using a massively complex schema that doesn't include normalization is like painting yourself into a corner. I acknowledge it is your corner to paint. But understand that when you come to us for advice, we will give it even if it is advice you didn't want to hear.

It is what it is because it is the way others require it to be and that is the end of that.

OK. Lemmings go over a cliff because other lemmings want it to be that way and that is the end of that, too. Your response presumes that the ones who want it a certain way understand relational database concepts. Care to wager on that?

Taxonomy is a real pain in the patootie because it is a tree structure with several uneven branches involved in full identification. I say "uneven branches" because by the time you get to hominids, there are only seven living member species in four genuses (including homo sapiens... us, on a one-member genus). But if you look at Astrolagus, it has 3300 species at the genus level. Very unbalanced. All I can say is, good luck dealing with a seriously unbalanced tree structure without normalizing to some degree.