CROSSTAB QUERIES 1

[Raptor136]

HOW do you paste a crosstab query into code? I copied the SQL view and then pasted the following into an OnClick. VBA hated it. All other SQL pasted worked awesome. BTW I deliberatly mangled the docmd.SQL in order to prevent someones machine from calling it a suspect script.

DoCmd.RanSQi TRANSFORM Count(LOCALtrlr002.trlr_nr) AS 'CountOftrlr_nr
SELECT LOCALtrlr002.ero, LOCALtrlr002.pri, Count(LOCALtrlr002.trlr_nr) AS [Total Of trlr_nr]
FROM LOCALtrlr002
WHERE (((LOCALtrlr002.pri)<&quot;07&quot)
GROUP BY LOCALtrlr002.ero, LOCALtrlr002.pri
ORDER BY LOCALtrlr002.ero
PIVOT LOCALtrlr002.rcvd_cd;

Thanks in advance,
Rap

 

[Steve101]

Try the following:

DoCmd.RUNSQL "TRANSFORM Count(trlr_nr) AS CountOftrlr_nr " & _
             "SELECT ero, pri, Count(trlr_nr) AS [Total Of trlr_nr] " & _
             "FROM LOCALtrlr002 " & _
             &quot;WHERE pri < '07' &quot; & _
             &quot;GROUP BY ero, pri &quot; & _
             &quot;ORDER BY ero &quot;
             &quot;PIVOT rcvd_cd &quot;

You need to provide the SQL statement as a string to the Docmd.RunSQL command; hense I've &quot;surrounded&quot; the SQL string by quotes; essentially concatenating or joining a bunch of line based strings to make up the entire SQL string.

In order not to cause confusion with the 07 literal, I changed the double quotes around it to single quotes. I also removed the table name prefixes from where-ever it was used as a field qualifier; this is not required if the query comprises only one table, and just causes the whole thing to be verbose. Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Raptor136]

Thanks. I cut and pasted it into the VBA window, but it just didn't work. I kept getting an &quot;Expected end of statement&quot; error at the word Count. I appreciate the effort though. If you think of any other approach I'd sure be interested. To bad it's too long to make into a macro and convert. Then at least we'd see how VBA prefers to see it typed. Thanks again -- Rap

 

[Steve101]

Rap,

Afraid I gave you a bum steer. There was an ommission in my last post, which I'll corrected below (in red). Try this:

DoCmd.RUNSQL "TRANSFORM Count(trlr_nr) AS CountOftrlr_nr " & _
             "SELECT ero, pri, Count(trlr_nr) AS [Total Of trlr_nr] " & _
             "FROM LOCALtrlr002 " & _
             &quot;WHERE pri < '07' &quot; & _
             &quot;GROUP BY ero, pri &quot; & _
             &quot;ORDER BY ero &quot;

 & _

             "PIVOT rcvd_cd "

Let me know how you go, Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Raptor136]

Steve -- I caught the missing characters myself and added them. But just to be sure, I ran it again. Now the Error reads &quot;Run time error 2342, RunSQL statement requires an argument consisting of an SQL statement&quot;. I don't want to be the kind of guy that nit picks it until someone else does my work for me. On the other hand, I really appreciate the time you've taken because I know you have better things to do. Any other advice will be gratefully taken. Once I find a solution, I will post so all benifit. Thanks again -- Rap

 

[Steve101]

Rap,
I've actually tested this SQL and it does work. If you send me your email address, I'll send you a working example. Dont know why its not working for you.
Cheers, Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[godmamma07]

Have been following this thread with much interest as I have the same kind of need. Working with my own CrossTab (not very complicated) I following the detailed instructions by Steve, and also encountered the error message, &quot;Run SQL statement requires an argument consisting of an SQL statement&quot;.

I'd appreciate the resolution being posted as I seem to be at the same place as Rap.

Thank you so much.
Pat

 

[Steve101]

Rap, Pat,

I've given you guys a bit of a bum steer. My SQL worked when pasted into an SQL design window and run, but not from the associated Docmd.RunSQL command. The RunSQL method only works with Action queries (ie. to create, update, delete records), of which the standard SELECT statement is not one.

To use the crosstab SQL in code, you could do the following:

(a) Take the SQL, and create a crosstab query from it in the query design window. If you have the table defined, then simply paste in the following SQL into the SQL window (its the same SQL less the string related characters):

   TRANSFORM Count(trlr_nr) AS CountOftrlr_nr 
   SELECT ero, pri, Count(trlr_nr) AS [Total Of trlr_nr] 
   FROM   LOCALtrlr002  
   WHERE  pri < '07' 
   GROUP BY ero, pri 
   ORDER BY ero 
   PIVOT rcvd_cd

(b) Save and run the query to test it. I'll assume its called qryYourCrossTab.

(c) To run the query from code, the command is then:

    DoCmd.OpenQuery "QueryYourCrossTab"

You might wish to also iterate through the records in code, in which case you'd define a recordset based on the query (or indeed directly on the SQL string). Thats another little exercise, and depends on what it is you're trying to achieve.

Also, having created up the Query definition, its possible programatically to change the SQL within it, using a querydef object. Just food for thought.

Get this working first, and let me know how you go.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Raptor136]

Well I have learned a thing or two, and now have to go investigate the querydef thing. Thanks Steve you've been awesome. Another Star! -- Rap

 

[LemonDrop]

Thanks for the response, Steve. Will try the alternative and see if it produces the results I'm looking for.

Pat