none
office-Access 2010 Help Please. Homework!

    Question

  • in access 2010 how do you modify a query so that all results appear even if there is no data input for a specific part?   I need every single  step and detail. be very specific because i do NOT know what to click on, or know where anything is located. im new to access and know nothing about it.

    I in sam 2010 access chapter two project for homework. and the question was that i has two different tables. Sales Rep and Customer. i already sort the customer by the last name in descending order but now i have to "modify the query so that all sale reps appear in the result (like when you press RUN!) even if they currently have no customers." then i had to save the query.

    Monday, April 29, 2013 7:27 PM

Answers

  • Double click on the line that links the two tables and select the option that says something like

    "Select ALL records from Sales Reps and only those records from Customer that match."

    That is what is known as an OUTER JOIN.  When you have the normal link it is an INNER JOIN which selects only records that exist in both tables for the linked field(s).


    Bob Larson, Access MVP 2008, 2009, 2011

    • Marked as answer by regina55555 Monday, April 29, 2013 10:55 PM
    Monday, April 29, 2013 7:35 PM

All replies

  • Double click on the line that links the two tables and select the option that says something like

    "Select ALL records from Sales Reps and only those records from Customer that match."

    That is what is known as an OUTER JOIN.  When you have the normal link it is an INNER JOIN which selects only records that exist in both tables for the linked field(s).


    Bob Larson, Access MVP 2008, 2009, 2011

    • Marked as answer by regina55555 Monday, April 29, 2013 10:55 PM
    Monday, April 29, 2013 7:35 PM
  • Oh, and I commend you for stating that this is a homework problem as many try to just slip it by.  Because of your honesty and because you asked a specific question, I chose to answer.  I just thought I'd point that out for anyone who just feels the need to post to get us to do the work for them.

    Bob Larson, Access MVP 2008, 2009, 2011

    Monday, April 29, 2013 7:36 PM
  • I pushed RUN! and it says "the SQL statement could not be excuted becuase it contains ambitious outer joins.  To force one of the joins to be performed first, create a seperate query that performs the first join and then include theat query in you SQL document"    How do i do that?

    .

    Monday, April 29, 2013 8:00 PM
  • The first 2 columns are common fields.  The last 3 fields only show data if the primary key (Link between the 2) are the same.   Regina, you should not be getting that error.  Just wanted to help, hope it does.


    I am not an expert, but the only situation i have seen this error is when you are using a query as a table...
    • Edited by La Mole Monday, April 29, 2013 8:22 PM
    Monday, April 29, 2013 8:20 PM
  • That sounds like you have more than two tables in your query or you have more than one link between the two tables.  If the first, then you would need to either change the link to the other one to an Outer Join as well or you would need to do the one set of two tables in a separate query and then bring that into this one instead and removing the two tables you currently have and then join with the third, fourth, etc.

    If the second option, just change the other links too.


    Bob Larson, Access MVP 2008, 2009, 2011

    Monday, April 29, 2013 8:24 PM
  • earlier i dragged one of the label to the other query to make a "line relationship" how do i undo that. or delete the relationship between the two? perhaphs that caused a problem.
    Monday, April 29, 2013 8:35 PM
  • If in the Relationships window, you do not set relationships for queries there - only tables.  If you did, just right click on the line and DELETE should show up as an option.  Delete that line and then delete the query from the relationships window.

    If you are talking about in the QUERY design grid, just right click on the line and Delete should show up as an option and you can then select it and it will remove the link.


    Bob Larson, Access MVP 2008, 2009, 2011

    Monday, April 29, 2013 8:38 PM
  • btw. thanx for replying to my question. i posted this question on a lot of forums and Q&A's and you the only one that has been replying so far. thanx for your time and help. i feel grateful. Hmmm... Larson. i know someone at my college; last name Larson. Do you teach at Wayne CC?
    Monday, April 29, 2013 8:39 PM
  • i think i may have found part of the answer on http://office.microsoft.com/en-us/access-help/create-edit-or-delete-a-relationship-HA010072597.aspx#BM7.  How to edit, create, delete relationship.
    Monday, April 29, 2013 8:41 PM
  • Larson. i know someone at my college; last name Larson. Do you teach at Wayne CC?
    Nope, not me.

    Bob Larson, Access MVP 2008, 2009, 2011

    Monday, April 29, 2013 8:46 PM
  • oh, lol. well, im going to restart a new access graph from the beginning. perhaphs i messed something up. i'll try emailing my teacher. exams for me is next week. got about 3 of them in different classes. I know just about everything in MS office. but access never had any expereince in it. took about every computer class they had from middle all the way up to high school. but thanks for you time again, really appreciated it. just reply back on the page for anything else. sorry it take me a long time to reply as well.
    Monday, April 29, 2013 8:58 PM
  • I redid my whole chart, and it must of been something that was entered wrong. I looked in my Discoving Computers: Microsoft Office 2010 book. and There it had it : exactly what you said. step 1: Return to Design View, step 2: click Join Properties on the shortcut menu, Step 3: Click Option button 2;  but in my case it was 3. step 4: View Query Results(Run!) and bam! yaaaay :) :) thanx! so much for all your help. Voted best answer.
    Monday, April 29, 2013 11:01 PM
  • Glad to hear you got it.

    Bob Larson, Access MVP 2008, 2009, 2011

    Monday, April 29, 2013 11:16 PM