query working in test db, failing in real db

[barbarossaii]

Hi,

got a query which works in a test db, but fails in the real db.


The SQL of the query is:

SELECT a.LG, a.dat, a.[anz-schl-], a.RMNR, a.[PB-DAT], a.STATUS, a.KST, a.E3, a.E4, a.LIEFERANTENNR_8, a.LIEFERANTENNR_9, a.LIEFENAM, a.ARTID, a.ARTIDX, a.ARTDESC1, a.[tech-entsch], a.merkmal1, a.merkmal2, a.merkmal3, a.PA_DAT, a.[anz-gepr-], a.[jahr-]
FROM qry_PB_getr AS a
WHERE (((a.[anz-schl-]) In (Select Top 3 [anz-schl-] from [qry_PB_getr] where [dat]=a.[dat] and [LG]=a.[LG] Order By [anz-schl-] desc)))
ORDER BY a.LG, a.dat, a.[anz-schl-] DESC;


In my test db I based the query on a table named qry_PB_getr, which has about 5000 records; the records are a copy of the results of the query qry_PB_getr in my real db. The SQL-Works in the test db !

When I run the SQL based on my query qry_PB_getr in the real db I get an error message (translated from German => Engl.):
"The Impression was entered wrong, or it is too compex. For example a numeric expression could contain too many complex elements. Simplify it by using variables."

What can I do ?

Thanks in advance,
Barbarossa II

PS: As I need this query just once a month I don't mind the long run time (getting the results on other ways will take more time and requires harder work ;-)

 

[Mile-O]

where [dat]=a.[dat] and [LG]=a.[LG]

May be a little bit of ambiguity there. [dat] and a.[dat], and [LG] and a.[LG] look as if they are the same field - one explicit and the other implicit.

 

[barbarossaii]

I simply took the code from this thread

http://www.access-programmers.co.uk/forums/showthread.php?threadid=57085

and adapted it regarding the column-names.

What I'd like to do: get the records with the top 3 entries for "anz-schl-" (number) for each dat (date in yyyy-mm formate) and LG (supplier group) combination.
(a simple "top 3" doesn't work because I need the top 3 from each Supplier group - month combination !)



Barbarossa II ( <---------- Beginner)

 

[barbarossaii]

hmmm,

the thread seems to be 'cold' now.

What additional info do you need to help ?


Thanks in advance,
Barbarossa II