Solved

Convert Design View CrossTab query SQL for use in VBA

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

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 36

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
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.

730 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