Re: query miscalculation

From: Alex (Alex_at_discussions.microsoft.com)
Date: 12/30/04 

Date: Wed, 29 Dec 2004 17:25:02 -0800

Thanks, John.
I did it but the same result: the sum of [PayrollWorkedTime] is 6,645.25
instead of 6,657.
I'll try to find which row gives this mistake.

"John Vinson" wrote:

> On Wed, 29 Dec 2004 16:31:03 -0800, "Alex"
> <Alex@discussions.microsoft.com> wrote:
>
> >AND ((tblAdjustedData.AdditionCode)<>"L" And
> >(tblAdjustedData.AdditionCode)<>"P" And (tblAdjustedData.AdditionCode)<>"W"));
>
> >I've checked the result of the query without WHERE in Excel, calculating "L"
> >and "P" separately there. Everything is correct. It should be 6,810.5 for
> >all, 65.5 for "L", and 88 for "P". But, with the criteria <>"L" And <> "P"
> >it's not working in Access.
>
> Well, if the code is P then it's certainly not equal to L; and if it's
> L, it's certainly not W. Therefore the clause above will return all
> records with non-NULL addition codes.
>
> Change that portion of the Where clause to
>
> AND tblAdjustedData.AdditionCode NOT IN("L", "P", "W")
>
>
> John W. Vinson[MVP]
>

Relevant Pages

  • Re: Ren Ci....Ming Yi..... perform charity works....they are no NKF!
    ... the mistake is not part of the clause yet or the clause of the regulations ... charity works for the benifit of the poor and destitute Singaporeans. ... Ren Ci and Ming Yi are doing. ...
    (soc.culture.singapore)
  • Re: Aggregate Functions "First" and "Last"
    ... You could have a subquery that used First to return a value, ... and then use that value in your Where clause, but you cannot use First the ... "John Vinson" wrote: ... I'll use Firstin a totals query where I know there will just be one ...
    (microsoft.public.access.gettingstarted)
  • Re: group by help please!
    ... i want would you please check where the mistake might be? ... from purinv, purch ... BY clause that is causing the problem. ...
    (comp.databases.oracle.server)
  • Re: Ordering a string numerically
    ... John Vinson wrote: ... ordered (in the Order By clause) the "0101A" comes before the "515A". ... you must enclose the fieldname in square ... brackets. ...
    (microsoft.public.access.queries)
  • Serious OLEDB Bug in DELETE Statements ?
    ... I've just spent a day trying to find a serious mistake in an OLEDB DELETE to an ACCESS Database. ... In spite of a quite detailed WHERE clause, the delete query deleted all records in the underlying table. ...
    (microsoft.public.data.oledb)