Solved

Convert Design View CrossTab query SQL for use in VBA

Posted on 2014-10-29
5
238 Views
Last Modified: 2014-10-31
Hello.  I am using Access 2013 (Office 365).  

I have a crosstab query which I created in the Design view.   This works as planned.  Below is the SQL version of the working crosstab query.  I want / need to put this in VBA so that I can change two parameters in the query ... [Source] & [Destination] at runtime as necessary.

This cross tab query will serve as the input for a simple query. I may have to post another question about that later on.  I  need to create the crosstab query at runtime, but do not require do view the query.  I know that I can use the code shown below (with minor modification),  but not sure how.

For now though,  I hope that I have asked this question with clarity.   There might be a better approach to this,  and I will give that consideration,  but I want to explore this approach first.

Thank you for your help.



TRANSFORM Sum([tbl Transactions].Quantity) AS SumOfQuantity
SELECT [tbl Transactions].StockNum, [tbl Transactions].Source, [tbl Transactions].Destination, [tbl Transactions].[Invoice Date], Sum([tbl Transactions].Quantity) AS [Total Of  Quantity], Sum([quantity]*(([Source]=8002)-([destination]=8002))) AS [IBC Cavendish -- Stock Vector], Sum([quantity])*([Transaction Type]=2)*([Source]=8002)*([Destination]=50003)*-1 AS [IBC Cavendish Sales]
FROM [tbl Transactions]
WHERE ((([tbl Transactions].Source)=8002)) OR ((([tbl Transactions].Destination)=8002))
GROUP BY [tbl Transactions].StockNum, [tbl Transactions].Source, [tbl Transactions].Destination, [tbl Transactions].[Transaction Type], [tbl Transactions].[Invoice Date]
PIVOT [tbl Transactions].Quantity;

Open in new window


I have converted that text to a string.   See below

Dim TheString As String
TheString = "TRANSFORM Sum([tbl Transactions].Quantity) AS SumOfQuantity" & _
" SELECT [tbl Transactions].StockNum, [tbl Transactions].Source, [tbl Transactions].Destination, [tbl Transactions].[Invoice Date], Sum([tbl Transactions].Quantity) AS [Total Of  Quantity], Sum([quantity]*(([Source]=8002)-([destination]=8002))) AS [IBC Cavendish -- Stock Vector], Sum([quantity])*([Transaction Type]=2)*([Source]=8002)*([Destination]=50003)*-1 AS [IBC Cavendish Sales]" & _
" FROM [tbl Transactions]" & _
" WHERE ((([tbl Transactions].Source) = 8002)) Or ((([tbl Transactions].Destination) = 8002)) " & _
" GROUP BY [tbl Transactions].StockNum, [tbl Transactions].Source, [tbl Transactions].Destination, [tbl Transactions].[Transaction Type], [tbl Transactions].[Invoice Date]" & _
" PIVOT [tbl Transactions].Quantity;"

Open in new window

0
Comment
Question by:peispud
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 40410963
You can leave the query as a querydef and use a parameter to supply the variable at runtime.  Unlike other queries, Crosstab queries require parameters to be specifically defined.

The best way to provide the parameter is to have the query refer to a control on a form.

TRANSFORM Sum([tbl Transactions].Quantity) AS SumOfQuantity
SELECT [tbl Transactions].StockNum, [tbl Transactions].Source, [tbl Transactions].Destination, [tbl Transactions].[Invoice Date], Sum([tbl Transactions].Quantity) AS [Total Of  Quantity], Sum([quantity))) AS [IBC Cavendish -- Stock Vector], Sum([quantity])*([Transaction Type]=2)*([Source]=8002)*([Destination]=50003)*-1 AS [IBC Cavendish Sales]
FROM [tbl Transactions]]*(([Source]=8002)-([destination]=8002
WHERE ((([tbl Transactions].Source)= Forms!yourformname!txtSource)) OR ((([tbl Transactions].Destination)= Forms!yourformname!txtDestination))
GROUP BY [tbl Transactions].StockNum, [tbl Transactions].Source, [tbl Transactions].Destination, [tbl Transactions].[Transaction Type], [tbl Transactions].[Invoice Date]
PIVOT [tbl Transactions].Quantity;

I'm not sure what you are trying to do with the hard coded source and destination fields in the calculations.  They don't make any sense to me.  An expression such as "TransactionType = 2" used as you have it will result in either 0 or -1 which will then be multiplied by quantity.  Is that what you are trying to do?
0
 
LVL 84
ID: 40410974
So you need to change this line, essentially:

WHERE ((([tbl Transactions].Source)=8002)) OR ((([tbl Transactions].Destination)=8002))

If so, you could perhaps do this:

Dim sWhere As String
Dim TheString As String

sWhere = "WHERE ((([tbl Transactions].Source)=" & Me.SomeTextbox & ")) OR ((([tbl Transactions].Destination)=" & Me.SomeTextbox & "))"

TheString = "TRANSFORM Sum([tbl Transactions].Quantity) AS SumOfQuantity" & _
" SELECT [tbl Transactions].StockNum, [tbl Transactions].Source, [tbl Transactions].Destination, [tbl Transactions].[Invoice Date], Sum([tbl Transactions].Quantity) AS [Total Of  Quantity], Sum([quantity]*(([Source]=8002)-([destination]=8002))) AS [IBC Cavendish -- Stock Vector], Sum([quantity])*([Transaction Type]=2)*([Source]=8002)*([Destination]=50003)*-1 AS [IBC Cavendish Sales]" & _
" FROM [tbl Transactions] " & _
sWhere & _
" GROUP BY [tbl Transactions].StockNum, [tbl Transactions].Source, [tbl Transactions].Destination, [tbl Transactions].[Transaction Type], [tbl Transactions].[Invoice Date]" & _
" PIVOT [tbl Transactions].Quantity;"

I broke this out so you could see what I'm doing, but there's no reason why you couldn't do that all when you build TheString ...
0
 

Author Comment

by:peispud
ID: 40411049
To answer the question    
Is that what you are trying to do?
.
The answer is yes,  although I would be hard pressed to follow the logic (although I could if necessary). I created this crosstab query 2 years ago and I have a day job.  The crosstab query works perfectly though.

So, if I understand this properly, the code can be under a form control (button).   I need to create a query table at runtime and delete it as needed.

How do  I create the temp crosstab table.   ie   CurrentDb.Execute(TheString)
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40411133
If that is really what you want, you need to replace all the hard coded values with form field references.  It looks like you have two separate values for destination so you will need two separate form fields.

The .execute method is used to run action queries.  It is not used to run crosstabs.  You won't have this query in VBA at all.  Just use the querydef as the RecordSource for a form or report.  If you want to open the query and read it in code, then you would use the .OpenRecordsource method but you would need to set the parameters in code first.  DAO can't get them from the form fields the way Access can.
0
 

Author Closing Comment

by:peispud
ID: 40415331
Per Pat Hartman

"You can leave the query as a querydef and use a parameter to supply the variable at runtime. "
                                                       and
"you need to replace all the hard coded values with form field references."

I will have to research this answer.  

Thank you for your answer.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

856 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