Software

Ask something
Question Locked

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

0 helpful
3 replies

by lnnakamu

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi all,

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

Table.MainInfo
PartID
Name
ContactData

Table.Projects
PartID
ProjectID
ProjectName

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!!

3 replies

by sven2157

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:
[ul][*][b]INNER JOIN( JOIN )[/b] - This returns rows when there is at least one match in both tables.
[*][b]LEFT JOIN[/b] - Return all rows from the left table, even if there are no matches in the right table.
[*][b]RIGHT JOIN[/b] - Return all rows from the right table, even if there are no matches in the left table.
[*][b]FULL JOIN[/b] - Return rows when there is a match in one of the tables.[/ul]

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

[quote]
SELECT *
FROM `Table.MainInfo`
JOIN `Table.Projects`
ON `Table.MainInfo.PartID` = `Table.Projects.PartID`
WHERE `Table.Projects.Project` = 'ProjSad'
[/quote]
This will then list ONLY the matches in BOTH tables, to ProjSad. You can find more information here: http://www.w3schools.com/sql/sql_join.asp. You can create/enter this in the Query's SQL Editor mode.

Hope that helps! ;-)

by lnnakamu

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!

This discussion is closed to new replies.