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
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