Ack! Same query with different results on different computers!

[nnaacc]

I open two copies of the same exact database on two different computers. The tables and queries are identical.

There are two nested queries. The first query merges two large tables - a 1st half 2001 and a 2nd half of 2001 with data by month. It merges them by first finding all possible key combinations (salesman and item), and then selecting all available data by month. As far as I can tell, on both computers, this query has the same results. On both computers - the result is 27,598 rows with the same key combinations.

The second query then takes the results from the first query (and nothing else) and sums it up by item by sales region. This query appears to have the same results (15,356 rows).

Then when I filter this second query for salespeople from a certain region, the query has different results on different computers! One computer has 8,500 resulting rows (different items), and the other has about 7,900 resulting rows. I believe that larger result - 8,500 rows is "more accurate".

I also tried to keep the second query without the filter, and create a simple 3rd query that takes the results from the second and performs the filter. Again - one computer has different results than the other.

Ack! This makes me VERY nervous. I need the list to be completely accurate. I don't know how to check the tens of thousands of resulting numbers from the query, but I need to know that the computer that appears accurate actual is.

Both computers are running MS Access version 9.0.2720. The "incorrect computer" is a PII400 with Windows NT version 4.00.1381 and the other one is a PIII933 with Windows 2000 Professional version 5.00.2195

 

[nnaacc]

I think I uncovered a bug in Access version 9.0.2720 installed on one of my two computers.

If I change the filter in the query to filter for more than one thing, regardless of what the second thing is, then it seems to work.

So the query basically was

Select SalesPerson, Region, Item, Sum(JanRevenue), Sum(FebRevenue) etc...
From CombinedH1andH2
Where Region = "NE"
Group By SalesPerson, Item

That gave the wrong result on one computer, but (I hope) the right result on another. If I use a HAVING clause instead of WHERE, the result is different, but still incomplete.

Then, when I change the query to have a second criteria

WHERE Region = "NE" OR Region = "anything I want such as Green"
it works! The second criteria can be anything I think.

Note: the computer that runs the query correctly came with Access 2000 installed. The computer that doesn't run correctly was upgraded from Access 97 to 2000 months ago.

I'm very curious if other people have found the same problem or can explain it.

 

[gwinn7]

This is a shot in the dark...

Perhaps when you upgraded the data in the table somehow was altered. What happens when you change your WHERE condition to ...

WHERE Region Like "*NE*"

???

Is there any change ?

Gary
gwinn7

 

[nnaacc]

if I use like instead of =, the query works correctly.

My first theory was the same as yours - that the "NE" field may contain more data than the NE that is displayed, therefore it filters differently. But adding a second, random criteria wouldn't seem to fix that possible problem.

I upgraded before I created any part of this database. And I made an exact duplicate to open on the other computer, which returns the complete, correct results for the query even without a like statement or a second random criteria.

 

[gwinn7]

Ok, it seems that the NE has trailing spaces. Have you looked into this? If so, you could write a function or query to clean this up.

Gary
gwinn7

 

[genomon]

Hey NNAACC
Don't even know if this is worth weighing in on, but the HAVING clause vs. the WHERE clause involves grouping issues. Is it possible that the NT system is treating its SQL differently (different MS Engine)? I have developed cross-platform Access apps without the troubling issues you bring up, and my experience has always been that different versions of ACCESS can create problems, but not the OS. If you have upgraded, maybe try uninstalling ACC97 altogether (making sure there are no file remnants) and reinstalling the newer one. Especially true if the "old" version is trying to peacefully coexist with the new; if it's a conflict of Office vs. stand-alone Access, there may be file name corruption. There are several articles out there, especially on MSKB, that try to get us to make the different versions get along on the same desktop, but this is seldom the case. Just a random thought...
HopeToHelp (little bit anyway)
-Geno

 

[nnaacc]

Thanks for all the tips guys.

I did repair & re-install Office 2000. That didn't fix the problem, but I didn't completely remove Office and then re-install. There are not multiple versions installed.

Like I said - I tried HAVING and WHERE. As I understand it, the queries will run in different order of operations, but I think in this case the results should be the same. The results are different, and neither seems complete. The results are complete using HAVING or WHERE, as long as I add a second criteria - any second criteria, or if I use like.

I did run queries to update the region field in the data tables to replace everything like "NE*" with "NE", but still get the same problem.

I guess I'll try to completely uninstall, and reinstall Office 2000. Not sure if it is worth my while though (needing to re-adjust all of my Office2000 options & settings). I have no idea if the Access engine (JET?) is OS or Access specific. Wouldn't really surprise me if it is both though - I have great faith that MS would try to interconnect things as much as possible.

 

[genomon]

Hey NNAACC
HAVING in Access needs grouping - WHERE does not. Wasn't too clear on that one before. Flavors of SQL irrelevant here - if there is a HAVING clause in Access inline SQL (and I believe in the query tool as well) it either requires or assumes grouping for you (GROUP BY ...). One last thing to consider before you spend time on the reinstall..
-Genomon

 

[nnaacc]

Yes - the query has grouping.
The underlying data is by Sales person and item and region. The query then groups by item and region - summing up all the sales person's revenues.

The other computer runs the query fine (the same) with HAVING or WHERE as the filtering clause.