Results 1 to 11 of 11
  1. #1
    wildlifeaccess is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    6

    Combining queries

    I am trying to combine three queries. All three queries have the same number of fields (7) and the same number of records (391). Four of the fields (BedNB, Sampyear, Treatment, Site) are common to all three queries, and the remaining three fields contain values that are unique to the separate queries.



    I would like to create a query that will result in 13 fields (4 common to all and 3 unique to each) and 391 records.

    A simple select query (SQL below) results in a huge table with almost infinite records that makes no sense to me.

    Any help?


    SELECT sqry_DF_Type_Quads.SampYear, sqry_DF_Type_Quads.Treatment, sqry_DF_Type_Quads.Site, sqry_DF_Type_Quads.BedNB, sqry_DF_Type_Quads.Herbaceous AS H_Cover, Count_DF_SpRchnes_Quads.Herbaceous AS H_Rich, sqry_DF_Type_Quads.Vine AS V_Cover,
    Count_DF_SpRchnes_Quads.Vine AS V_Rich, sqry_DF_Type_Quads.Woody AS W_Cover, Count_DF_SpRchnes_Quads.Woody AS W_Rich, Fqry_DF_Diversity_Quads.Herbaceous AS H_Diversity, Fqry_DF_Diversity_Quads.Vine AS V_Diversity, Fqry_DF_Diversity_Quads.Woody AS W_Diversity
    FROM sqry_DF_Type_Quads, Count_DF_SpRchnes_Quads, Fqry_DF_Diversity_Quads;

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    if the 4 common fields are unique in each table, you can use a join query to join the three tables on those four fields, then you choose the fields you want.

  3. #3
    wildlifeaccess is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    6
    Quote Originally Posted by weekend00 View Post
    if the 4 common fields are unique in each table, you can use a join query to join the three tables on those four fields, then you choose the fields you want.
    Thanks!

    I'm new to SQL so I could use a little help with the code. Do I list all of the queries I want to Join in the JOIN statement? Or do I make do JOIN statements for each query.

    Also, how do I join based on all four common fields instead of just one common fields as I see in most examples?

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Join the three queries on 4 fileds:

    SELECT sqry_DF_Type_Quads.SampYear, sqry_DF_Type_Quads.Treatment, sqry_DF_Type_Quads.Site, sqry_DF_Type_Quads.BedNB, sqry_DF_Type_Quads.Herbaceous AS H_cover, Count_DF_SpRchnes_Quads.Herbaceous AS H_rich, Fqry_DF_Diversity_Quads.Herbaceous AS H_diversity, sqry_DF_Type_Quads.Vine AS V_cover, Fqry_DF_Diversity_Quads.Vine AS V_diversity, Count_DF_SpRchnes_Quads.Vine AS V_rich, sqry_DF_Type_Quads.Woody AS W_cover, Fqry_DF_Diversity_Quads.Woody AS W_diversity, Count_DF_SpRchnes_Quads.Woody AS W_rich
    FROM (Count_DF_SpRchnes_Quads INNER JOIN Fqry_DF_Diversity_Quads ON (Count_DF_SpRchnes_Quads.BedNB = Fqry_DF_Diversity_Quads.BedNB) AND (Count_DF_SpRchnes_Quads.Site = Fqry_DF_Diversity_Quads.Site) AND (Count_DF_SpRchnes_Quads.Treatment = Fqry_DF_Diversity_Quads.Treatment) AND (Count_DF_SpRchnes_Quads.SampYear = Fqry_DF_Diversity_Quads.SampYear)) INNER JOIN sqry_DF_Type_Quads ON (Fqry_DF_Diversity_Quads.BedNB = sqry_DF_Type_Quads.BedNB) AND (Fqry_DF_Diversity_Quads.Site = sqry_DF_Type_Quads.Site) AND (Fqry_DF_Diversity_Quads.Treatment = sqry_DF_Type_Quads.Treatment) AND (Fqry_DF_Diversity_Quads.SampYear = sqry_DF_Type_Quads.SampYear);

    if one of the four fields is unique in three queries, you can join on that field only.

  5. #5
    wildlifeaccess is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    6
    Back again...

    The code posted above by weekend00 solved my initial problem, but I've hit another bump in the road with this series of queries.

    The problem is that I am missing records because they have null values when they actually need to be zeros. I have created another query called "listsites" that contains ALL of the records with the four common fields. Now I need to join that query with the other three queries we joined previously.

    Here is the code that I've worked up myself, but obviously, it isn't working.

    I'd really appreciate any help.


    SELECT listsites.Site, listsites.SampYear, listsites.Treatment, listsites.BedNB, sqry_QF_Type_Quads.Herbaceous AS H_Cover, Count_QF_SpRchnes_Quads.Herbaceous AS H_Rich, Fqry_DF_Diversity_Quads.Herbaceous AS H_Diversity, Count_QF_SpRchnes_Quads.Vine AS V_Rich, Count_QF_SpRchnes_Quads.Woody AS W_Rich

    FROM Fqry_DF_Diversity_Quads

    RIGHT JOIN ((Count_QF_SpRchnes_Quads

    RIGHT JOIN listsites ON (Count_QF_SpRchnes_Quads.[Site] = listsites.[Site]) AND (Count_QF_SpRchnes_Quads.Treatment = listsites.Treatment) AND (Count_QF_SpRchnes_Quads.SampYear = listsites.SampYear) AND (Count_QF_SpRchnes_Quads.[BedNB] = listsites.[BedNB]))


    LEFT JOIN (sqry_QF_Type_Quads LEFT JOIN listsites ON (sqry_QF_Type_Quads.SampYear = listsites.SampYear) AND ( sqry_QF_Type_Quads.[Site] = listsites.[Site]) AND (sqry_QF_Type_Quads.BedNB = listsites.BedNB) AND (sqry_QF_Type_Quads.treatment = listsites.Treatment)

    LEFT JOIN (Fqry_DF_Diversity_Quads LEFT JOIN listsites ON (Fqry_DF_Diversity_Quads.SampYear = listsites.SampYear) AND (Fqry_DF_Diversity_Quads.[Site] = listsites.[Site]) AND (Fqry_DF_Diversity_Quads.BedNB = listsites.BedNB) AND (Fqry_DF_Diversity_Quads.treatment = listsites.Treatment)

    GROUP BY listsites.Site, listsites.SampYear, listsites.Treatment, listsites.BedNB, sqry_QF_Type_Quads.Herbaceous AS H_Cover, Count_QF_SpRchnes_Quads.Herbaceous AS H_Rich, Fqry_DF_Diversity_Quads.Herbaceous AS H_Diversity, Count_QF_SpRchnes_Quads.Vine AS V_Rich, Count_QF_SpRchnes_Quads.Woody AS W_Rich
    Last edited by wildlifeaccess; 09-16-2010 at 08:45 PM. Reason: Another kink

  6. #6
    wildlifeaccess is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    6

    Refresh

    Refreshing this thread

  7. #7
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Since the fields need to be zero, how about update all nulls to zeros before running the query?

  8. #8
    oldman is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    17
    Use left joins and the items that are missing will not stop you getting the other items with those 4 things in common.

  9. #9
    wildlifeaccess is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    6
    Quote Originally Posted by weekend00 View Post
    Since the fields need to be zero, how about update all nulls to zeros before running the query?
    I can't. The records have attributes that are queried via a lookup table. There are no records that match the criteria (via a lookup table) I am looking so that creates the null fields.

    Not sure if this makes sense, but I really don't think I can do that.

  10. #10
    wildlifeaccess is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    6
    Quote Originally Posted by oldman View Post
    Use left joins and the items that are missing will not stop you getting the other items with those 4 things in common.
    Forgive my ignorance, but I really can't figure out these joins. I have tried several combinations of joins but none seem to work. It's probably my programming.

    I hace since made this situation work out.

    I created multiple sub queries with the query wizard that finally got me what I needed. I ended up with 6 additional queries when I think it could have been done with two.

    gm

  11. #11
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    you can modify a little bit on all the join fields to get the nulls. just use nz(joinfieldn,"yes") instead of joinfieldn. e.g.
    select * from x inner join y on nz(x.field1,"yes")=nz(y.field1,"yes") ...

    (you need to go to SQL view to modify the query)

    Please note again if the 4 common fields are not unique, you will get more than 391 records in the result.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combining fields
    By cotri in forum Forms
    Replies: 2
    Last Post: 01-18-2010, 12:06 PM
  2. Combining Two Queries
    By csolomon in forum Queries
    Replies: 1
    Last Post: 09-03-2009, 01:33 PM
  3. Replies: 0
    Last Post: 03-31-2009, 02:05 AM
  4. Combining like update queries
    By xcr800man in forum Queries
    Replies: 2
    Last Post: 05-20-2008, 08:36 AM
  5. Replies: 1
    Last Post: 06-21-2007, 01:02 PM

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