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!!
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Access 2010 DB Question - Filtering Reports with Multiple Variables/Fields
31st Jan
Answers (1)
1
Vote
JOINS, JOINS the musical fruit....
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:
So I believe what you are looking for, if I understood you, is the JOIN or INNER JOIN; something like:
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!
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:
SELECT *
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: http://www.w3schools.com/sql/sql_join.asp. You can create/enter this in the Query's SQL Editor mode.
Hope that helps!
Updated - 1st Feb
Replies
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!
lnnakamu
28th Feb
