[ASP] Query with DB Access

[Mike1961]

Hi everyone.

I have this query ASP and Db Access:

strSql = "SELECT Last(ID) AS ID, " &_
          "A, " &_
          "B, " &_
          "C, " &_
          "COUNT(D) AS TotD, " &_
          "Last(D) AS D, "&_
          "Last(E) AS E, " &_
          "Last(date) AS date, "&_
          "Last(tot) AS tot, "&_
          "Last(MonthName) AS MonthName, "&_
          "Last(IDMonth) AS IDMonth "&_
          "FROM tbl "&_
               "WHERE " &_         
          "(" &_
          "(" &_
          "(IDMonth = 12) OR " &_
          "(IDMonth = 1) OR " &_
          "(IDMonth = 2) OR " &_
          "(IDMonth = 3) " &_
          ") " &_
          "AND " &_
           "(DATE > #2007/12/01#) " &_
          "AND " &_
          "(tot = 0) " &_
          "OR " &_
          "(EX = 'KO') " &_
          "AND " &_
          "(D NOT IN ('SA')) " &_
          "AND " &_
          "(" &_
          "(E = 'NT') " &_
          ")) " &_          
          "GROUP BY D, A, B, C "&_
           "HAVING " &_
           "((COUNT(D)) = 5) " &_
          "ORDER BY ID asc"

The query no error, but extract from the table access all values in the field "E".

Extraction should value equal to E = 'NT'

Can you help me ?

[DrBunchman]

Hi mike1961,

The problem is your OR condition. You need to be careful when using an OR to surround the conditions with brackets. For example:

WHERE A = 1 AND B = 2 AND (C = 3 OR C = 4)

Will return different results to

WHERE A = 1 AND B = 2 AND C = 3 OR C = 4

Let me know how you get on.

Dr B

[Mike1961]

Thanks moderator for you reply.

I have this response.write query:

[php]
SELECT Last(ID) AS ID, A, B, C, COUNT(D) AS TotD, Last(E) AS E, Last(F) AS F, Last(date) AS date, Last(tot) AS tot, Last(MonthName) AS MonthName, Last(IDMonth) AS IDMonth FROM tbl WHERE (((IDMonth = 12) OR (IDMonth = 1) OR (Month = 2) OR (IDMonth = 3) ) AND (DATE > #2007/12/01#) AND (tot = 0) AND (EX = 'KO') AND (D NOT IN ('SA')) AND ((E = 'NT') )) GROUP BY D, A, B, C HAVING ((COUNT(D)) = 5) ORDER BY ID asc
[/php]

In th DB Access I have rows whit EX = 'KO' AND E = 'NT' but this query not extract this rows...

[DrBunchman]

Have you got Query Analyser or something similar you can run this query in to test it? If so, do that and comment out your conditions one by one until you get the data returned you are expecting. That way you'll see where the problem is.

I would hazard a guess that the problem is the AND (D NOT IN ('SA')) line so start with that.

Let me know how it goes,

Dr B

[Mike1961]

Nothing, not working... :(

[php]
SELECT Last(ID) AS ID, A, B, COUNT(D) AS TotD, Last(E) AS E, Last(F) AS F, Last(date) AS date, Last(tot) AS tot, Last(EX) AS EX, Last(MonthName) AS MonthName, Last(IDMonth) AS IDMonth FROM tbl WHERE (((IDMonth = 12) OR (IDMonth = 1) OR (Month = 2) OR (IDMonth = 3) ) AND (DATE > #2007/12/01#) AND (tot = 0) AND (EX = 'KO') AND ((E = 'NT') )) GROUP BY D, A, B HAVING ((COUNT(D)) = 4) ORDER BY ID asc
[/php]

[DrBunchman]

Comment out all your conditions.....presumably the data you are expecting to see will display now?

Then start putting the conditions back in one by one and see which one is preventing your data from displaying.

Dr B

[Mike1961]

Comment out all your conditions.....presumably the data you are expecting to see will display now?

Then start putting the conditions back in one by one and see which one is preventing your data from displaying.

Dr B

OK.

I try this query in the DB Access.

Query working, but I would like see the field "EX"; now no see the field "EX".

[php]

SELECT tbl.A, tbl.B, tbl.C, Count(tbl.D) AS TotD
FROM tbl
WHERE (((tbl.IDMonth)=12 Or (tbl.IDMonth)=1 Or (tbl.IDMonth)=2 Or (tbl.IDMonth)=3) AND ((tbl.DATE)>#12/1/2007#) AND ((tbl.TOT)=0) AND ((tbl.E)='NT'))
GROUP BY tbl.A, tbl.B, tbl.C
HAVING (((Count(tbl.D))=4))
ORDER BY tbl.A;

[/php]

[Mike1961]

Nothing?

Help me please....

[DrBunchman]

I'm not sure what you mean......if you want to see the field EX then you need to include it in your SELECT statement e.g. SELECT EX

Or am I missing something here?

Dr B

[Mike1961]

I'm not sure what you mean......if you want to see the field EX then you need to include it in your SELECT statement e.g. SELECT EX

Or am I missing something here?

Dr B

But if I include in the SELECt statement the field EX, I have:

[php]

SELECT tbl.A, tbl.B, tbl.C, tbl.EX, Count(tbl.D) AS TotD
FROM tbl
WHERE (((tbl.IDMonth)=12 Or (tbl.IDMonth)=1 Or (tbl.IDMonth)=2 Or (tbl.IDMonth)=3) AND ((tbl.DATE)>#12/1/2007#) AND ((tbl.TOT)=0) AND ((tbl.E)='NT'))
GROUP BY tbl.A, tbl.B, tbl.C, tbl.EX
HAVING (((Count(tbl.D))=4))
ORDER BY tbl.A;

[/php]

The result of query is 109 rows and not see the field EX; without field EX the result of query is 126 rows...

which is the right result ?

[DrBunchman]

Is EX null in some of your rows?

[Mike1961]

Is EX null in some of your rows?

Yes, the field EX is always null...

[DrBunchman]

It's always null? If it never contains any data then how come you want to SELECT it?

[Mike1961]

It's always null? If it never contains any data then how come you want to SELECT it?

In the DB access is not NULL... this query write NULL...