This must be a bug!?

[Les Isaacs]

Hi All
I'm no expert, but I think I've found a bug in access's query builder
I have a query with 4 rows of criteria, with the same value in 3 of the fields (these 4 criterion must be satisfies in every case), and varying values in values on one row only in 4 other fields: so the 3 criteria must be satisfied in every case, plus any 1 of the 3 criteria.
One of the 4 'must be satisfied' criteria is for a numerical field to be >0, so I set >0 on each of the 4 rows. I save the query. I save the query again. I close the query. I open the query and hey presto, my 4 '>0' criterion are now all on one row - i.e. >0 or >0 or >0 or >0
This is obviously not going to return the same (correct) results as I get with the '>0' set on each of the 4 rows.
Is it a bug?
Am I the first to identify this?
Is there a prize?
Hope someone can help (and probably make me feel foolish )
In casse anyone wants/needs to see the query, the sql is below.
Thanks
Les

SELECT Sum([x confirmed_local].[nhsp ees]) AS [SumOfnhsp ees], staffs.staff_name, practices.[prac name], Format([joined nhpps],"ddmmyyyy") AS [Date of Commencement], staffs.[joined nhpps], staffs.[SA number] AS [SD NO], staffs.[NI number] AS [NI No], staffs.NHSP_updated_to, inyearstarter("",[staffs].[staff_name]) AS Starter
FROM (practices INNER JOIN staffs ON practices.[prac name] = staffs.practice) INNER JOIN (months INNER JOIN [x confirmed_local] ON months.[month name] = [x confirmed_local].[month name]) ON staffs.staff_name = [x confirmed_local].staff_name
WHERE (((Format([joined nhpps],"ddmmyyyy")) Is Null) AND ((months.year)=[Forms]![frm x main]![year]) AND ((staffs.[leaving date]) Is Null Or (staffs.[leaving date])>CDate("31/3/" & Right([forms]![frm x main]![year],2))) AND ((staffs.nhsp_leaving_date) Is Null Or (staffs.nhsp_leaving_date)>CDate("31/3/" & Right([forms]![frm x main]![year],2))) AND ((staffs.NHSP_updated_to)<CDate("31 " & [forms]![frm x main]![month name]))) OR (((months.year)=[Forms]![frm x main]![year]) AND ((staffs.[leaving date]) Is Null Or (staffs.[leaving date])>CDate("31/3/" & Right([forms]![frm x main]![year],2))) AND ((staffs.nhsp_leaving_date) Is Null Or (staffs.nhsp_leaving_date)>CDate("31/3/" & Right([forms]![frm x main]![year],2))) AND ((staffs.NHSP_updated_to)<CDate("31 " & [forms]![frm x main]![month name])) AND ((staffs.[SA number]) Is Null)) OR (((months.year)=[Forms]![frm x main]![year]) AND ((staffs.[leaving date]) Is Null Or (staffs.[leaving date])>CDate("31/3/" & Right([forms]![frm x main]![year],2))) AND ((staffs.nhsp_leaving_date) Is Null Or (staffs.nhsp_leaving_date)>CDate("31/3/" & Right([forms]![frm x main]![year],2))) AND ((staffs.NHSP_updated_to)<CDate("31 " & [forms]![frm x main]![month name])) AND ((staffs.[NI number]) Is Null)) OR (((months.year)=[Forms]![frm x main]![year]) AND ((staffs.[leaving date]) Is Null Or (staffs.[leaving date])>CDate("31/3/" & Right([forms]![frm x main]![year],2))) AND ((staffs.nhsp_leaving_date) Is Null Or (staffs.nhsp_leaving_date)>CDate("31/3/" & Right([forms]![frm x main]![year],2))) AND ((staffs.NHSP_updated_to) Is Null))
GROUP BY staffs.staff_name, practices.[prac name], Format([joined nhpps],"ddmmyyyy"), staffs.[joined nhpps], staffs.[SA number], staffs.[NI number], staffs.NHSP_updated_to, inyearstarter("",[staffs].[staff_name])
HAVING (((Sum([x confirmed_local].[nhsp ees]))>0)) OR (((Sum([x confirmed_local].[nhsp ees]))>0)) OR (((Sum([x confirmed_local].[nhsp ees]))>0)) OR (((Sum([x confirmed_local].[nhsp ees]))>0));

 

[pr2-eugin]

How about making the query readable? So people who want to help will actually be interested in looking at it?

 

[Les Isaacs]

Hi pr2-eugin
I did realise that the sql wasn't pretty, which is why I described the problem quite fully in words - thinking that this would be sufficient to get across what is happening. The actual combinations of criterion are quite complex, which is why the sql is how it is: how can I make it readable? The critical bit (with the '>0' criterion) is all fairly clear at the end of the sql anyway!
Grateful for any advice.
Les

 

[spikepl]

You have confused yourself. The >0 criterion for the sum must be satisfied under all circumstances, according to your narrative, and that is also what the SQL says, according to the sql you show.

 

[Les Isaacs]

Hi Peter - nice to hear from you!
Thanks for your input. It did occur to me that the sql was consistent with the criteria as I originally entered it, but what seem wrong - and very offputting - is that the design grid view of the query changes to something else after closing/reopening the query (i.e. the nonesense of >0 or >0 or >0 or >0 all showing on a single row, and then nothing showing for this field on the other rows). I guess I could manufacture some date to establish whether the query results reflect the design view of the criteria or the sql view! Either way, I think there's an access problem here?
Thanks again
Les

 

[spikepl]

I would chalk this up to the gremlins of Redmond and move on with my life. The QBE can at times commit much worse rewrites ...

The QBE's rection implies that you cannot conditionally mix WHERE and HAVING, and there might be some SQL logic to this that I cannot fathom off hand, being an SQL-amateur.

Let the wise ones cast some cracks or stones into this thread ... (in the spirit of the moment)

 

[Brianwarnock]

Hi pr2-eugin
I did realise that the sql wasn't pretty, which is why I described the problem quite fully in words - thinking that this would be sufficient to get across what is happening. The actual combinations of criterion are quite complex, which is why the sql is how it is: how can I make it readable? The critical bit (with the '>0' criterion) is all fairly clear at the end of the sql anyway!
Grateful for any advice.
Les

Use code tags and break each criterion onto a new line

Brian

 

[Brianwarnock]

If in the design grid you code multiple criteria in one column on separate rows the System will combine them onto one row using the necessary Or function if it can. It obviously does not know that you have coded the same thing four times, this has arisen because you are applying all of the other criteria before aggregation and t his test post aggregation, nothing illogical in that.
Delete three of the tests and see what happens if it interests you.

Brian