Query is filtering - need help

[Maria83]

Hi Guys,

I'm trying to create a query that will pull data from several tables without filtering data from those tables at the same time, and I don't know how to do this. I'm working with a grant database that holds information of successful and unsuccessful applicants.

I have a table, tblAll Applicants, that holds ApplicantName, GrantName, ProjectTitle. This table holds this information for both successful and unsuccessful applicants, but there is no indication of which applicants are successful or not in this table. I have another table, tblAwarded Grants that hold information for only successful grants. Those two tables are linked by a junction table. (I've attached a screen shot of the relationships)

What I'd like the query to do is pull all the applicant data from tblAllApplicants (of both successful and unsuccessful applicants) and merge this with data from tblAwarded Grants, so that I end up with a query that displays all applicants and awardees are noted because those records have GrantNumbers associated with them.

right now, when I query these two tables the query filters out the unsuccessful applicants.

If someone can help me out on this, I'd really appreciate it.

 

[GinaWhipp]

Hmm, sounds like you query has an INNER join and you want a LEFT join? Have a look here...

Query Joins
http://www.access-diva.com/q5.html

It should help you see how tables pull information based on the JOIN type.

 

[Maria83]

Oh man... I was just using the Query Wizard...I guess I'm going to have to design the query from scratch. I'll read through that link and try to figure it out.

Thanks!

 

[GinaWhipp]

It's not that hard... you'll get it! The wizard is great for simple queries but when you want to start to get specific you really need to do it yourself.

 

[Maria83]

Okay, I'm confused. I tried to create the query from scratch and Access is telling me I need to use SQL? I don't know SQL and don't know where to write that code even if I was given the correct code. I also don't know how to fix my joins, because I have a junction table connecting my two tables.

Any suggestions?

I attached screen shots of the error message I got and of the query joins.

 

[GinaWhipp]

What's it is say is it cannot correctly display the data from those three tables, because you have LEFT and RIGHT joins. The joins, in this case need to go in one direction LEFT or RIGHT. So, play with the which way the little arrows are pointing. Understand?

 

[Maria83]

Awesome, got it to work! I didn't have to write any SQL code
thanks for the help

 

[GinaWhipp]

Great! No problem...