Databases
StackOverflow2013
Postsdbo
POMS Access Subquery that returns Multiple Fields
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMS Access Subquery that returns Multiple Fields
    primarykey
    data
    text
    <p>So im back again with more MS Access problems. I have a INSERT INTO query with a subquery that checks to see if the data already exists.</p> <pre><code>SELECT name, course FROM foo WHERE (name, course) NOT IN (SELECT name, course FROM bar); </code></pre> <p>to expound a little on what i am trying to accomplish since the above doesn't work.</p> <p>I am trying to select composite keys that do not exist already in the table bar. For example the following could be stored in the table bar:</p> <pre><code>"John Doe" , "Calc 101" "John Doe" , "English" "Jane Doe" , "Calc 101" </code></pre> <p>And the following could be in the table foo:</p> <pre><code>"John Doe", "Calc 101" "John Doe", "Science" </code></pre> <p>The query should return the following:</p> <pre><code>"John Doe", "Science" </code></pre> <p>Everywhere i have looked says the above will work and im sure it does in theory. The problem i run into is with MS Access... When i attempt to run this query it pops up stating that the subquery will return multiple fields. Indeed it should as that is what i want it do do is return 2 fields that i can compare the other 2 fields. The above 2 fields are a composite key in my "bar" database. </p> <p>For more background I am using MS Excel 2007 and MS Access 2007. The Excel is being used to input data and running the query through VB script. I am trying to make a subquery that checks for fields already in the final database because i ran into the error of MS Access opening up and spitting out a ERROR message about appending primary keys and closes with out executing the query. &lt;-- That is to be expected due to the composite key.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJan
    1. USdivdeamor
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou say "Everywhere i have looked says the above will work..." can you show your sources?
      singulars
      1. POMS Access Subquery that returns Multiple Fields
      1. USTony
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload