Can't edit MSQuery

[ajm]

can anyone tell me how to get around this. i have a daily update that relies on MS Query to update figures in my main workbook. At the end of each day, i am to hit a button which updates historical sales data.

Problem arises only on Tuesday. Sure, the SQL of the query is different to each other day. However, try as i might, MS Query will not let me edit the underlying query. the sql for Tuesday and Monday (M, W, Th and Fri are all the same syntax).

MONDAY:

SELECT tbllastyr0021.Date, tbllastyr0021.ProductNo, Sum(tbllastyr0021.NetSaleLastYr) AS 'Net Sale'
FROM `I:\SHARED\Estimates\Estimates`.tbllastyr0021 tbllastyr0021
WHERE (tbllastyr0021.`CAT NO` In ('312','315','450','500','510','520','560')) AND (tbllastyr0021.CALWKNO=?)
GROUP BY tbllastyr0021.Date, tbllastyr0021.ProductNo


TUESDAY

SELECT tbllastyr0022.Date, tbllastyr0022.ProductNo, Sum(tbllastyr0022.NetSaleLastYr) AS 'Net Sale'
FROM `I:\SHARED\Estimates\Estimates`.tbllastyr0022 tbllastyr0022
WHERE (tbllastyr0022.CALWKNO=?)
GROUP BY tbllastyr0022.Date, tbllastyr0022.ProductNo, tbllastyr0022.`CAT NO`
HAVING (tbllastyr0022.`CAT NO` In ('312','315','450','500','510','520','560'))

I want to redo Tuesday's query so that it resembles the format and syntax of Monday - it just won't let me.

any suggestions?

ajm

 

[dk]

Hi,

Are you able to click on the Edit Query button or is it disabled? Do you get any messages and if so, what is the exact message you get?

Dan

 

[ajm]

G'day Dan,

Edit Query brings up the following message "This query cannot be edited by the query wizard".

If you then click on "OK" it takes you to an input box which is actually the first stage of the routine asking you for the week number. cancelling this then lets you change the query.

However, it wil not let me save any changes.

??

ajm

 

[dk]

OK, that's what I get. However, once you're in MS Query you should be able to click the SQL button and edit the SQL there. Once you've edited as you the SQL click OK - if you've specified a parameter in your query then it will prompt you to enter a value. Enter any old value. Your new query should hopefully then be displayed in the MS Query window....

If so, click File, Return Data to MS Excel and you're away.

Let me know how you get on,

Dan

 

[ajm]

Dan,

Seems to have worked and is running ok. keep fingers crossed as we archive this folder weekly and begin again next week in the same spot.

Regs,

ajm