Solved

Convert Design View CrossTab query SQL for use in VBA

Posted on 2014-10-29
5
234 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
I suddenly cannot write to C drive 20 74
User Level Security 6 38
Run SQL Server Proc from Access 11 30
Criteria for Date for DCount 4 19
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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