Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm wondering what would be the correct way to combined queries that have two columns that are different, but need to be added to the same report

    Iím trying to link four tables together.
    In one table I'm going to be using all the fields [Feild1],[Feild2],[Feild3],[feild4]

    In the other 3 queries I would like to all a column and put one row of data from each other query's [Feild1],[Feild2],[Feild3],[feild4] adding [FieldA],FieldB]

    This shows just a select query in access, putting the tables together

    [SQL]
    SELECT Wholesale_Rank_qry_2.OMNI_Number,
    Wholesale_Rank_qry_2.branch,
    Wholesale_Rank_qry_2.branch_name,

    Wholesale_Rank_qry_2.[Wholesale_Rank>],
    Table_Rank_qry_2.[Table_Rank>],
    Purchase_Rank_qry_2.[Purchase_Rank>],
    CUSB_Rank_qry_2.[CUSB_Rank>]

    FROM CUSB_Rank_qry_2 RIGHT JOIN (Purchase_Rank_qry_2 RIGHT JOIN (Table_Rank_qry_2 RIGHT JOIN Wholesale_Rank_qry_2 ON Table_Rank_qry_2.OMNI_Number = Wholesale_Rank_qry_2.OMNI_Number) ON Purchase_Rank_qry_2.OMNI_Number = Wholesale_Rank_qry_2.OMNI_Number) ON CUSB_Rank_qry_2.OMNI_Number = Wholesale_Rank_qry_2.OMNI_Number; [/sql]

    The main table with all the data in it is Wholesale_Rank_qry_2. <-This table will have all of the Wholesale_Rank fieldís populated.

    Now the queries below will only have the rank field populated if they have a rank from there original table

    Table_Rank_qry_2.[Table_Rank>],
    Purchase_Rank_qry_2.[Purchase_Rank>],
    CUSB_Rank_qry_2.[CUSB_Rank>]

    Thanks for taking the time to help me learn more about what queries I can us

    TCB

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I am not sure that I understand the problem, but I think the issue is that the last three queries mentioned only contain data for some of the records.

    When you join tables (or queries) in a query you can specify the type of join. To see the options double click the line joining the tables in the query design grid.

    [attachment=91131:JoinProperties.gif]

    For each of your joins you need to specify that you want all records from Wholesale_Rank_qry_2
    Attached Images Attached Images
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •