How to design a query to get unique IDs?

A

Amit

Hi,

I have the following tables in my database:
1. tblOrg (OrgID*, OrgName)
2. tblProg (ProgID*, ProgName)
3. tblProgOrg (OrgID*, ProgID*)
since there is a many-to-many relationship between Org and Prog.

I need to get a list of OrgIDs for each ProgID where the OrgID is exclusive
to that ProgID. I will be entering the ProgID as parameter.

e.g.
OrgID ProgID
--------------
1, A
1, B
1, C
2, A
3, A
3, B
4, B
4, C
5, A
6, C

So, I need the following query result for ProgID = A: (2, 5) since those are
the two OrgIDs for A that do not occur with any other ProgID.
For ProgID = B: (null) and for ProgID = C: (6).

I've tried designing some queries using wizard, but without luck. Would
appreciate any help with this.

Thanks.

-Amit
 
C

ChrisJ

Try...

QUERY1
Select OrgId, Count(*) from tblProgOrg Group By OrgId Having Count(*) = 1;

Query2
Select tblProgOrg.* from tblProgOrg inner join query1 on
tblProgOrg.OrgId = query1.OrgId
Where ProgId = [Enter ProgID];
 
V

Van T. Dinh

I assume the *s in (OrgID, ProgID*) mean the combination (OrgID, ProgID)
is the PK (or at least uniquely ineded) then try something like:

SELECT PO.ProgID, PO.OrgID
FROM tblProgOrg
WHERE ( PO.OrgID In
( SELECT Sub.OrgID
FROM tblProgOrg AS Sub
GROUP BY OrgID
HAVING Count(*) = 1 ) )
AND ( PO.ProgID = [Enter Required ProgID:] )
 
V

Van T. Dinh

Sorry, I left out part of the SQL. Try:

SELECT PO.ProgID, PO.OrgID
FROM tblProgOrg AS PO
WHERE ( PO.OrgID In
( SELECT Sub.OrgID
FROM tblProgOrg AS Sub
GROUP BY OrgID
HAVING Count(*) = 1 ) )
AND ( PO.ProgID = [Enter Required ProgID:] )


--
HTH
Van T. Dinh
MVP (Access)


Van T. Dinh said:
I assume the *s in (OrgID, ProgID*) mean the combination (OrgID, ProgID)
is the PK (or at least uniquely ineded) then try something like:

SELECT PO.ProgID, PO.OrgID
FROM tblProgOrg
WHERE ( PO.OrgID In
( SELECT Sub.OrgID
FROM tblProgOrg AS Sub
GROUP BY OrgID
HAVING Count(*) = 1 ) )
AND ( PO.ProgID = [Enter Required ProgID:] )

--
HTH
Van T. Dinh
MVP (Access)



Amit said:
Hi,

I have the following tables in my database:
1. tblOrg (OrgID*, OrgName)
2. tblProg (ProgID*, ProgName)
3. tblProgOrg (OrgID*, ProgID*)
since there is a many-to-many relationship between Org and Prog.

I need to get a list of OrgIDs for each ProgID where the OrgID is exclusive
to that ProgID. I will be entering the ProgID as parameter.

e.g.
OrgID ProgID
--------------
1, A
1, B
1, C
2, A
3, A
3, B
4, B
4, C
5, A
6, C

So, I need the following query result for ProgID = A: (2, 5) since those are
the two OrgIDs for A that do not occur with any other ProgID.
For ProgID = B: (null) and for ProgID = C: (6).

I've tried designing some queries using wizard, but without luck. Would
appreciate any help with this.

Thanks.

-Amit
 
A

Amit

Hi Chris and Van,

Thanks for your responses and answers. Yes, that worked like a charm!!
I'd actually figured out how to get the results using 3 queries, but your
responses are even simpler using only one query.

Thanks and have a wonderful day!

-Amit

Van T. Dinh said:
Sorry, I left out part of the SQL. Try:

SELECT PO.ProgID, PO.OrgID
FROM tblProgOrg AS PO
WHERE ( PO.OrgID In
( SELECT Sub.OrgID
FROM tblProgOrg AS Sub
GROUP BY OrgID
HAVING Count(*) = 1 ) )
AND ( PO.ProgID = [Enter Required ProgID:] )


--
HTH
Van T. Dinh
MVP (Access)


Van T. Dinh said:
I assume the *s in (OrgID, ProgID*) mean the combination (OrgID, ProgID)
is the PK (or at least uniquely ineded) then try something like:

SELECT PO.ProgID, PO.OrgID
FROM tblProgOrg
WHERE ( PO.OrgID In
( SELECT Sub.OrgID
FROM tblProgOrg AS Sub
GROUP BY OrgID
HAVING Count(*) = 1 ) )
AND ( PO.ProgID = [Enter Required ProgID:] )

--
HTH
Van T. Dinh
MVP (Access)



Amit said:
Hi,

I have the following tables in my database:
1. tblOrg (OrgID*, OrgName)
2. tblProg (ProgID*, ProgName)
3. tblProgOrg (OrgID*, ProgID*)
since there is a many-to-many relationship between Org and Prog.

I need to get a list of OrgIDs for each ProgID where the OrgID is exclusive
to that ProgID. I will be entering the ProgID as parameter.

e.g.
OrgID ProgID
--------------
1, A
1, B
1, C
2, A
3, A
3, B
4, B
4, C
5, A
6, C

So, I need the following query result for ProgID = A: (2, 5) since those are
the two OrgIDs for A that do not occur with any other ProgID.
For ProgID = B: (null) and for ProgID = C: (6).

I've tried designing some queries using wizard, but without luck. Would
appreciate any help with this.

Thanks.

-Amit
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top