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
    Ok, I have a couple of quirky things going on with my database.

    The first is my Rank formula.
    For some reason I have to have two Rank fields in order for at lest one to be correct.

    **I have attached the sample database if you want to view that also**

    The second one is when Iím trying to make a master join query from all my tables.

    This one Iíve been having trouble with all day!
    Iíve tried different joins, different queries, PLEASE HELP!

    In Plain English is would sound like this:

    From Wholesale query, show all records and take fields
    OMNI_Number,
    branch,branch_name,
    Wholesale_Rank>,
    Wholesale_Star_Rating

    Then from Table query, show only the records the are in the Table query and fields blank that done have data for that omni number

    The same for the other two tables

    What Iím hoping to get for my final outcome
    OMNI_Number,
    branch,
    branch_name,
    Wholesale_Rank>,
    CUSB_Rank>,
    Purchase_Rank1,
    Table_Rank>,
    Wholesale_Star_Rating,
    CUSB_Star_Rating,
    Purchase_Star_Rating,
    Table_Star_Rating,

    [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>], CUSB_Rank_qry_2.[CUSB_Rank>], Purchase_Rank_qry_2.Purchase_Rank1, Table_Rank_qry_2.[Table_Rank>], Wholesale_Rank_qry_2.Wholesale_Star_Rating, CUSB_Rank_qry_2.CUSB_Star_Rating, Purchase_Rank_qry_2.Purchase_Star_Rating, Table_Rank_qry_2.Table_Star_Rating
    FROM ((Wholesale_Rank_qry_2 LEFT JOIN CUSB_Rank_qry_2 ON Wholesale_Rank_qry_2.OMNI_Number = CUSB_Rank_qry_2.OMNI_Number) LEFT JOIN Purchase_Rank_qry_2 ON Wholesale_Rank_qry_2.OMNI_Number = Purchase_Rank_qry_2.OMNI_Number) RIGHT JOIN Table_Rank_qry_2 ON Wholesale_Rank_qry_2.OMNI_Number = Table_Rank_qry_2.OMNI_Number;[/sql]

    Thanks for taking the time to help me.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Corey Vlasak View Post
    The first is my Rank formula.
    For some reason I have to have two Rank fields in order for at lest one to be correct.
    What query should I look at in your sample to see this problem?

    For the second question, for each join you want All Records from Wholesale_Rank_qry_2. In the Diagram there should be an arrow pointing away from Wholesale_Rank_qry_2 for each join.

    The query CUSB_Rank_qry_2 is returning multiple records for each OMNI_Number, so when you joing this to the Master query you also get multiples.
    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
  •