Query help

ndello

Active Member
Joined
Oct 16, 2002
Messages
382
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Monkey

Active Member
Joined
Feb 10, 2003
Messages
251
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

Well-known Member
Joined
Aug 15, 2003
Messages
1,470
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,182
Messages
5,594,727
Members
413,927
Latest member
PolAmer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top