query too complex

E

ersus

hi can anyone help me about my problem..

i made a query that gets all the data from one query(INVTYCOVBCWH) where
location code = "LGWHSE" i get the data on this query: INVTYCOVBCWH

My access code is this:

(the name of this query is: ALL_LGWHSE)

SELECT DISTINCT [INVTYCOVBCWH].[ITEMNMBR],
Sum([INVTYCOVBCWH].BCICWWH.INVCOVWH.ENDBAL) AS ENDL,
[INVTYCOVBCWH].[ITEMDESC], [INVTYCOVBCWH].[UOMPRICE],
[INVTYCOVBCWH].[BRNDCODE], [INVTYCOVBCWH].[BRAND],
[INVTYCOVBCWH].[CLASSCODE], [INVTYCOVBCWH].[LEF], [INVTYCOVBCWH].[CLASS],
[INVTYCOVBCWH].[LOCNCODE], [INVTYCOVBCWH].[LOCNDESC],
[INVTYCOVBCWH].[NUMMONS], [INVTYCOVBCWH].[AVESALES],
[INVTYCOVBCWH].[COVERAGE], [INVTYCOVBCWH].[WC], [INVTYCOVBCWH].[INVVAL],
[INVTYCOVBCWH].[BCLCOV], [INVTYCOVBCWH].[BCLWC], [INVTYCOVBCWH].[BCCOV],
[INVTYCOVBCWH].[BCWC], [INVTYCOVBCWH].[BCOV], [INVTYCOVBCWH].[BWC],
[INVTYCOVBCWH].[GRANDWC]
FROM INVTYCOVBCWH
WHERE LOCNCODE='LGWHSE'
GROUP BY [INVTYCOVBCWH].[ITEMDESC], [INVTYCOVBCWH].[UOMPRICE],
[INVTYCOVBCWH].[BRNDCODE], [INVTYCOVBCWH].[BRAND],
[INVTYCOVBCWH].[CLASSCODE], [INVTYCOVBCWH].[LEF], [INVTYCOVBCWH].[CLASS],
[INVTYCOVBCWH].[LOCNCODE], [INVTYCOVBCWH].[LOCNDESC],
[INVTYCOVBCWH].[NUMMONS], [INVTYCOVBCWH].[AVESALES],
[INVTYCOVBCWH].[COVERAGE], [INVTYCOVBCWH].[WC], [INVTYCOVBCWH].[INVVAL],
[INVTYCOVBCWH].[BCLCOV], [INVTYCOVBCWH].[BCLWC], [INVTYCOVBCWH].[BCCOV],
[INVTYCOVBCWH].[BCWC], [INVTYCOVBCWH].[BCOV], [INVTYCOVBCWH].[BWC],
[INVTYCOVBCWH].[GRANDWC], [INVTYCOVBCWH].[ITEMNMBR];

based on that query, i made an additional query that sums the ending balance
and inventory balance where location code is equal to LGWHSE. i use two query
on this. the ALL_LGWHSE query and INVTYCOVBCWH query.

my access code is this:

(the name of this query is: ALL_NA_LGWHSE)

SELECT Sum([INVTYCOVBCWH].BCICWWH.INVCOVWH.ENDBAL) AS ENDBALL,
Sum([INVTYCOVBCWH].[INVVAL]) AS INVVAL, [ALL_LGWHSE].[ITEMNMBR],
[ALL_LGWHSE].[ITEMDESC], [ALL_LGWHSE].[UOMPRICE], [ALL_LGWHSE].[BRNDCODE],
[ALL_LGWHSE].[BRAND], [ALL_LGWHSE].[CLASSCODE], [ALL_LGWHSE].[LEF],
[ALL_LGWHSE].[CLASS], [ALL_LGWHSE].[LOCNCODE], [ALL_LGWHSE].[LOCNDESC],
[ALL_LGWHSE].[NUMMONS], [ALL_LGWHSE].[AVESALES], [ALL_LGWHSE].[COVERAGE],
[ALL_LGWHSE].[WC], [ALL_LGWHSE].[BCLCOV], [ALL_LGWHSE].[BCLWC],
[ALL_LGWHSE].[BCCOV], [ALL_LGWHSE].[BCWC], [ALL_LGWHSE].[BCOV],
[ALL_LGWHSE].[BWC], [ALL_LGWHSE].[GRANDWC]
FROM ALL_LGWHSE INNER JOIN INVTYCOVBCWH ON
[ALL_LGWHSE].[ITEMNMBR]=[INVTYCOVBCWH].[ITEMNMBR]
GROUP BY [ALL_LGWHSE].[ITEMNMBR], [ALL_LGWHSE].[ITEMDESC],
[ALL_LGWHSE].[UOMPRICE], [ALL_LGWHSE].[BRNDCODE], [ALL_LGWHSE].[BRAND],
[ALL_LGWHSE].[CLASSCODE], [ALL_LGWHSE].[LEF], [ALL_LGWHSE].[CLASS],
[ALL_LGWHSE].[LOCNCODE], [ALL_LGWHSE].[LOCNDESC], [ALL_LGWHSE].[NUMMONS],
[ALL_LGWHSE].[AVESALES], [ALL_LGWHSE].[COVERAGE], [ALL_LGWHSE].[WC],
[ALL_LGWHSE].[BCLCOV], [ALL_LGWHSE].[BCLWC], [ALL_LGWHSE].[BCCOV],
[ALL_LGWHSE].[BCWC], [ALL_LGWHSE].[BCOV], [ALL_LGWHSE].[BWC],
[ALL_LGWHSE].[GRANDWC];

all is well in my program. i generated the inventory report that i needed.
but when i deployed my program on other computers, "ERROR DETECTED BY
DATABASE DLL" prompted.(by the way, i am using visual basic 6 and seagate
crystal report on my program and ms access 2000) when i check my query,
"QUERY TOO COMPLEX" appears. i tried to run my program on different
computers, the strange this is it only works on three computers(including
mine) and doesnt work on other. almost all of the computers i've tired has
the same specifications. i also tried updating the windows and office in this
site but still, it doesn't work. can anyone please help me with my problem?
thanks in advance!
 
D

david epsom dot com dot au

"Query too complex" happens when the query optimiser
is compiling the SQL into the internal representation.

It sometimes means that there is a problem with
the data, because the query optimiser needs to
look at the data to decide what the optimum
execution path will be.

Apart from that, download and re-register the
latest Jet update. I'm not sure if it is part
of either Windows or Office update.

(david)

ersus said:
hi can anyone help me about my problem..

i made a query that gets all the data from one query(INVTYCOVBCWH) where
location code = "LGWHSE" i get the data on this query: INVTYCOVBCWH

My access code is this:

(the name of this query is: ALL_LGWHSE)

SELECT DISTINCT [INVTYCOVBCWH].[ITEMNMBR],
Sum([INVTYCOVBCWH].BCICWWH.INVCOVWH.ENDBAL) AS ENDL,
[INVTYCOVBCWH].[ITEMDESC], [INVTYCOVBCWH].[UOMPRICE],
[INVTYCOVBCWH].[BRNDCODE], [INVTYCOVBCWH].[BRAND],
[INVTYCOVBCWH].[CLASSCODE], [INVTYCOVBCWH].[LEF], [INVTYCOVBCWH].[CLASS],
[INVTYCOVBCWH].[LOCNCODE], [INVTYCOVBCWH].[LOCNDESC],
[INVTYCOVBCWH].[NUMMONS], [INVTYCOVBCWH].[AVESALES],
[INVTYCOVBCWH].[COVERAGE], [INVTYCOVBCWH].[WC], [INVTYCOVBCWH].[INVVAL],
[INVTYCOVBCWH].[BCLCOV], [INVTYCOVBCWH].[BCLWC], [INVTYCOVBCWH].[BCCOV],
[INVTYCOVBCWH].[BCWC], [INVTYCOVBCWH].[BCOV], [INVTYCOVBCWH].[BWC],
[INVTYCOVBCWH].[GRANDWC]
FROM INVTYCOVBCWH
WHERE LOCNCODE='LGWHSE'
GROUP BY [INVTYCOVBCWH].[ITEMDESC], [INVTYCOVBCWH].[UOMPRICE],
[INVTYCOVBCWH].[BRNDCODE], [INVTYCOVBCWH].[BRAND],
[INVTYCOVBCWH].[CLASSCODE], [INVTYCOVBCWH].[LEF], [INVTYCOVBCWH].[CLASS],
[INVTYCOVBCWH].[LOCNCODE], [INVTYCOVBCWH].[LOCNDESC],
[INVTYCOVBCWH].[NUMMONS], [INVTYCOVBCWH].[AVESALES],
[INVTYCOVBCWH].[COVERAGE], [INVTYCOVBCWH].[WC], [INVTYCOVBCWH].[INVVAL],
[INVTYCOVBCWH].[BCLCOV], [INVTYCOVBCWH].[BCLWC], [INVTYCOVBCWH].[BCCOV],
[INVTYCOVBCWH].[BCWC], [INVTYCOVBCWH].[BCOV], [INVTYCOVBCWH].[BWC],
[INVTYCOVBCWH].[GRANDWC], [INVTYCOVBCWH].[ITEMNMBR];

based on that query, i made an additional query that sums the ending
balance
and inventory balance where location code is equal to LGWHSE. i use two
query
on this. the ALL_LGWHSE query and INVTYCOVBCWH query.

my access code is this:

(the name of this query is: ALL_NA_LGWHSE)

SELECT Sum([INVTYCOVBCWH].BCICWWH.INVCOVWH.ENDBAL) AS ENDBALL,
Sum([INVTYCOVBCWH].[INVVAL]) AS INVVAL, [ALL_LGWHSE].[ITEMNMBR],
[ALL_LGWHSE].[ITEMDESC], [ALL_LGWHSE].[UOMPRICE], [ALL_LGWHSE].[BRNDCODE],
[ALL_LGWHSE].[BRAND], [ALL_LGWHSE].[CLASSCODE], [ALL_LGWHSE].[LEF],
[ALL_LGWHSE].[CLASS], [ALL_LGWHSE].[LOCNCODE], [ALL_LGWHSE].[LOCNDESC],
[ALL_LGWHSE].[NUMMONS], [ALL_LGWHSE].[AVESALES], [ALL_LGWHSE].[COVERAGE],
[ALL_LGWHSE].[WC], [ALL_LGWHSE].[BCLCOV], [ALL_LGWHSE].[BCLWC],
[ALL_LGWHSE].[BCCOV], [ALL_LGWHSE].[BCWC], [ALL_LGWHSE].[BCOV],
[ALL_LGWHSE].[BWC], [ALL_LGWHSE].[GRANDWC]
FROM ALL_LGWHSE INNER JOIN INVTYCOVBCWH ON
[ALL_LGWHSE].[ITEMNMBR]=[INVTYCOVBCWH].[ITEMNMBR]
GROUP BY [ALL_LGWHSE].[ITEMNMBR], [ALL_LGWHSE].[ITEMDESC],
[ALL_LGWHSE].[UOMPRICE], [ALL_LGWHSE].[BRNDCODE], [ALL_LGWHSE].[BRAND],
[ALL_LGWHSE].[CLASSCODE], [ALL_LGWHSE].[LEF], [ALL_LGWHSE].[CLASS],
[ALL_LGWHSE].[LOCNCODE], [ALL_LGWHSE].[LOCNDESC], [ALL_LGWHSE].[NUMMONS],
[ALL_LGWHSE].[AVESALES], [ALL_LGWHSE].[COVERAGE], [ALL_LGWHSE].[WC],
[ALL_LGWHSE].[BCLCOV], [ALL_LGWHSE].[BCLWC], [ALL_LGWHSE].[BCCOV],
[ALL_LGWHSE].[BCWC], [ALL_LGWHSE].[BCOV], [ALL_LGWHSE].[BWC],
[ALL_LGWHSE].[GRANDWC];

all is well in my program. i generated the inventory report that i needed.
but when i deployed my program on other computers, "ERROR DETECTED BY
DATABASE DLL" prompted.(by the way, i am using visual basic 6 and seagate
crystal report on my program and ms access 2000) when i check my query,
"QUERY TOO COMPLEX" appears. i tried to run my program on different
computers, the strange this is it only works on three computers(including
mine) and doesnt work on other. almost all of the computers i've tired has
the same specifications. i also tried updating the windows and office in
this
site but still, it doesn't work. can anyone please help me with my
problem?
thanks in advance!
Click to expand...
 
Top