How to make a seach and selection querry that goes over multiple tables/groups in the database?

[ThunderSpark]

Hi am trying I am learning to create query's but what I learned so far for as the basics, does not really explain enough what I have in my mind to create. I want to keep this short but sadly I cannot do that without explaining my situation first. NOTE: I am using the 2010 version of all office applications that counts Excell and Access also.



Ok here is the situation, currently I am trying to build a plant/tree database based on the information on a online search engine. Be
cause I could not import the data directly I needed to copy all the text when I had "selected" the text and then pasting it in excel tables.
This is actually still in progress because there is so much information and filters I need to process.
Currently I am sorting the information in one table per "Main Filter" and each worksheet representing a sub filter of that main filter. So for example there are you have tree's as plant type but there are different kind of tree's (like a "Climate Tree's) so they represent a sub filter. So each worksheet in every table actually consists of ONE COLUMN which contains the species names.

(Note this how I think it will go so please correct me if I am wrong) To make searching, sorting and adding new data easy these species names will be the primary key in each table. So to keep it short each table (except the unfiltered list) is a "trait table" with the plant species as a primary key.
To make things easier I believe I can group each "Trait Table" in "Trait Collection" for example I have three worksheets in a height category table which are the category of heights in the trait table.


I also have a list of species names that is unfiltered that one is actually already imported but considering this situation I think I might not need this.


I want to program a query that searches for specific traits of plants. For example I want to search for a tree that had a Oval Crown shape (which would be the ), is category 3 (which is trait Table Category 3) and can stay in open ground. So I specify to search in the Crown Shape, height category and location category's/groups. Then what all plant names that have all three types or in other words can be found in all three table and not just one alone. Those plant names I want to have saved in a new query. So how do I do this?

PS: I will be adding a new topic that is based on this one. Since technically you can only import ask one question per topic.

[andy49]

Why not create table in excel then link or import it into access.

Why so many worksheets in excel? Are they different in some way?


Sent from my iPhone using Tapatalk

[ThunderSpark]

They are not really different, they are same as structure mainly one column of species name. The reasons I did this was to better organize data from a online catalogue. I assumed that was because my data is not a "spreadsheet" meaning there not all rows are filled in the columns. It would give problems with import. Keep in mind this was the easiest for me to organize by pasting down the data. I had problems directly importing from the website so I had to do it manually.

I will upload two attachements here, one was the file that originally I wanted

One was what the file was originally one worksheet per Main Filter and one column per subtype. These are in dutch so sorry if you can not understand what is exactly written.

If you want to know the website I try to base my database on here it is: https://www.ebben.nl/en/treeebb/#.

Please analyse and keep what I said in mind.

Boomvormen zonder stam (seperated from Boomsoorten Tree-Eb).zipAttachment 28117
Boomvormen zonder stam (seperated from Boomsoorten Tree-Eb).zipBoomsoorten Tree-Eb (Before Seperation).zip

[andy49]

Well we always say table structure first.

If that isn't right we'll be coming back to it every time you start a new topic.

My expertise is mostly programming so I'm gonna hold back until the structure experts come on board.

Sorry I can't be of more help but your Dbase needs to be normalised.


Sent from my iPhone using Tapatalk

[orange]

As andy49 advised it is better to work with your proposed table structures before getting too far into HOW you are going to do search etc.
I suggest you tell us about your business in simple , plain English (avoid jargon both business and Access).
Tell us about a typical day in your "business" so we understand your set up in some context. You have to identify WHAT is involved in the business and then look at options for HOW that might be supported with a database.

You have Trees and Trees have Traits. I imagine there are some Categorizations also.
Here is a draft Nursery Business data model from Barry Williams' site.

Good luck.

[ThunderSpark]

Oke I will try to explain. I am working here at a community building at the Partimony service. I am an intern working here who has an assignment of creating a database of plants (or tree's specific), which will be linked to a geodatabase (POSTGRESS/POSTGIS THIS HAS ALREADY BEEN SETUP BY ME SO IGNORE THIS) by a table relate. Currently we need to search online at a specific website to search for plants. However we wish to create a access database that BASED ON THAT WEBSITE where you can search by filters, about where the plant must stand and/or what charactestic it has (not only trees) . For example a tree in this case must stand in pavement, half pavement or open soil and it must have "white bark" for esthestics. This way we can work with the base principe "The Right Plant at the Right Place" and make new plant concepts easier. These new tables created from our specific search query's will be uploaded to geodatabase (in the form of DBF files) where they can be linked to our shapefile's if we desire. I know I can convert these into a spread sheet but how currently the data is organized (see my uploads). So I want ACCESS to create that spreadsheet for me based on the "Trait Table" querry's.

[orange]

?? Not sure I'm following

How does the database Postgress relate to the website where you search for plants?
Is this part of your organization or some random site on the internet?
How do you know the accuracy of the data on the website?
What traits do you record/search?
Can you give an example of a search with several traits?
How do you plan to keep the data current?