Query help

[ndello]

I have a 2 column database
The columns are Hedge_No and GID
here is what it looks like
Hedge_No.......GID
BF239.............CLU
BF239.............Swap
BF240.............CLS
BF240.............Swap
etc on down

What I want to do is build a query that will show me if a Hedge_No is missing either the CLU/CLS or Swap. There are hundreds of Hedge_No's with other GID's added, but the minimum criteria is that they must have either a CLS or CLU coupled with a Swap.

Any help would be greatly appreciated.
Thanks
Neil

 

[Monkey]

If I understand you correctly this is what you want to do else let me know


SELECT YourTable.HEDGE_NO, YourTable.GID
FROM YourTable
GROUP BY YourTable.HEDGE_NO, YourTable.GID
HAVING (((YourTable.GID) Is Null));


Greetings Monkey

 

[bat17]

I think that you will need a series of queries to do this, the first four setting up the base data and then one to check for Hedge No with out a swap and the last checking for Hedge No with out either a CLS or CLU

QryUniqueHedge
SELECT YourTable.Hedge_No
FROM YourTable
GROUP BY YourTable.Hedge_No;

qrySwap
SELECT YourTable.Hedge_No, YourTable.GID
FROM YourTable
WHERE (((YourTable.GID)="Swap"));

qryCLS
SELECT YourTable.Hedge_No, YourTable.GID
FROM YourTable
WHERE (((YourTable.GID)="cls"));

qryCLU
SELECT YourTable.Hedge_No, YourTable.GID
FROM YourTable
WHERE (((YourTable.GID)="clu"));

QryFindNoSwap
SELECT QryUniqueHedge.Hedge_No, qrySwap.GID
FROM qrySwap RIGHT JOIN QryUniqueHedge ON qrySwap.Hedge_No = QryUniqueHedge.Hedge_No
WHERE (((qrySwap.GID) Is Null));

qryFindNoClsCluSELECT QryUniqueHedge.Hedge_No, qryCLU.GID, qryCLS.GID
FROM qryCLS RIGHT JOIN (QryUniqueHedge LEFT JOIN qryCLU ON QryUniqueHedge.Hedge_No = qryCLU.Hedge_No) ON qryCLS.Hedge_No = QryUniqueHedge.Hedge_No
WHERE (((qryCLU.GID) Is Null) AND ((qryCLS.GID) Is Null))


HTH

Peter

 

[ndello]

Thank You
I will look into this.