?
Solved

Convert Design View CrossTab query SQL for use in VBA

Posted on 2014-10-29
5
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 38

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 85
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 38

Accepted Solution

by:
PatHartman earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 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