Query becomes corrupt

[JaneInMA]

I am writing a series of crosstab queries like the one below

TRANSFORM Nz(Count([macs-US].[loggedBy]),0) AS CountOfREFNUM
SELECT Format([ALRTDATE],"yyyy-mm") AS monthu, Format([ALRTDATE],"mmm-yyyy") AS [Month]
FROM (SELECT [macs-US].LOGGEDBY, [macs-US].ALRTDATE, [macs-US].COMPCODE, tblcompcode.cocode, [macs-US].LEVEL, [macs-US].MODNUM, tblcompcode.FCategory, tblmodnum.Type
FROM ([macs-US] INNER JOIN tblcompcode ON [macs-US].COMPCODE = tblcompcode.CompCODE) INNER JOIN tblmodnum ON [macs-US].MODNUM = tblmodnum.MODNUM
WHERE ((([macs-US].ALRTDATE) Between DateSerial(Year(Date())-2,Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1-1)) AND (([macs-US].COMPCODE) Like "rsg*") AND (([macs-US].LEVEL)<>"0") AND (([macs-US].MODNUM) Like "*3*") AND ((tblmodnum.Type)="meter"))
UNION ALL
SELECT [macs-UK].LOGGEDBY, [macs-UK].ALRTDATE, [macs-UK].COMPCODE, tblcompcode.cocode, [macs-UK].LEVEL, [macs-UK].MODNUM, tblcompcode.FCategory, tblmodnum.Type
FROM ([macs-UK] INNER JOIN tblcompcode ON [macs-UK].COMPCODE = tblcompcode.CompCODE) INNER JOIN tblmodnum ON [macs-UK].MODNUM = tblmodnum.MODNUM
WHERE ((([macs-UK].ALRTDATE) Between DateSerial(Year(Date())-2,Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1-1)) AND (([macs-UK].COMPCODE) Like "rsg*") AND (([macs-UK].LEVEL)<>"0") AND (([macs-UK].MODNUM) Like "*3*") AND ((tblmodnum.Type)="meter"))
)
GROUP BY Format([ALRTDATE],"yyyy-mm"), Format([ALRTDATE],"mmm-yyyy")
ORDER BY Format([ALRTDATE],"yyyy-mm")
PIVOT tblcompcode.FCategory;

in access 2000. This query runs fine but when I save this and close it it alters the sql to this
TRANSFORM Nz(Count([macs-US].[loggedBy]),0) AS CountOfREFNUM
SELECT Format([ALRTDATE],"yyyy-mm") AS monthu, Format([ALRTDATE],"mmm-yyyy") AS [Month]
FROM [SELECT [macs-US].LOGGEDBY, [macs-US].ALRTDATE, [macs-US].COMPCODE, tblcompcode.cocode, [macs-US].LEVEL, [macs-US].MODNUM, tblcompcode.FCategory, tblmodnum.Type
FROM ([macs-US] INNER JOIN tblcompcode ON [macs-US].COMPCODE = tblcompcode.CompCODE) INNER JOIN tblmodnum ON [macs-US].MODNUM = tblmodnum.MODNUM
WHERE ((([macs-US].ALRTDATE) Between DateSerial(Year(Date())-2,Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1-1)) AND (([macs-US].COMPCODE) Like "rsg*") AND (([macs-US].LEVEL)<>"0") AND (([macs-US].MODNUM) Like "*3*") AND ((tblmodnum.Type)="meter"))
UNION ALL
SELECT [macs-UK].LOGGEDBY, [macs-UK].ALRTDATE, [macs-UK].COMPCODE, tblcompcode.cocode, [macs-UK].LEVEL, [macs-UK].MODNUM, tblcompcode.FCategory, tblmodnum.Type
FROM ([macs-UK] INNER JOIN tblcompcode ON [macs-UK].COMPCODE = tblcompcode.CompCODE) INNER JOIN tblmodnum ON [macs-UK].MODNUM = tblmodnum.MODNUM
WHERE ((([macs-UK].ALRTDATE) Between DateSerial(Year(Date())-2,Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1-1)) AND (([macs-UK].COMPCODE) Like "rsg*") AND (([macs-UK].LEVEL)<>"0") AND (([macs-UK].MODNUM) Like "*3*") AND ((tblmodnum.Type)="meter"))
]. AS [%$##@_Alias]
GROUP BY Format([ALRTDATE],"yyyy-mm"), Format([ALRTDATE],"mmm-yyyy")
ORDER BY Format([ALRTDATE],"yyyy-mm")
PIVOT tblcompcode.FCategory;

At this point Access gives an error message when trying to run the query.
Has anyone any idea why Access is doing this?
I need to run about 20 of these queries monthly so hand-running them is not really my first option. I really want to be able to run them via VBA in a scheduling db.
Not sure if this is a factor but this is being run on about 2GB of dbf data with Access lookup tables.
Thanks for any help you can provide

 

[BNPMike]

It will do this if you try to open the query in the QBE screen. QBE can't deal with Unions. I don't know why.

Is that what happenned? When did it get changed into the other format?

 

[JaneInMA]

I write the query and it runs fine, I then save it and exit. I then try to open the query in the application windows and it fails. If I then open it in design view it goes to the QBE window (with all issues) but if I go to the sql change the square brackets around the subquery to normal brackets and remove the alias it runs just fine
But it seems to prevent running except immediately after editing the sql.

 

[BNPMike]

Don't know. I've just tried a simpler thing and it picks up it's a union query and actually greys out the QBE option. On the other hand I have seen it generate this alias. Also if you exit query design from the SQL view, sometimes it goes back to SQL when you open it again; sometimes to the QBE screen.

(If it goes quiet, re-post it)

 

[JaneInMA]

Makes me wonder if it is a microsoft feature.
The query does pull four tables, two of which are massive and dbfs, together using a union query in a subquery for a crosstab. Perhaps the complexity breaks the sql.
It doesnt even realise this is a union query because of it being nested in the subquery.
I do wish I had a more robust query writer which didnt require such work arounds.
The query works but whether it is optimal is a good question. But I always find pushing these boundaries is the best way to learn new tricks with SQL.

 

[BNPMike]

What you might want to look at is CreateQueryDef method in VBA. I don't use VBA but it looks as though you can 'save' your source SQL into a query without using the Query Dialogue.

 

[JaneInMA]

I am pretty sure the reason Access is having issues with this is because I am doing my joins within the subquery calling the lookup tables twice.
I have rewritten the query giving the subquery an alias and setting up the joins using the subquery product.
Here is the new query
TRANSFORM Nz(Count([WWFC].[loggedBy]),0) AS CountOfREFNUM
SELECT Format([WWFC].[ALRTDATE],"yyyy-mm") AS monthu
, Format([WWFC].[ALRTDATE],"mmm-yyyy") AS [Month]
FROM (tblmodnum INNER JOIN
[SELECT [macs-US].LOGGEDBY, [macs-US].ALRTDATE, [macs-US].COMPCODE, [macs-US].LEVEL, [macs-US].MODNUM
FROM [macs-US]
WHERE ((([macs-US].ALRTDATE) Between DateSerial(Year(Date())-2,Month(Date())-1,1)
And DateSerial(Year(Date()),Month(Date()),1-1))
AND (([macs-US].COMPCODE) Like "rsg*")
AND (([macs-US].LEVEL)<>"0") )
UNION ALL
SELECT [macs-UK].LOGGEDBY, [macs-UK].ALRTDATE, [macs-UK].COMPCODE, [macs-UK].LEVEL, [macs-UK].MODNUM
FROM [macs-UK]
WHERE ((([macs-UK].ALRTDATE) Between DateSerial(Year(Date())-2,Month(Date())-1,1)
And DateSerial(Year(Date()),Month(Date()),1-1))
AND (([macs-UK].COMPCODE) Like "rsg*")
AND (([macs-UK].LEVEL)<>"0"))
]. AS WWFC
ON tblmodnum.MODNUM = WWFC.MODNUM)
INNER JOIN tblcompcode ON WWFC.COMPCODE = tblcompcode.CompCODE
WHERE (((tblmodnum.Type)="meter")
AND ((WWFC.MODNUM)="03"))
GROUP BY Format([WWFC].[ALRTDATE],"yyyy-mm")
, Format([WWFC].[ALRTDATE],"mmm-yyyy")
PIVOT tblcompcode.FCategory;

Of course just what the fact that sorting this issue and getting this fairly complex query to work makes me so happy means, I shall leave for others to determine!