adding criteria to a query (1 Viewer)

sparkanother

New member
Local time
Today, 15:02
Joined
Mar 24, 2014
Messages
5
[SOLVED] adding criteria to a query

Hi All,

I have one datatable and i have to list the duplicates from it based on different criterias. It contains invoices, we would like to avoid duplicated payments.
I'm struggling with a query. Basically i have a working one, i have to add one more criteria to filter out a couple of rows where the text column is "freight", but anyhow i have tried, it's not working. Without that one line where the not equal is, it's working as it should, when i'm adding that line it ruturns the same as before, but it should give me 108 less lines. The below one is based on an other query result called Duplicates_Rule1.

Here is the query:
Code:
SELECT * FROM Duplicates_Rule1
WHERE (((Duplicates_Rule1.BSEG_DocumentNo) In (SELECT [BSEG_DocumentNo] FROM [Duplicates_Rule1] As Tmp 
GROUP BY [BSEG_DocumentNo] 
HAVING Count(*)=1 )) 
AND (Duplicates_Rule1.Check) Is Null) 
and (Duplicates_Rule1.BSAK_Text <> "FREIGHT")
ORDER BY Duplicates_Rule1.BSEG_DocumentNo;

Any help is appriciated!
Rgds,
Adam
 

Attachments

  • tableNQueries.JPG
    tableNQueries.JPG
    62 KB · Views: 88
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:02
Joined
Jan 23, 2006
Messages
15,362
It would be helpful to readers if there was some contest for your question. For example:

What is the "subject matter" of the data base?
Can you give a 3-4 line overview of the "business issue/opportunity" involved?
Can you post a jpg of your tables and relationships as a zip file?
 

sparkanother

New member
Local time
Today, 15:02
Joined
Mar 24, 2014
Messages
5
I have edited it a bit, hope it makes more sense now
 

Brianwarnock

Retired
Local time
Today, 14:02
Joined
Jun 2, 2003
Messages
12,701
Check your brackets I think the ) after the Null should be moved to after "freight")

Brian
 

sparkanother

New member
Local time
Today, 15:02
Joined
Mar 24, 2014
Messages
5
I have just tested it, but unfortunately this is not the problem. My guess is that the problem is with the "<>" sign, now it's giving me more less rows than it should. I have to exclude that exact word and nothing else, but now it's filtering a lot more :(
 

Brianwarnock

Retired
Local time
Today, 14:02
Joined
Jun 2, 2003
Messages
12,701
I don't know what you tried but the bracketing here

AND (Duplicates_Rule1.Check) Is Null)

makes no sense unless the last ) was to finish the Where clause, probably in your original query, you could remove the ) after check and code

AND (Duplicates_Rule1.Check Is Null)
and (Duplicates_Rule1.BSAK_Text <> "FREIGHT"))

Brian
 

sparkanother

New member
Local time
Today, 15:02
Joined
Mar 24, 2014
Messages
5
First of all thanks for the help! Yep, the brackets were weird. I have amended them the way you suggested, but it's all the same. The strange thing is that if i change the 'not equal' sign to equal, it gives me 108 rows, but when i'm using the not equal, not just that 108 rows are missing. I really don't get it. :)
 

Brianwarnock

Retired
Local time
Today, 14:02
Joined
Jun 2, 2003
Messages
12,701
What is the starting point of your arithmetic, is it the file before any criteria is applied or after the Is Null , eg 500 records with Is Null , then add = "FREIGHT" and get 108, then I don't get it either, but if before the Is Null is applied then it
is entirely possible a record with Not Is Null and "PASSENGER" for example will not be selected in either situation.

Brian
 
Last edited:

sparkanother

New member
Local time
Today, 15:02
Joined
Mar 24, 2014
Messages
5
Thanks a lot for the effort Brian, i have finally figured it out. There were a lot of empty cells in the text column, they were mixing up the result. This is the working one:

AND (Duplicates_Rule1.Check Is Null)) and (Duplicates_Rule1.BSAK_Text Is Null or Duplicates_Rule1.BSAK_Text <> "FREIGHT")
 

Users who are viewing this thread

Top Bottom