I have a select query,
That does exactly as it say's and returns all records where boxno > 0. bayno = 0, shelfno = 0 and boxedcascollection = 'Gayler"
What I need is either bayno = 0 or shelfno = 0 while boxno > 0 and boxedascollection = "Gayler"
This query returns all entries where shelfno = 0 regardless of the status of boxno, bayno or boxedascollection.
I'm sure this must be do-able but the method escapes me.
Code:
SELECT Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection
FROM Main
GROUP BY Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection
HAVING (((Main.BoxNo)>0) AND ((Main.BayNo)=0) AND ((Main.ShelfNo)=0) AND ((Main.BoxedAsCollection)="Gayler"))
ORDER BY Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection;
That does exactly as it say's and returns all records where boxno > 0. bayno = 0, shelfno = 0 and boxedcascollection = 'Gayler"
What I need is either bayno = 0 or shelfno = 0 while boxno > 0 and boxedascollection = "Gayler"
Code:
SELECT Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection
FROM Main
GROUP BY Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection
HAVING (((Main.BoxNo)>0) AND ((Main.BayNo)=0) OR (((Main.ShelfNo)=0)) AND ((Main.BoxedAsCollection)="Gayler"))
ORDER BY Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection;
This query returns all entries where shelfno = 0 regardless of the status of boxno, bayno or boxedascollection.
I'm sure this must be do-able but the method escapes me.