query off of a junction table

[jk42]

Hi All!

So I have a form that has a sub form on it (based off of a junction table), indicating which category a particular project belongs to. It can belong to multiple categories:

ProjectID
100
Category
Admin
Finance

But I want to run a query to find all of the projects that are both admin and finance. When I do that now, it comes up as two separate records in the query, which is fine... but I have to know the projects before hand to find if it's in both, because it returns heaps of records, anything that says admin or finance... and that defeats the whole purpose. I want the query to show me JUST the projects that have both of those in their subform..

Any help would be appreciated. I'm 99% sure my explanation is horrid, but I did the best I could!

 

[plog]

You didn't provide the necessary table or field names for a specific solution, so let me tell you how to do it in general:

1. Create a query based on your junction table. In design view, bring down 1 instance of the Project Id field and 2 instances of the Category field. Click the Sigma (aka Summation symbol) in the ribbon to make this an aggregate query. Underneath the Project ID and 1 instance of the Category data, make it say 'Group By'. Underneath the second instnace of the Category field, make it say 'Where' and underneath that put in your criteria ("Admin" OR "Finance").

2. Save that query as 'sub1'. Then make a query based on sub1. In design view, bring down the Project ID field and the Category field. Make this an aggregate query as well (click the Sigma/Summation symbol). Underneath the Project ID field make it say 'Group By' and underneath the Category field make it say 'Count'. Then underneath the same Category field, in the criteria section put this: >1

Run that query and you have all the Project Id numbers that have both Admin and Finance.

 

[Cronk]

JK42

You can also use two instances of tblProjectCategories, instead of tblCategories as suggested by plog.

 

[plog]

I never mentioned 2 instances of any tables. Nor did I refer to any specific table names.

 

[Cronk]

Sorry, misread in a hurry.

 

[jk42]

WOW! That is EXACTLY what I needed!! Thanks so much!! Seriously, that just made my Friday afternoon a little bit better! Thanks again!!!!!

 

[jk42]

If i wanted to add all of the pertinent details to that project, in addition to it's category, would that be easy to do? Would I have to do anything special? I just tried to use clicking the things I wanted from tblProjectdetails in that query of "sub1' and it did NOT work! It returned 34,000+records, so I KNOW I'm doing something wrong!

 

[jk42]

Sorry-let me be more specific. in addition to wanting to know which are "finance" and "admin" categories, there will also be all sorts of other project specific details that are not in that tbljunctionproject_categories. The details will come from tblprojectdetails. I hope that provides more clarity.

 

[jk42]

So sorry, one more question. If I wanted to add multiple things, like finance, admin, managerial, accounting, would I just need the category column to the original query for each item? And then would the >1 thing change as well? I just want to understand how to manipulate for other things.
Thanks!

 

[plog]

You've lost me. To unlose me, you're going to have to post sample data. Post sample data from all relevant tables (include table and field names) and then, based on that sample data; also post what the end result should be.

 

[jk42]

Darn! I don't know how I missed your response! So sorry I'm so slow. Ok, so I now beautifully know which things are "finance and admin" but I forgot that I also needed all the relevant data that goes along with the "finance and admin" projects.

Right now I have the Sub table that you helped me design that shows:

TblSub
ProjectID
Category

But I still want to be able to do what that "sub" table did, but with the details from
"TblProjectDetails"

So I want together the results of:
TBLSUB
(from tblSUB)ProjectID
(from tblSUB)Category

WITH items from TBlprojectdetails
Category
# employees
Countries
Date started
Etc.
etc.

Does this make more sense? basically I want to expand the details that the awesome sub table provided.

 

[plog]

First, I never said anything about creating a new table. I gave you instructions for creating a query and sub-query. Second, I'm going to need sample data. Post relevant data from your tables and then what you want to ultimately end up with using that sample data. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
12, David, 1/7/2011
44, Jennifer, 7/2/2003
17, Steve, 6/8/2009

 

[jk42]

Apologies, I just used the wrong word, I was talking about the queries.

TblProjectDetails
ProjectId, Category, Category 2, Countries, Type, #Employees, #Buildings,
100.001, Finance, admin, USA, Internal, 6, 5
100.002, finance, admin, USA, External, 8,2
100.003, finance, accounting, Mexico, External, 12, 1
100.004, finance, management, USA, Internal, 4, 1

What I ultimately want to end up with is the results of the Subquery that you helped me with above-All of the things that are finance and admin, but I also want to pull of it's accompanying details of Country, external, # employees, # buildings. And then depending on the person's request, they may want me to add some more fields from TBlprojectdetails, but the above ones are the "classic'" ones they ask for. Did I answer it better this time?

 

[plog]

That's half the battle. I also want what data you want to end up with. Not an explanation, the actual data you want the query to show based on that sample data.

 

[jk42]

100.001, Finance, admin, USA, Internal, 6, 5
100.002, finance, admin, USA, External, 8,2

 

[plog]

Maybe I'm missing something, but this query will provide you with what you want:

SELECT * FROM TblProjectDetails WHERE Category='Finance' AND [Category 2]='Admin';

 

[Cronk]

Maybe Admin and Finance can be stored in either Category field.

In which case the where clause is
WHERE (Category='Finance' AND [Category 2]='Admin') OR (Category='Admin' AND [Category 2]='Finance')

 

[Cronk]

Eh?????????????