none
Query to select records in one table that exist in aother table based on a column RRS feed

  • Question

  • Hi Everyone

    Is there a fairly easy way to write a query in Design View or in SQL that selects records from one table that exist in another table based on one column?

    In Design View for a column, I tried the below, but it keeps prompting me for values:

    In ([CaseExport].[Local Case Number])

    Paul


    • Edited by Paul-NYS Friday, April 15, 2016 3:31 PM
    Friday, April 15, 2016 3:26 PM

Answers

  • You should be able to just join the two:

    SELECT DISTINCT Hearings.*
    FROM Hearings INNER JOIN CaseExport
    ON Hearings.[Case Number] = CaseExport.[Local case number];

    You might or might not need the DISTINCT predicate in the SELECT clause depending on whether the JOIN otherwise returns multiple instances of each row or not.

    Ken Sheridan, Stafford, England

    • Marked as answer by Paul-NYS Friday, April 15, 2016 6:42 PM
    Friday, April 15, 2016 5:06 PM
  • Hi Paul. I agree with Ken. As I was saying earlier, you shouldn't need any criteria if you just add the other table in the query design grid and link the two "matching" fields together. Try his suggested query and let us know if it doesn't work. Cheers!
    • Marked as answer by Paul-NYS Friday, April 15, 2016 6:42 PM
    Friday, April 15, 2016 5:11 PM

All replies

  • Hi Paul. You would normally just add the other table in the query design grid and link the two fields together. You won't need any criteria. Hope that helps...
    Friday, April 15, 2016 4:03 PM
  • Hi DB Guy

    That won't work in this instance. I need three distinct resulting data sets from 3 queries. The first one is easy and is just a select and I create a table from the query result. The second two need to based on matching records from the first and potentially one other table for the third query. Any ideas for this scenario?

    Paul

    Friday, April 15, 2016 4:14 PM
  • Hi Paul. Sorry, I am not following. If you create a table based off the first query and want to match them with a second query, then why can't you join the second query to the table? Can you post some images to help us understand what you're trying to accomplish?
    Friday, April 15, 2016 4:26 PM
  • Hi DB guy:

    "then why can't you join the second query to the table?" That is what I am trying to do actually. I want the selection of records for the second query (screenshots below) to be based on the presence of the Hearings.Case Number in the CasesExport.Local Case Number table. So I tried the below, but Access just prompts me for values:

    Also, there is a sequence ID in both tables and Access prevents you from having a query result with two sequence IDs--I wouldn't want to do this anyway:


    • Edited by Paul-NYS Friday, April 15, 2016 4:49 PM
    Friday, April 15, 2016 4:48 PM
  • You should be able to just join the two:

    SELECT DISTINCT Hearings.*
    FROM Hearings INNER JOIN CaseExport
    ON Hearings.[Case Number] = CaseExport.[Local case number];

    You might or might not need the DISTINCT predicate in the SELECT clause depending on whether the JOIN otherwise returns multiple instances of each row or not.

    Ken Sheridan, Stafford, England

    • Marked as answer by Paul-NYS Friday, April 15, 2016 6:42 PM
    Friday, April 15, 2016 5:06 PM
  • Hi Paul. I agree with Ken. As I was saying earlier, you shouldn't need any criteria if you just add the other table in the query design grid and link the two "matching" fields together. Try his suggested query and let us know if it doesn't work. Cheers!
    • Marked as answer by Paul-NYS Friday, April 15, 2016 6:42 PM
    Friday, April 15, 2016 5:11 PM
  • Thank you both! That did it!

    Paul

    Friday, April 15, 2016 6:42 PM
  • Hi Paul. Glad to hear you got it sorted out. Ken and I were happy to assist. Good luck with your project.
    Friday, April 15, 2016 7:04 PM