same query, same data, same machine, sometimes missing one row returned

[Don Maclean]

I have a query that correctly returns 149 rows. I immediately run it
again and get 148 rows.
About 2/3 of the time, one row is missing - but its different row each
time. A couple of times, more than one row was missing. The underlying
table remains the same and I am the only user when.
How can this happen?

SELECT e.[Updated WBS],
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70422000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70422000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70423000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70423000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70600000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70600000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77051000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77051000Direct,
Sum(e.[US$ AMOUNT]) AS SumOfDirect
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

The full query has more subquerys of exactly the same form.

I'm utterly baffled by this behaviour anyone know what could be causing
this?

Thanks.

 

[Gary Walter]

I have a query that correctly returns 149 rows. I immediately run it
again and get 148 rows.
About 2/3 of the time, one row is missing - but its different row each
time. A couple of times, more than one row was missing. The underlying
table remains the same and I am the only user when.
How can this happen?

SELECT e.[Updated WBS],
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70422000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70422000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70423000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70423000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70600000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70600000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77051000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77051000Direct,
Sum(e.[US$ AMOUNT]) AS SumOfDirect
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

The full query has more subquerys of exactly the same form.

I would imagine the first thing I would do is import
the table into a new blank db, then start with simplest
query

SELECT e.[Updated WBS]
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

Does this return different results?

If so, I might next remove all indexing on table
(or, at least, any index on "[Updated WBS]"
if it exists).

Does this help? If so, the next thing I might do
is go to Microsoft Office website

http://office.microsoft.com/en-us/downloads/FX101321101033.aspx

and click on

"Check for Microsoft Updates"

(in fact, I might suggest doing this first thing
no matter what)

If removing the index did not help (nor checking
for updates) , then I might change our simple query
to a make table query, then run it several times
changing the INTO table name each time.
Actually, I'd probably just save several such
make table queries with different table names,
then I can repeat as often as needed.

Then run unmatched query on these tables to see *exactly*
which values are appearing/disappearing. I know getting
different results must be unnerving, but it must lie in these
data points (maybe they don't get along with LIKE possibly).

From the subqueries you have provided, I do not
see why your criteria could not be moved to the
WHERE clause.

SELECT e.[Updated WBS]
FROM Expenses AS e
WHERE e.[Updated WBS] Not Like "888*"
GROUP BY e.[Updated WBS];

I have no reason to suspect this will change your
results, but if some subquery was somehow affecting
results, this will filter before the subqueries are run....

To rule out LIKE playing some part in this, you could
use the LEFT function in your criteria....

SELECT e.[Updated WBS]
FROM Expenses AS e
WHERE LEFT(e.[Updated WBS], 3) <> "888"
GROUP BY e.[Updated WBS];

As constructed, this will *not* return a group where
[Updated WBS] is NULL, just so you know...

?LEFT(Null,3)<>"abc"
Null <--- which is not true, so "no return"
?LEFT("",3)<>"abc"
True
?LEFT("abcde",3)<>"abc"
False
?LEFT("ab",3)<>"abc"
True

If, somewhere along the process above, you do
get consistent results, then I might add my subqueries
one by one and test results. I have no reason to suspect
them....just to be sure....

I'm sorry I cannot be more specific.

I would suspect....

-- Access version
-- index(es)
-- specific data points

but I cannot truly be more specific...

 

[Don Maclean]

Thanks for the suggestions.

I was working with a fully-updated version of access 2003. Today I'm
using an older build and getting the same results. I tried copying the
table to a new database and re-creating the query, with the same
results.
The main query alone consistently returns all 149 rows.
Removing / rebuilding the indices seems to make no difference.
Moving the criteria to the Where clause and/or replacing Like with the
Left function seems to make no difference.
I also tried making a table with just all 149 values of [Updated WBS]
and joining my query to it in an attempt to force the return of all 149
rows. This did not work either.

The very first thig I did was to look at which rows were missing but i
couldn't see any explanation there.
The values of [Updated WBS] range from 413 to 661

For one series of runs, the missing [Updated WBS] values were:
527, 485, 482, 498, 497, 485, 487, 529, 503

The row numbers for these missing rows were:
48, 24, 23, 33, 32, 24, 26, 49, 39

During this series, about 3 trials returned all 149 rows.

The missing rows do seem to cluster around a quarter to a third into
the list but, other than that, I don't see anything notable in the
missing rows or data. More to the point, I can't even imagine anything
that I could possibly do to my data that should produce results that
vary in this manner.

Now, for the good news, I have found that adding DISTINCT to the main
query causes it to (so far) reliably return all 149 rows. I tried this
in a spirit of desperate experimentation and I don't understand why it
should change the results.

So, I suppose my query is fixed, but I no longer trust my database and
I have a great many queries more complex than this one. I would still
like to understand what's happening here and I would still appreciate
any explanation or suggestions.

 

[Gary Walter]

I'm not sure what you mean by:

"The main query alone consistently returns all 149 rows."

Does that mean the simple group by query without the subqueries?

SELECT e.[Updated WBS]
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

So it is the subqueries/aggregation that may be throwing a
wrench in the works?

"About 2/3 of the time, one row is missing - but its different row each
time. A couple of times, more than one row was missing."

Or that adding DISTINCT to above query was what
caused "main query" to produce consistent results?

What field type is [Updated WBS]? Long, Text?

Did you try running update query that TRIM's this
field? Or query that verifies all values are numeric?

You are welcome to save your new db with only
the table and queries in 2000 form, zip it, then send to
me in an attachment. I only have 2000 and 2002 Access
versions. If that is something you feel comfortable doing,
my email address is

g
a
r
y
l
w
at
w
a
m
e
g
o
dot
n
e
t

I also have a small Access db that goes out and
gets versions of Jet files and compares in reports
if that would be something you might be interested
in trying? I have not run on 2003 machine, plus I
haven't updated for a year or better. It might show
though where versions are out of synch....

I was working with a fully-updated version of access 2003. Today I'm
using an older build and getting the same results. I tried copying the
table to a new database and re-creating the query, with the same
results.
The main query alone consistently returns all 149 rows.
Removing / rebuilding the indices seems to make no difference.
Moving the criteria to the Where clause and/or replacing Like with the
Left function seems to make no difference.
I also tried making a table with just all 149 values of [Updated WBS]
and joining my query to it in an attempt to force the return of all 149
rows. This did not work either.

The very first thig I did was to look at which rows were missing but i
couldn't see any explanation there.
The values of [Updated WBS] range from 413 to 661

For one series of runs, the missing [Updated WBS] values were:
527, 485, 482, 498, 497, 485, 487, 529, 503

The row numbers for these missing rows were:
48, 24, 23, 33, 32, 24, 26, 49, 39

During this series, about 3 trials returned all 149 rows.

The missing rows do seem to cluster around a quarter to a third into
the list but, other than that, I don't see anything notable in the
missing rows or data. More to the point, I can't even imagine anything
that I could possibly do to my data that should produce results that
vary in this manner.

Now, for the good news, I have found that adding DISTINCT to the main
query causes it to (so far) reliably return all 149 rows. I tried this
in a spirit of desperate experimentation and I don't understand why it
should change the results.

So, I suppose my query is fixed, but I no longer trust my database and
I have a great many queries more complex than this one. I would still
like to understand what's happening here and I would still appreciate
any explanation or suggestions.

 

[David F Cox]

My guess is that it is because there is no ORDER BY clause.

 

[Don Maclean]

I'm not sure what you mean by:
"The main query alone consistently returns all 149 rows."
Does that mean the simple group by query without the subqueries?

Yes, exactly. I mean the following query consistently returns all
rows:

SELECT e.[Updated WBS]
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

Or that adding DISTINCT to above query was what
caused "main query" to produce consistent results?

No, I mean here that the full original query, with all subqueries and
the addition of a single DISTINCT in the main query, consistently
returns all rows. I may as well include it here.
The following query appears to reliably return all 149 rows. My
original query, (which is identical except for the addition of
DISTINCT) returns a varying set of rows

SELECT DISTINCT e.[Updated WBS],
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70422000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70422000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70423000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70423000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70600000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70600000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70601000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70601000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70620000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70620000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70800000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70800000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77000000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77000000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77005000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77005000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77015000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77015000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77040000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77040000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77041000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77041000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77042000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77042000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77043000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77043000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77050000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77050000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77051000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77051000Direct,
Sum(e.[US$ AMOUNT]) AS SumOfDirect
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

What field type is [Updated WBS]? Long, Text? Text

Did you try running update query that TRIM's this
field?

Yes. It did not help.

You are welcome to save your new db with only
the table and queries in 2000 form, zip it, then send to
me in an attachment. I only have 2000 and 2002 Access
versions. If that is something you feel comfortable doing,

I'm not entirely comfortable with that as the data is not mine.
However I'm sufficiently troubled by this that I may see if I can
reproduce the effect in a subset of data and take you up on your offer.
I very much appreciate your suggestions.

 

[Don Maclean]

Sorry, I don't understand Why an ORDER BY clause would change which
rows are returned?

In any case, adding ORDER BY e.[Updated WBS] does not help.

 

[Gary Walter]

I apologise in advance for this WAG
but did you try using separate aliases
(not all "b") in subqueries.

Also...

I imagine you realize you basically have
"written out" a crosstab....


TRANSFORM SUM(e.[US$ AMOUNT]) As s
SELECT
e.[Updated WBS],
SUM(s) AS SumOfDirect
FROM
Expenses As e
GROUP BY
e.[Updated WBS]
HAVING e.[Updated WBS] Not Like "888*"
PIVOT e.[G/L acct] & "Direct"

you could add an "IN .." to the PIVOT
clause to pick up only the G/L accts you
wanted, but I imagine a better option would
be to set up a table for these specific accts,
then....???

Just join to this table in the SELECT clause of xtab...

Note:
SumOfDirect won't sum for *all* accts
for each [Updated WBS] if you use table
or add "IN ..."
it will only sum over PIVOT columns...

you could instead replace SUM(s)
with a correlated domain function to
get all accts

DSUM("[US$ AMOUNT]","Expenses",
"[Updated WBS] ='" & e.[Updated WBS] & "'")

I'm not sure what you mean by:
"The main query alone consistently returns all 149 rows."
Does that mean the simple group by query without the subqueries?

Yes, exactly. I mean the following query consistently returns all
rows:

SELECT e.[Updated WBS]
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

Or that adding DISTINCT to above query was what
caused "main query" to produce consistent results?

No, I mean here that the full original query, with all subqueries and
the addition of a single DISTINCT in the main query, consistently
returns all rows. I may as well include it here.
The following query appears to reliably return all 149 rows. My
original query, (which is identical except for the addition of
DISTINCT) returns a varying set of rows

SELECT DISTINCT e.[Updated WBS],
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70422000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70422000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70423000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70423000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70600000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70600000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70601000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70601000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70620000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70620000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70800000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70800000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77000000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77000000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77005000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77005000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77015000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77015000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77040000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77040000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77041000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77041000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77042000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77042000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77043000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77043000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77050000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77050000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77051000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77051000Direct,
Sum(e.[US$ AMOUNT]) AS SumOfDirect
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

What field type is [Updated WBS]? Long, Text? Text

Did you try running update query that TRIM's this
field?

Yes. It did not help.

You are welcome to save your new db with only
the table and queries in 2000 form, zip it, then send to
me in an attachment. I only have 2000 and 2002 Access
versions. If that is something you feel comfortable doing,

I'm not entirely comfortable with that as the data is not mine.
However I'm sufficiently troubled by this that I may see if I can
reproduce the effect in a subset of data and take you up on your offer.
I very much appreciate your suggestions.

 

[Gary Walter]

Of course maybe more efficient "divide-and-conquer"
would sum over all accts and filter out [Updated WBS]'s
in prelim query

qrySumDirect

SELECT
b.[Updated WBS] As UpdWBS,
SUM(b.[US$ AMOUNT]) As SumAmt
FROM
Expenses As b
WHERE
b.[Updated WBS] Not Like "888*";

then join this query and acct table
and Expenses in xtab query...

tblRptGLAccts
ID Acct
1 70422000
2 70600000
3 70601000
etc

TRANSFORM SUM(e.[US$ AMOUNT]) As s
SELECT
e.[Updated WBS],
First(q.SumAmt) AS SumOfDirect
FROM
tblRptGLAccts As t
INNER JOIN
(Expenses As e
INNER JOIN
qrySumDirect As q
ON
e.[Updated WBS] = q.UpdWBS)
ON
t.Acct = e.[G/L Acct]
GROUP BY
e.[Updated WBS]
PIVOT t.Acct & "Direct";

Of course "writing out" is not my forte,
but if I made some stupid mistake above,
hopefully you can follow the logic...

an of course, my logic may be wrong as well....

I apologise in advance for this WAG
but did you try using separate aliases
(not all "b") in subqueries.

Also...

I imagine you realize you basically have
"written out" a crosstab....


TRANSFORM SUM(e.[US$ AMOUNT]) As s
SELECT
e.[Updated WBS],
SUM(s) AS SumOfDirect
FROM
Expenses As e
GROUP BY
e.[Updated WBS]
HAVING e.[Updated WBS] Not Like "888*"
PIVOT e.[G/L acct] & "Direct"

you could add an "IN .." to the PIVOT
clause to pick up only the G/L accts you
wanted, but I imagine a better option would
be to set up a table for these specific accts,
then....???

Just join to this table in the SELECT clause of xtab...

Note:
SumOfDirect won't sum for *all* accts
for each [Updated WBS] if you use table
or add "IN ..."
it will only sum over PIVOT columns...

you could instead replace SUM(s)
with a correlated domain function to
get all accts

DSUM("[US$ AMOUNT]","Expenses",
"[Updated WBS] ='" & e.[Updated WBS] & "'")

I'm not sure what you mean by:
"The main query alone consistently returns all 149 rows."
Does that mean the simple group by query without the subqueries?

Yes, exactly. I mean the following query consistently returns all
rows:

SELECT e.[Updated WBS]
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

Or that adding DISTINCT to above query was what
caused "main query" to produce consistent results?

No, I mean here that the full original query, with all subqueries and
the addition of a single DISTINCT in the main query, consistently
returns all rows. I may as well include it here.
The following query appears to reliably return all 149 rows. My
original query, (which is identical except for the addition of
DISTINCT) returns a varying set of rows

SELECT DISTINCT e.[Updated WBS],
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70422000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70422000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70423000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70423000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70600000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70600000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70601000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70601000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70620000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70620000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="70800000" AND b.[Updated WBS] = e.[Updated WBS])
AS 70800000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77000000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77000000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77005000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77005000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77015000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77015000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77040000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77040000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77041000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77041000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77042000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77042000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77043000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77043000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77050000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77050000Direct,
(SELECT Sum(b.[US$ AMOUNT]) FROM Expenses AS b
WHERE b.[G/L acct] ="77051000" AND b.[Updated WBS] = e.[Updated WBS])
AS 77051000Direct,
Sum(e.[US$ AMOUNT]) AS SumOfDirect
FROM Expenses AS e
GROUP BY e.[Updated WBS]
HAVING (((e.[Updated WBS]) Not Like "888*"));

What field type is [Updated WBS]? Long, Text? Text

Did you try running update query that TRIM's this
field?

Yes. It did not help.

You are welcome to save your new db with only
the table and queries in 2000 form, zip it, then send to
me in an attachment. I only have 2000 and 2002 Access
versions. If that is something you feel comfortable doing,

I'm not entirely comfortable with that as the data is not mine.
However I'm sufficiently troubled by this that I may see if I can
reproduce the effect in a subset of data and take you up on your offer.
I very much appreciate your suggestions.