Solved

Convert Design View CrossTab query SQL for use in VBA

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

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now