Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Crosstab Query

Posted on 2012-08-17
9
Medium Priority
?
417 Views
Last Modified: 2013-01-18
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
Comment
Question by:Stephen Roesner
  • 4
  • 3
  • 2
9 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 38307125
Paste the working query.
0
 

Author Comment

by:Stephen Roesner
ID: 38307157
???? I have no idea what your talking about, paste what working query???
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 38307377
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 31

Expert Comment

by:hnasr
ID: 38307392
"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

by:Stephen Roesner
ID: 38308089
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

by:peter57r
ID: 38308110
"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

by:Stephen Roesner
ID: 38308207
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 31

Expert Comment

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

Author Closing Comment

by:Stephen Roesner
ID: 38793747
I did as you said and it works thank you very much
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question