Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Carnior is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Apr 2015
    Posts
    6

    Multiple criteria query joining two tables

    Hello,

    First, thank you for your time; I apologize if this thread appears somewhere else, but my searches were inconclusive.

    I am working with a set of two tables containing data on regions, habitats, species recovery documents, standard conservation actions, and, of course, species name (taxa). For context, in the first table (see Table 1), the species recovery documents list recovery measures which all have a standard conservation action equivalent. In the second table (see Table 2), standard conservation actions are identified for species but are specific to regions and habitats. Furthermore, there are 32 regions, 14 habitat classes, 33 species recovery documents including 150 recovery measures, so you can imagine that the following objective can only be achieved with a query.

    The objective is to identify conservation actions that have equal Region and Habitat values in both tables. In other words, this will give us a list of species associated to conservation actions contained within each recovery document (see Table 3).

    This is a quasi-SQL statement of the basic logic of what I am trying to explain:

    INNER JOIN Table1.ConservationAction ON Table2.ConservationAction
    WHERE Table1.Region=Table2.Region AND Table1.Habitat=Table2.Habitat AND Table1.ConservationAction=Table2.ConservationActio n
    GROUP BY Table1.Region, Table1.Habitat, Table1.RecoveryDocument, Table1.ConservationAction, Table2.PrioritySpecies


    Table 1
    Region Habitat Recovery Document Conservation Action
    14-NB Wetlands Management Plan for the Yellow Rail 1.16. Identify and protect important wetland habitat
    14-NB Coastal Recovery Strategy for the Roseate Tern 5.115. Promote compliance with, and enforce, existing legal protection
    3-NL Coastal Recovery Strategy for the Piping Plover 2.57. Maintain/improve oil spill response programs
    ... ... ... ...




    Table 2
    Region Habitat Conservation Action Priority Species
    14-NB Wetlands 6.18 Provide incentives for landowners to protect wetlands/waterbodies Yellow Rail
    7-NL Wetlands 1.16. Identify and protect important wetland habitat Olive-sided Flycatcher
    14-PE Coniferous 1.11. Identify and protect imporant forest habitat Rusty Blackbird
    ... ... ... ...


    Desired query result
    Region Habitat Recovery document Conservation Action Priority Species
    14-NB Coastal Recovery Strategy for the Roseate Tern 1.22. Secure long-tern protection of coastal habitat American Bittern
    " " " " Bank Swallow
    " " " " Common Tern
    " " " " Spotted Sandpiper
    " Wetlands Management Plan for the Yellow Rail 2.52. Maintain and/or restore wetland habitat American Black Duck
    " " " " Magnolia Warbler
    7-NL ... ... ... ...
    ... ... ... ... ...


    PROBLEM: When building the query to obtain the desired results (see above), I get conservation actions without regards of their attributed region and habitat in Table 2. It is important that each conservation action appears in the right geographical context, i.e. it would not make sense to have "Maintain and restore wetland habitat" associated with, in the query results, Coniferous habitat.


    Thank you in advance,

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, can have multiple link criteria in JOIN clause, although the WHERE clause should also restrict recordset to matching records. However, JOIN clause is recommended.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Your table structure looks suspect to me.
    Would like/need to see the dB.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm with Steve - I think your tables may need some work.

    You have mentioned
    regions, habitats, species recovery documents, standard conservation actions, and species name (taxa).
    I would identify some of the business rules either explicit or implied in your project description.

    Seems a Region could house 1 or many Habitats
    Seems a Region or Habitat could be occupied by 1 or many Species
    etc

    I'm not a biologist/conservationist, but these things seem to be "subjects/entities" within the scope of your project.
    see Habitat for example

    Good luck.
    Last edited by orange; 04-29-2015 at 05:19 PM. Reason: Info from Steve --applied

  5. #5
    Carnior is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Apr 2015
    Posts
    6
    Thank you for your replies. You guys are obsolutely right and maybe the description of my project was not clear enough. Regions house many habitats; habitats are occupied by one or many species; and conservation actions aim one or many species. You can see where this is going... The examples that I've provided above are simplified tables showing, for example, that in region 14-NB there is a Management Plan in the wetland habitat with one of the conservation action being 1.16. Identify and protect important wetland habitat. However, without implementing a query, I can look at Table 1 and Table 2 and find answers to my question - but the task would be extremely time consuming because of all the possible combinations.

    To achieve the obtain the desired table (see 3rd table), I want Access to recognize the Table2.ConservationActions which occur in the same Region-Habitat combination where the Table 1.RecoveryDocument also occurs. This is where I fail to apply this logic in Access, and obtain query results where Conservation actions that normally only occur in one region (say east of Canada) appear in another (say west of Canada).

    Thanks for your support!

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You know your information better than the readers. But readers may know more about database design and Access. From a database perspective it is important to organize your data into tables and relationships that "make sense"/support our "business".
    You have provided some business rules in this
    Regions house many habitats; habitats are occupied by one or many species; and conservation actions aim one or many species.
    1 Region may house 1 or many Habitats
    1 Habitat may be occupied by 1 or many Species
    1 ConservationAction is focused on 1 or many Species

    So in general terms your tables (those in the description) are the following
    and the relationships (at least some of them) would be as follows
    - where A--->B indicates 1 A related to 1 or Many B ( 1 to Many relationship)

    Region -->Habitat--->Species<---ConservationAction

    I would not suggest you arbitrarily identify tables independent of your business rules. You may get some groupings that make sense to you (as table1 and table 2 do)

    I'm not sure what a recovery document is --I'd guess it is some outcome from a ConservationAction (but as I said I'm not a biologist). I'll let you elaborate on the "things/entities" within your business.

    You mention Species and Priority Species, can you tell us how these relate/what they mean?
    I don't see management plan in you latest description. Seems it would be some set/subset of
    ConservationActions within some regions/habitats focused on Species influenced by some set of Priorities - but that's a guess.

    It seems your regions are a breakdown of Province into some sort of areas, correct?
    Also seems that there is a hierarchy within your "region" -- East of Canada (NL, PE, NB...)

    Get your database designed to meet your requirements before you worry about query design etc.

    Good luck.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As far I can see from the example tables, if they are joined on Region, Habitat, and ConservationAction, the output should be correct.

    Did you attempt the query you posted? Even the WHERE clause should produce correct output, although query performance might be less efficient.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Carnior is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Apr 2015
    Posts
    6
    Orange,

    Will trying to grasp everything that you said regarding relationships and identifying tables, I will further clarify that the Recovery documents are basically broad strategies that include (not an outcome of) Conservation Actions. Hence RecoveryDocument --> ConservationAction --> Species.

    Species and priority species are the same and refer to the Table2.PrioritySpecies column. It might be relevant to note that a particular species will occupy one or more regions and occupy one or more habitat.

    Indeed, ConservationActions are influenced by underlying threats... but this is another story (I hope) because all I am interested in are the species aimed by ConservationActions.

    You are correct when saying that the regions are a breakdown of provinces. They are, however, the most relevant spatial scale at which I am looking at the ConservationActions and Species.


    June7,

    When I look at my two tables side by side, I know that they are organized correctly because I can "manually" look up and find the desired results. I am quite a beginer at Access, I know 'what' SQL is and how to interpret it, but would not know how to integrate that WHERE clause in the SQL statement.

    I don't understand when you say 'if they are joined on Region, Habitat, and ConservationAction', do you imply creating three Inner Joins?. If you say that the output should be correct, then the eroneous output I get might stem from the organization of my tables as Orange said?


    Regards,

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Use the query builder. Basic Access functionality. Creating the WHERE clause can be done in the query design grid. The join links are established in the table/query window.

    I mean one INNER join with 3 links. This is a compound key.

    You said the posted statement was quasi-SQL - what is the exact query you attempted? If you do not know how to integrate the WHERE clause then what did you do?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It might be relevant to note that a particular species will occupy one or more regions and occupy one or more habitat.
    Yes, very - if a Habitat can contain many Species and a Species may occupy many Habitats - it seems to be a Many to Many relationship. If this is correct, then a junction table - tbljncHabitatForSpecies may be required.
    This table would identify which Habitat and Species combination are relevant to your study/business.
    See junction table Many to Many It could even be that the finer area of relevance to your study is Region-Habitat-Species - that is it is Species within Habitat within Region.

    Perhaps it is just me, but referring to tables as table1 and table2 is raising a flag. Those may reflect some grouping of your intended study, but I don't see them as table/entity names in a data model.

    I also see Strategies and ConservationActions as things to rectify/improve a recognized problem or condition.
    You may very well have tables suited to your needs, but it isn't clear to me. If you are happy with your set up and it can answer the questions you pose, then go with it. It is for your business/project. It really isn't whether I understand your solution or not.

    Good luck with your project.

  11. #11
    Carnior is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Apr 2015
    Posts
    6
    June7,

    My 'SQL' example was not the actual query statement from Access, it was only a way to express the logic behind what I need! Sorry for the confusion I guess that I find the SQL more intuitive to express a logical statement than plain English language (which is not my first language). I did not build my query from SQL, but with the View interface, adding tables, etc.

    This is the definition I find for compound (composite) key: A composite key, in the context of relational databases, is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. Uniqueness is only guaranteed when the columns are combined; when taken individually the columns do not guarantee uniqueness.

    This seems to put the finger on my problem, which is to uniquely identify each row in the table from a combination of two or more columns (Region, Habitat, ConservationAction).

    Now, more generally, could you explain to me the fundamental difference in the results between example 1 and example 2, below:

    Example 1:

    Click image for larger version. 

Name:	Example1.jpg 
Views:	14 
Size:	10.4 KB 
ID:	20547


    Example 2:

    Click image for larger version. 

Name:	Example2.jpg 
Views:	14 
Size:	10.5 KB 
ID:	20548


    If I interpret your comment correctly, example 2 would create a 'compound key' and identify unique rows from the attributes of two columns (Habitat, ConservationAction)?

    Orange,
    I am addressing June7 here, but by no mean I am disregarding your comments: they encourage me to think about the basic organisation of my tables and the logic behind how they are related.

    Thank you

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I think you also need to include the Region fields in the compound key.

    Hard to describe what happens if the linking does not provide unique identifier, but basically will get a Cartesian result - every record in each table will join every record of other table (at some level depending on the links) because the query doesn't know what else to do without proper instructions. Do some testing with the links and evaluate the output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm sticking with my theme. We may come back to your tables, but for the moment, I'd like to see a data model using names of the "things"/"entities you have described.
    For each of the things, we need to see how they relate, based on your business.

    Here is a data model from Barry Williams' site
    http://www.databaseanswers.org/tutor...al_slide_9.htm

    Here's an example of the business rules/facts from Barry Williams' site (for demonstration purposes)
    Code:
    B. THE THINGS OF INTEREST include :-
    B.1 Addresses.
    B.2 Customers.
    B.3 Deliveries
    B.4 Orders.
    B.5 Payments
    B.6 Products.
    B.7 Suppliers.
    
    
    C. These THINGS are Related as follows :-
    C.1 A Customer can have zero,one or many ORDERS.
    C.2 A CUSTOMER_ADDRESS can be associated with only one ADDRESS.
    C.3 A CUSTOMER_ADDRESS can be associated with only one CUSTOMER.
    C.4 A DELIVERY is associated with just one ORDER.
    C.5 An ORDER must be associated with one and only one CUSTOMER.
    In different applications in the real world, it is possible that an ORDER can
    be associated with more than one CUSTOMER,but in our example, it's only one.
    C.6 An ORDER can be associated with one or many PRODUCTS.
    C.7 A PAYMENT must be associated with one and only one ORDER.
    Please NOTE: This does not mean there is anything wrong with your set up. I just don't like table1 and table2 as names--they convey no business meaning (to me).


    In your samples:
    In 1 only 1 link so these field values have to match in both tables. ConservationAction must match, but any Habitat is OK.

    In 2 you have 2 links/joins, so values of both corresponding fields in each table have to match.
    You are dealing with records that have equal values for ConservationAction and for Habitat.

  14. #14
    Carnior is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Apr 2015
    Posts
    6
    June7,

    You are correct, the Region fields need to be included in the compound key. I have tested with my database and it appears that the query is working!

    The solution to finding conservation actions from equal Regions and Habitats was to add INNER joins. It was unknown to me that you could have more than one INNER join between two tables and that these INNER joins would match equal values.

    Orange,

    Thank you for your insights on this topic. Table1 and Table2 were titles that I used here as examples - but your insight on this led me to chose more appropriate titles in the actual database: "Recovery Documents Description" and "Regions Conservation Actions". I will look at the demonstrations you provided.

    Thank you for your time

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you show us a jpg of your latest Relationships Window?
    For more background on tables and relationships you may get some insight from
    free videos here

    These are a little more on the academic side, but it seems your project is more academic/statistical than the typical Employee/Project, or Customer/Orders posts we see.
    Good luck.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Joining 2 tables on multiple fields
    By smoothlarryhughes in forum Queries
    Replies: 6
    Last Post: 12-13-2012, 10:40 AM
  2. Joining 2-Tables in a query
    By djclntn in forum Queries
    Replies: 7
    Last Post: 03-15-2012, 08:37 AM
  3. Replies: 14
    Last Post: 02-12-2012, 10:14 AM
  4. Joining 2-Tables in a Query
    By djclntn in forum Queries
    Replies: 25
    Last Post: 01-13-2012, 12:54 PM
  5. Query criteria, multiple tables
    By Vicker in forum Queries
    Replies: 2
    Last Post: 08-16-2011, 01:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums