Pulling out records from one table that isn't present in another

[breadcrumbtrail]

First of all, Hi! I'm new here, and I'm a complete Access n00b, so I apologise for that in advance.

OK, here's my situation. I've been assigned to design a question and answer helpdesk forum in access, and I think I've got most of the background done OK, there's just a few little things that I can't get my head round. Like one for instance. I have 2 tables, "Questions" and "Answers". I'm trying to get a query or a report (i'm not actually sure which one is which to be bluntly honest...) to show all questions that haven't been answered yet. In the Questions table, I have a Yes/No checkbox "Answered" to specify whether someone has come along to answer that question already, so I'm guessing the query would have something to do with that. I have defined the relationships, and the "Question ID" Primary key is also in the "Answered" table.

Sorry if I haven't explained this very well, but I'd really appreciate any help and/or suggestions!

Many thanks,

- John

 

[dcx693]

You can use a very simple query to tell you which questions have not yet been answered. (The "Answered" field that you already have is useful, but at best redundant, and at worst just wrong. There's no need for it since it can be calculated dynamically.)

Just join the Question table to the Answer table on the primary key field. Just look for questions with no answers. I.e., look for answers that are equal to Null. Use Null as the criteria for the field from the Answer table.

 

[neileg]

dcx693 is right but you can use the unmatched records query wizard to build this for you.

 

[breadcrumbtrail]

You can use a very simple query to tell you which questions have not yet been answered. (The "Answered" field that you already have is useful, but at best redundant, and at worst just wrong. There's no need for it since it can be calculated dynamically.)

Just join the Question table to the Answer table on the primary key field. Just look for questions with no answers. I.e., look for answers that are equal to Null. Use Null as the criteria for the field from the Answer table.

Hi, thanks for your reply!

I'm finding it a bit difficult to understand the last paragraph, i'm not too fluent in Access. Which field should I search for for answers equal to null? In the Questions table, I have the fields 'ID', 'Product ID' and 'answered', and in the answers table, I have 'ID' and 'Question ID' (which I have defined a relationship to ID from the Questions table). Sorry to not understand, I really appreciate your reply though!

Many Thanks,

- John

 

[breadcrumbtrail]

dcx693 is right but you can use the unmatched records query wizard to build this for you.

Hi,

Yeah, I searched for some help on this subject on google and that wizard was mentioned, but it doesn't appear to be installed on my version of Office (which is 2000 by the way) and I have no idea where my original disk has gone! Thanks for the suggestion though.

- John

 

[neileg]

In the Query window select New and the wizards are there.

 

[breadcrumbtrail]

In the Query window select New and the wizards are there.

Yes, but when I try to run the query, it says it needs the Office CD as the component is not installed. So i'm really looking to do it manually, even though it is harder and I don't really understand it! Thanks for your help anyway.

- John

 

[neileg]

Like dcx693 I assume that if there is no answer, then there is no record in the answer table, and that there is a left join between the tables ( all records from questions and those records from answers that match).

Create a new query, add both tables to the query grid. Drag the ID field from the question table to the first colum and the ID field from the answer table to the second. In the criteria row of the second column type Is Null. Run the query. This will give you the ID of any question that does not have a corresponding answer.

 

[breadcrumbtrail]

Like dcx693 I assume that if there is no answer, then there is no record in the answer table, and that there is a left join between the tables ( all records from questions and those records from answers that match).

Create a new query, add both tables to the query grid. Drag the ID field from the question table to the first colum and the ID field from the answer table to the second. In the criteria row of the second column type Is Null. Run the query. This will give you the ID of any question that does not have a corresponding answer.

Hi, thanks for your help.

When I do this, and run the query, it simply presents me with the response (autonumber) in both columns, and that is it. The tables are populated with answered and unanswered questions, so there should be at least one record being shown. The answer ID that you suggested I specify with the criteria Is Null, is that correct? Because from my understanding this is just an auto-assigned number every time a new answer is placed and hence would never be = null. Thats why I introduced the 'answered' field, but when I specify that with the criteria "Is Null", only one record is returned when I know that there should be more. Sorry for all the trouble, it seems like an easy question but its really troubling me!

Many Thanks,

- John

 

[neileg]

You said in your first post that the Question ID was also in the Answer table. So if the question has not been answered, there won't be a record in the answer table with that question ID. It is the Question ID from both tables that should be in your query.

 

[breadcrumbtrail]

You said in your first post that the Question ID was also in the Answer table. So if the question has not been answered, there won't be a record in the answer table with that question ID. It is the Question ID from both tables that should be in your query.

Ah ok, I see. But still, when I try this out, no records are returned. I'm still not understanding how Question ID in the Answer table could be null. Sorry, I am a complete newbie when it comes to access, I really appreciate your help.

Many Thanks,

- John

 

[dcx693]

Ah ok, I see. But still, when I try this out, no records are returned. I'm still not understanding how Question ID in the Answer table could be null.

If you have a question in the Question table for a certain question ID, then would you always have the same question ID in the Answer table? How does it get there?

 

[neileg]

Ah ok, I see. But still, when I try this out, no records are returned. I'm still not understanding how Question ID in the Answer table could be null. Sorry, I am a complete newbie when it comes to access, I really appreciate your help.

It's not that the field is null but that there is no record and so the query result is null.

If you recall, I said

I assume that if there is no answer, then there is no record in the answer table

The only way the the query I described can return no records is if you have created a record for the answer of every question. Have you, even if the record is blank?

 

[neileg]

Here's an example.

 

[breadcrumbtrail]

It's not that the field is null but that there is no record and so the query result is null.

If you recall, I said The only way the the query I described can return no records is if you have created a record for the answer of every question. Have you, even if the record is blank?

I've looked in the tables, and there are currently 3 questions, 1 unanswered. If I expand the unanswered question, the relationship to the answer table is displayed, with every field empty apart from Answer ID which has (AutoNumber). Would this count as a result when running the query?

I'm going to attempt to upload my database to this message - i've not done this before so if it goes wrong I apologise! I've cut out all the other features which aren't related to this problem, just put the 2 relevant tables and the query. Thanks again for your help,

- John

 

[neileg]

I've look at your db and it's exactly the same as my sample, except that you have the wrong join between the tables. Remember I said

there is a left join between the tables ( all records from questions and those records from answers that match).

You have an inner join which will only ever return records if they exist in both tables. Change this to a left join as explained and it works.

Originally Posted by breadcrumbtrail
If I expand the unanswered question, the relationship to the answer table is displayed, with every field empty apart from Answer ID which has (AutoNumber).

Don't be fooled. This feature in A2k onwards is simply a select query built in to the table view, it's not intrinsic to your data. The result we are looking for here is no result.

 

[breadcrumbtrail]

You have an inner join which will only ever return records if they exist in both tables. Change this to a left join as explained and it works.

Hey, thanks for your reply again, sorry about the delay in getting back.

Thank you very much for your assistance. Its now finally working!

Many Thanks

- John