none
Unmatched data in tables RRS feed

  • Question

  • I have two tables>  tblWords & tblBMData. (Access 2013)

    One has a list of 1,000 words the other holds the data, including words,  that was selected and saved by the user from an input form.

    I need to know which words in tblWords have not been stored in the tblBMData.

    Knowing this will allow me to create a report that lists the words that haven’t been stored yet.

    I thought I had solved this issue but it turns out I was wrong. I tried creating an Unmatched Query but it didn’t give me the correct results.

    Suggestions would be appreciated.


    C vivid

    Tuesday, July 28, 2015 5:48 PM

Answers

  • Try these 2 queries --

         qryStudentWord  --

    SELECT tblStudent.[Student#], tblWord.Word
    FROM tblStudent, tblWord;

    SELECT qryStudentWord.[Student#], qryStudentWord.Word
    FROM qryStudentWord LEFT JOIN tblBMData ON (qryStudentWord.[Student#] = tblBMData.StudentNum) AND (qryStudentWord.Word = tblBMData.Word)
    WHERE (((tblBMData.StudentNum) Is Null) AND ((tblBMData.Word) Is Null))
    ORDER BY qryStudentWord.[Student#], qryStudentWord.Word;


    Build a little, test a little

    • Marked as answer by L.HlModerator Tuesday, August 4, 2015 8:44 AM
    Thursday, July 30, 2015 4:06 AM

All replies

  • Hi. Your tblBMData table structure shows that the field Word is a Short Text, but your screenshot of the datasheet view for the same table displays values like 103, 102, 109, etc. for the Word column. Are you using lookup fields? If so, that could be an issue. Anyways, what happens if you tried something like this?

    SELECT tblWords.Word
    FROM tblWords
    LEFT JOIN tblBMData
    ON tblWords.Word=tblBMData.Word
    WHERE tblBMData.Word Is Null

    Tuesday, July 28, 2015 6:53 PM
  • Sorry but I don't know how to use the answer you gave me. Where do I enter it? In a query?

    C vivid

    Tuesday, July 28, 2015 7:49 PM
  • This query gives me most of what I want but not the studentnum which is important.


    C vivid

    Tuesday, July 28, 2015 7:54 PM
  • Hi. Not sure I completely understand your requirement. If tblWords stores all the unique words that each student can learn, which are then stored with their student number in tblBMData after they have learned it, are you trying to determine which words each student hasn't learned yet? If so, you'll need a third table for the unique list of student numbers. Do you have that?
    Tuesday, July 28, 2015 7:58 PM
  • Here is the contents of the table tblBMData.


    C vivid

    Tuesday, July 28, 2015 7:59 PM
  • Here is the contents of the table tblBMData.


    C vivid


    Hi. The query I gave you will list all words that haven't been learned by any student. Without the table of students, there is no way to determine the student number from tblBMData which students haven't learned which word yet because tblBMData will only have student numbers if they have learned a word already. So, if you're trying to figure out which words haven't been learned, you'll need to look at a different table than tblBMData. Hope that makes sense...
    Tuesday, July 28, 2015 8:01 PM
  • Yes you are correct. The stored words in tblBMData are the words that have been learned and I want to create a report from a query that tells me which words are yet to be learned. I have a table that just has student info called tblstudent. see below...


    C vivid

    Tuesday, July 28, 2015 8:05 PM
  • Okay, I can't test this right now, but I think it might go something like the following:

    SELECT SQ.StudentNum, SQ.Word
    FROM (SELECT tblStudent.StudentNum, tblWords.Word
    FROM tblStudent, tblWords) As SQ
    LEFT JOIN tblBMData
    ON SQ.Word=tblBMData.Word
    WHERE tblBMData.Word Is Null

    (untested)

    Hope that helps...

    Tuesday, July 28, 2015 8:12 PM
  • OK thanks. I'll try the above SQL and see what I come up with.

    C vivid

    Tuesday, July 28, 2015 8:23 PM
  • Yeah, please let us know since I am not sure if that will work without testing it. Good luck!
    Tuesday, July 28, 2015 8:46 PM
  • Sorry, My cable modem died so no internet access till today.

    I tried the SQL but couldn't make it work.


    C vivid

    Wednesday, July 29, 2015 10:32 PM
  • Hi. If you could email me a copy of your database, I could try it out. Just a thought...
    Wednesday, July 29, 2015 11:30 PM
  • I tried the SQL but couldn't make it work.

    What were the results?  Too much, too little, etc?

    Build a little, test a little


    Wednesday, July 29, 2015 11:33 PM
  • OK, I'll send you a copy of my database. I would appreciate you taking a look at it.

    Where do I send it?


    C vivid

    Thursday, July 30, 2015 3:04 AM
  • Try these 2 queries --

         qryStudentWord  --

    SELECT tblStudent.[Student#], tblWord.Word
    FROM tblStudent, tblWord;

    SELECT qryStudentWord.[Student#], qryStudentWord.Word
    FROM qryStudentWord LEFT JOIN tblBMData ON (qryStudentWord.[Student#] = tblBMData.StudentNum) AND (qryStudentWord.Word = tblBMData.Word)
    WHERE (((tblBMData.StudentNum) Is Null) AND ((tblBMData.Word) Is Null))
    ORDER BY qryStudentWord.[Student#], qryStudentWord.Word;


    Build a little, test a little

    • Marked as answer by L.HlModerator Tuesday, August 4, 2015 8:44 AM
    Thursday, July 30, 2015 4:06 AM