Solved

Access Crosstab Query

Posted on 2012-08-17
354 Views
I have inherited an access crosstab query that I cant seem to figure out. It counts the number of records in 3 tables by acct for 30 days. I need to add a total of 8 tables. I thought it would be simple to just copy and paste the code from table 2 and just renumber the numbers. The minute I try to run it it tells me i have improper bracketing around T.name. Can you tell me how to add tables 4 thru 8. This is the code in sql view:

TRANSFORM Sum(SQ.[How Many]) AS [CountOFHow Many]
SELECT Date() AS [Date], SQ.Account, SQ.[Account Name], Sum(SQ.[How Many]) AS Total
FROM [SELECT T.[Account], A.[AccountName] AS [Account Name], T.[Days Old], COUNT(*) AS [How Many] FROM
(SELECT CINT(TS1.[REC-AGE]) AS [Days Old], TS1.[Acct-Num] AS [Account]
FROM TSO_File1 TS1
UNION ALL
SELECT CINT(TS2.[REC-AGE]), TS2.[Acct-Num]
FROM TSO_File2 TS2
UNION ALL
SELECT CINT(TS3.[REC-AGE]), TS3.[Acct-Num]
FROM TSO_File3 TS3) T LEFT JOIN [BEQ_Accounts] A
ON T.[Account]=A.[Account]
GROUP BY T.[Account], A.[AccountName], T.[Days Old]]. AS SQ
GROUP BY Date(), SQ.Account, SQ.[Account Name]
PIVOT SQ.[Days Old] In (0, 1, 2, 3, 4, 5, 6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);
0
Question by:Grizbear51

LVL 30

Expert Comment

Paste the working query.
0

Author Comment

???? I have no idea what your talking about, paste what working query???
0

LVL 77

Accepted Solution

You will see that you have ..

.....FROM [SELECT T.[Account........

...T.[Days Old]]. AS SQ....

The [ before SELECT and the ].  before AS  should be ( and )  -No dot- respectively.
This is Access getting things wrong.  If you correct this and save the query then you should be oK again, assuming the rest of the sql is correct - I haven't checked.

But if you open the query in design view again you will have the same problem and have to make the same corrections.

It is possible to fix the problem permanently by using saved queries instead of subqueries.
0

LVL 30

Expert Comment

"I have inherited an access crosstab query that I cant seem to figure out"

Open the query in sql view.
Just copy that's query sql and paste here.
0

Author Comment

I don't think people are understanding me here. This query as is is working fine!
It does what its suppose to do and counts records in 3 tables. So all is good!!!!
The problem is how do I add tables 4 thru 8. Thats when I get an error - when I try to do this: (Copy lines 7 thru 9 and paste to lines 10 thru 12 and change the numbers from 2 to 3 and the lines after from 3 to 4)

TRANSFORM Sum(SQ.[How Many]) AS [CountOFHow Many]
SELECT Date() AS [Date], SQ.Account, SQ.[Account Name], Sum(SQ.[How Many]) AS Total
FROM [SELECT T.[Account], A.[AccountName] AS [Account Name], T.[Days Old], COUNT(*) AS [How Many] FROM
(SELECT CINT(TS1.[REC-AGE]) AS [Days Old], TS1.[Acct-Num] AS [Account]
FROM TSO_File1 TS1
UNION ALL
SELECT CINT(TS2.[REC-AGE]), TS2.[Acct-Num]
FROM TSO_File2 TS2
UNION ALL
SELECT CINT(TS3.[REC-AGE]), TS3.[Acct-Num]
FROM TSO_File3 TS3
UNION ALL
SELECT CINT(TS4.[REC-AGE]), TS4.[Acct-Num]
FROM TSO_File4 TS4) T LEFT JOIN [BEQ_Accounts] A
ON T.[Account]=A.[Account]
GROUP BY T.[Account], A.[AccountName], T.[Days Old]]. AS SQ
GROUP BY Date(), SQ.Account, SQ.[Account Name]
PIVOT SQ.[Days Old] In (0, 1, 2, 3, 4, 5, 6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);

When I try to add a table between 2 and 3 and change the numbers it errors with improper bracketing.
0

LVL 77

Expert Comment

"The minute I try to run it it tells me i have improper bracketing around "

You do have improper bracketing.  I've just explained why.
0

Author Comment

OK I did what you said to do and replaced the bracket and bracket & period with "and" and saved it now I get a generic statement that there is a syntax error in the transform statement.

TRANSFORM Sum(SQ.[How Many]) AS [CountOFHow Many]
SELECT Date() AS [Date], SQ.Account, SQ.[Account Name], Sum(SQ.[How Many]) AS Total
FROM and SELECT T.[Account], A.[AccountName] AS [Account Name], T.[Days Old], COUNT(*) AS [How Many] FROM
(SELECT CINT(TS1.[REC-AGE]) AS [Days Old], TS1.[Acct-Num] AS [Account]
FROM TSO_File1 TS1
UNION ALL
SELECT CINT(TS2.[REC-AGE]), TS2.[Acct-Num]
FROM TSO_File2 TS2
UNION ALL
SELECT CINT(TS3.[REC-AGE]), TS3.[Acct-Num]
FROM TSO_File3 TS3) T LEFT JOIN [BEQ_Accounts] A
ON T.[Account]=A.[Account]
GROUP BY T.[Account], A.[AccountName], T.[Days Old] and AS SQ
GROUP BY Date(), SQ.Account, SQ.[Account Name]
PIVOT SQ.[Days Old] In (0, 1, 2, 3, 4, 5, 6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);
0

LVL 30

Expert Comment

Remove rest of expression after PIVOT SQ.[Days Old] . Run the query and check the column headings.
0

Author Closing Comment

I did as you said and it works thank you very much
0