Solved Query query. (1 Viewer)

[John Sh]

I have a select query,

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"

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.

 

[arnelgp]

you may try:

SELECT Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection
FROM Main
Where (Main.BayNo = 0 Or Main.ShelfNo = 0) And Main.BoxNo > 0 And Main.BoxedAsCollection="Gayler"
GROUP BY Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection
ORDER BY Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection;

 

[John Sh]

you may try:

SELECT Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection
FROM Main
Where (Main.BayNo = 0 Or Main.ShelfNo = 0) And Main.BoxNo > 0 And Main.BoxedAsCollection="Gayler"
GROUP BY Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection
ORDER BY Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo, Main.BoxedAsCollection;

Not being overly familiar with SQL, I thought something like this might be the answer and it is.
The result in design view repeats the >0 in the "or" row of boxno. Puts 0 in the "or" row of shelfno and repeats "Gaynor" in the "or" row of boxedas...
It all looks a bit odd but makes sense when you read it carefully.
I thank you for your expertise and willingness to share.
John

 

[ebs17]

BTW: Why are you grouping across all fields that are present in the SELECT part? Is your table full of duplicates?
If not, get rid of this part of the statement, which only eats up performance and worsens the overview, but brings no functional benefit.

It all looks a bit odd

Yes, the logic is more readable and representable in SQL view than in Design view.

 

[John Sh]

All that is good but there is another problem.
When I open the form "NoBay" and select a family and infrafamily from their respective dropdowns the "Collection" dropdown is populated but includes invalid entries for my purpose.
When I open the form "NoBay2" and select a family and infrafamily from their respective dropdowns the "Collection" dropdown is empty.

If I now open the query "qNoBayCollectH" in design mode and select "view" with "Fabaceae" as the family and "faboideae" as the infrafamily, the result only includes entries with "faboideae" as the infrafamily, this is what is required.
Doing the same with "qNoBayCollectH2" returns a list including entries with all 3 infrafamilies. Not what I want.

The behaviour of both queries in "View" is correct, as is the behaviour in frm "NoBay2", albeit not what is required.

Question: why do these entries not show in the "Collections" dropdown in form "NoBay"

Before anyone complains about the "non-normalised" table, I am bound by international standards that dictate each record must be complete in and of itself. This requirement cancels out the ability to normalise the table.

It should also be pointed out that this form is used with a range of tables hence the lengthy "onload" event and the commented out sections.
I have not included functionality past the "Collections" combobox.

 

[John Sh]

There turned out to be two problems.
1) I had named a control txtinfra instead of cboInfra in the QNoBayCollect query and
2) in that same query in the infrafamily column I had to wrap both instances of the criteria as "nz([form]![NoBay]![infrafamily],"").