Access 2010 DB Question - Filtering Reports with Multiple Variables/Fields

By lnnakamu ·
Hi all,

I'm hoping that someone will be able to help me. I have an Access 2010 db -



Right now, my queries are looking like - Name, Project
Leanne - ProjHappy
Leanne - ProjSad
Aaron - ProjSmiles
Linda - (Null)

Running in the report is easy, it all groups it. However, if I want to filter out those who have done ProjSad, it only filters the project. So it looks like:
Leanne - ProjHappy
Aaron - ProjSmiles
Linda - (Null)

How do I filter out those who have done the project totally? So basically, my end query would look like (filtering out all those who have done ProjSad):
Aaron - ProjSmiles
Linda - (Null)

I know the answer should be right in front of my brain, but I've been staring at this way too long. I've tried grouping - but since it's text it's not working, I've tried pivotcharts but it's not as userfriendly as a report (and there are about 313 projects and 22K participants).

Thank you for your help!!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

JOINS, JOINS the musical fruit....

by Sven2157 In reply to Access 2010 DB Question - ...

You are looking for JOINS. In your example, you have the 'MainInfo' list first; the result lists the contents of 'MainInfo' first, as well. I would consider this the 'LEFT' Table. Consequently, the Projects Table is the RIGHT table.

There are 4 kinds of JOIN:

  • INNER JOIN( JOIN ) - This returns rows when there is at least one match in both tables.
  • LEFT JOIN - Return all rows from the left table, even if there are no matches in the right table.
  • RIGHT JOIN - Return all rows from the right table, even if there are no matches in the left table.
  • FULL JOIN - Return rows when there is a match in one of the tables.

So I believe what you are looking for, if I understood you, is the JOIN or INNER JOIN; something like:

FROM `Table.MainInfo`
JOIN `Table.Projects`
ON `Table.MainInfo.PartID` = `Table.Projects.PartID`
WHERE `Table.Projects.Project` = 'ProjSad'

This will then list ONLY the matches in BOTH tables, to ProjSad. You can find more information here: You can create/enter this in the Query's SQL Editor mode.

Hope that helps! ;-)
Collapse -

Reponse To Answer

by lnnakamu In reply to JOINS, JOINS the musical ...

Thank you for answering! My alert went straight into junk mail so I didn't get to see this solution until now. Such a simple idea - I both hate and love when that happens! Thank you!

Related Discussions

Related Forums