How to design a query to get unique IDs?

[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

 

[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];

 

[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:] )

 

[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)

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)

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

 

[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

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)

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)

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