6 Replies - 782 Views - Last Post: 07 March 2014 - 10:30 AM Rate Topic: -----

#1 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Convert Access Query to CFQuery

Posted 06 March 2014 - 01:29 PM

I am entirely new to CF. So here is my request for help:

Will please you give me a boost here? I downloaded a bunch of CF training videos as well Eclipse and CFEclispe for an IDE, I am learning as I go. Id like to know if you have the time to walk me through the following conversion of an existing Access Query to a CFQuery:

SQL Statement from Access:
PARAMETERS [EndDate] DateTime;
TRANSFORM CLng(Nz(Count([DatatblQDLog].[QDLogType]),0)) AS CountOfType
SELECT DatatblQDLog.QDLogType
FROM DatatblQDLog
GROUP BY DatatblQDLog.QDLogType
PIVOT "Mth" & DateDiff("m",[QDLogDate],[EndDate]) In ("Mth12","Mth11","Mth10","Mth9","Mth8","Mth7","Mth6","Mth5","Mth4","Mth3","Mth2","Mth1","Mth0");

Expected results from Access:
Copy Of qryCrosstabTypes
QDLogType Mth12 Mth11 Mth10 Mth9 Mth8 Mth7 Mth6 Mth5 Mth4 Mth3 Mth2 Mth1 Mth0
1 1 0 0 0 0 0 0 0 0 0 0 0 0
2 153 210 67 58 59 48 143 172 29 166 143 95 0
3 18 37 25 16 18 0 8 2 7 18 31 28 0

What the data represents is a count in each of the last 12 months of the number of Audits(1), Forms(2), and Surveillances(3) conducted from an [EndDate] of 3/6/14.

What I think I need:
A Cfselect field to input any date from the past; I would like the form to default to today(), unless another date is chosen.
A Cfquery using the CfSelect as an input parameter and the 13 local variables outputted and populated in a table.

A script that labels the MTH12MTH0 with the actual MMM YY represented in Access this is a TextBox with the Control Source:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...


Again this is only my second day working with ColdFusion.

Is This A Good Question/Topic? 0
  • +

Replies To: Convert Access Query to CFQuery

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1912
  • View blog
  • Posts: 3,444
  • Joined: 13-January 08

Re: Convert Access Query to CFQuery

Posted 06 March 2014 - 01:49 PM

Welcome to DIC braddorl!

So, to begin with, welcome to CFML as well! It's a flexible and powerful web development language with a rather manageable learning curve. The question you've asked is one that you'll eventually get accustomed to the more CF you write.

To be able to answer you more properly we'd need to know what you're using for the database backend for your CF application. Is it still Access or are you using SQL Server? The reason I ask is because, while both are Microsoft database products, they use slightly different versions of Structured Query Language (SQL). In fact, given that it's your second day, have you set up your database as a CF data source as yet? This is a necessary first step before you can even concern yourself with translating an existing Access data request into a SQL statement to be executed by CFQUERY.

Get back with some details and we can go from there.

Good luck!
Was This Post Helpful? 1
  • +
  • -

#3 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Re: Convert Access Query to CFQuery

Posted 06 March 2014 - 03:19 PM

Craig328,
The backend is still in Access and has been setup in ColdFusion. I will caveat my request for assistance with the statement that I am not a Developer, I am a Quality Engineer that knew a little bit of VBA. I ended up with this task because I am the Process Owner of the data and we do not have any programmers; so it was more a "Tag!!! Your it!" Our IT guy is a network sort, when I approached him to help he suggested Google. I learned Access by shear necessity and I assume that is how I will learn CF. Any help is certainly appreciated; the Lynda.com videos will only take me so far.

Thank you,
Randy
Was This Post Helpful? 0
  • +
  • -

#4 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Re: Convert Access Query to CFQuery

Posted 06 March 2014 - 04:35 PM

So a little more information:

I initially tried to just drop my AccessSQL statement above in as a Cfquery and needless to say it gave me an exception error. So I started commenting out lines of the SQL, until I was able to achieve a list of the QDLOGType and QDLogDate, so I least know I am getting something. I tried various other queries to see if I could GROUP BY QDLogType and even tried .recordcount, which told me in the small dataset I am using for development I have 1767 records. I also used a WHERE statement in the SQL to limit the query to 1, 2, or 3 for QDLogType, but I obviously did something wrong because I couldn't get the QDLogDate to show using Cfdump.


<cfquery datasource="DASHBOARD" dbtype="ODBC" name="qtesting">

SELECT QDLogType, QDLogDate
FROM DatatblQDLog
Order By QDLogDate
</cfquery>



So now to answer the lingering question: Why am I migrating to CF?
The original database was an Excel spreadsheet (Yes I can hear you cringing!) that I used for the charting functions. However, over time the spreadsheet grew and grew, my boss would request new metrics, something new to analyze, yada yada yada! until it became unbearable and began to reach the Excel limitations. So I made an Access database to contain the data and wrote queries that exported only what was necessary to Excel for analysis and charting, very tedious. Since our server was already running CF and MySQL for other processes, the boss decided that my data and the analysis needed to be web based so others could access it without needing me to pull a rabbit out of a hat every time they needed information. So here I am. While it may sound like I am cynical about the move to CF, I am certainly not! I know it is robust and will be able to do what I and my small company need it to. And I have to admit that I am a little excited by the challenge of learning it. So I am not looking for anyone to develop the code for me and package it up. I want to learn the syntax and pull out a few more hairs. So please, walk me through the logic of your answers or point me in the right direction.

Randy
Was This Post Helpful? 0
  • +
  • -

#5 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Re: Convert Access Query to CFQuery

Posted 07 March 2014 - 05:32 AM

Here is the code I played with last evening. My thought process is:

1. Query the records from DatatblQDLog providing me with QDLogData (a date a record is created) and QDLogType (the type of record--possible values are 1, 2, or 3).

2. Provide the user with inputs to set the date they want to analyze to (#EndDate#), the number of periods to analyze (#NumPeriod# --Limit to a whole number between 1 and 12), and the size of the period (#Periods#--"w" week, "m" month, "q" quarters).

3. When the user Submits, use the variables from 2 above to evaluate and count each record in the query into the desired Column "Per12, Per11, ..., Per0" and row (determined by QDLogType). Present the data in a table.

The previous AccessSQL handled null counts with CLng(Nz(Count([DatatblQDLog].[QDLogType]),0)) I will need to figure this out to ensure that each column has a number even if null.

This thought process seems to be a rather clunky way to query and count the records into a essentially a Pivot table. Is there a cleaner way?

<cfquery datasource="DASHBOARD" dbtype="ODBC" name="qtesting">

<!--- This is the SQL Statement and Results from Access that I am trying to recreate in CF

SQL Statement from Access:
PARAMETERS [EndDate] DateTime;
TRANSFORM CLng(Nz(Count([DatatblQDLog].[QDLogType]),0)) AS CountOfType
SELECT DatatblQDLog.QDLogType
FROM DatatblQDLog
GROUP BY DatatblQDLog.QDLogType
PIVOT "Mth" & DateDiff("m",[QDLogDate],[EndDate]) In ("Mth12","Mth11","Mth10","Mth9","Mth8","Mth7","Mth6","Mth5","Mth4","Mth3","Mth2","Mth1","Mth0");

Expected results from Access:
Copy Of qryCrosstabTypes
QDLogType	Mth12	Mth11	Mth10	Mth9	Mth8	Mth7	Mth6	Mth5	Mth4	Mth3	Mth2	Mth1	Mth0
1	1	0	0	0	0	0	0	0	0	0	0	0	0
2	153	210	67	58	59	48	143	172	29	166	143	95	0
3	18	37	25	16	18	0	8	2	7	18	31	28	0
--->

SELECT QDLogType, QDLogDate
FROM DatatblQDLog
Order By QDLogDate
</cfquery>

<!--- The (2) cfinclude template clauses that follow build the header and menu items --->
<cfinclude template="../includes/header.cfm"/>
<cfinclude template="../includes/menuitems.cfm"/>
	
<div id="page-content">
	<h3 class="title">Enter Parameters for Analysis:</h3>

<!--- This is where we will begin to build page's dynamic content  --->

<!--- start form --->
<cfform name="SetAnalysisPeriod" method="post" class="noprint">
      <table width=100%>
      <tr>
	  <td width=5% align="left">Date:&nbsp; </td>
      <td width=20%>
            <cfinput type="datefield" name="EndDate" size="13" validateat="onsubmit" validate="usdate"  required="yes"  value= #now()# message="You must enter a 'FROM' date">
      </td>
      <td width=20% align="left">Number of Periods:&nbsp; </td>
      <td width=20%>
            <cfinput type="text" name="NumPeriod" size="13" validateat="onsubmit" required="yes" value="12" message="Enter number of periods" >
      </td>
      <td width=10% align="left">Periods:&nbsp; </td>
      <td width=20%>
            <!--- <cfselect name="Periods">
                  <option value="" selected>-- Select Periods --</option>
                  <cfoutput query="SelectedPeriodicty">
                        <option value="#Analysis_Period#">#Analysis_Period#</option>
                  </cfoutput> 
            </cfselect> --->
      </td>
      <td width=10%><cfinput type="Submit" name="submitit" value="Go" width="100"></td>
     </tr>
     </table>
</cfform>

<!--- IN ORDER TO GET THIS TO WORK date2 will need to be QDLogDate and the "m" will need to change to the result of "Periods". 
I believe some variant of the following code will be necessary to manipulate QDLogDate from the query into the column format
"MTH12, MTH11,....MTH0"--->

<!--- <cfset testdate="Per" & DateDiff("m",date2,EndDate)> 	
<cfoutput>											
#testdate#
</cfoutput> --->

		<div class="entry">
			<div  style="font-size: 11px">
			</div>				
		</div>
<!-- All content will end here -->

<!--- This includes the formatted footer --->
<cfinclude template="../includes/footer.cfm">




Was This Post Helpful? 0
  • +
  • -

#6 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1912
  • View blog
  • Posts: 3,444
  • Joined: 13-January 08

Re: Convert Access Query to CFQuery

Posted 07 March 2014 - 08:57 AM

With development, there is always more than one way to skin a cat. In your case, I'd recommend that you consider re-ordering the operations. That is, present the user with a form wherein they can indicate what time period they'd like data for, the periods and period size (and any other qualifiers or filters you might like to apply) and then take those conditions and THEN apply them to a data pull from the database.

This way, you can pull only the data you need and then display that however you choose.

Not knowing your database tables or the way the data in them is organized, it's difficult to say how best to query your data. For instance, you're performing a pivot in the SQL which, while entirely valid, is something I don't normally deal with. In your case, it may be entirely necessary though. In any event, building a CF application like you're wanting will be a step by step process. So you'll first want to build the simple form, submit that form to another page or itself, run the query on that destination page using the passed in form values as conditions you'd add to a WHERE statement in your query (which will be enclosed in a CFQUERY tag). At that point, I'd use a CFDUMP that dumps the returned recordset from Access to see what came back. This will allow you to tweak your form and your query to get it to do what it is you want. When you're satisfied with what it's bringing back then you can remove the CFDUMP and start building your output display using CFOUTPUT amongst other tags.

Try building out the form piece first and feel free to post it here for suggestions. We'll be happy to help you along.

Good luck!
Was This Post Helpful? 1
  • +
  • -

#7 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Re: Convert Access Query to CFQuery

Posted 07 March 2014 - 10:30 AM

So something like this:

It outputs the query as expected, but I am having a problem formatting the dates in the heading. I have commented in the code.

<!--- The (2) cfinclude template clauses that follow build the header and menu items --->
<cfmodule template="../includes/header.cfm" pagetitle="Quality Dashboard" headingtitle="[company name]" sectiontitle="Quality Dashboard">
<cfmodule template="../includes/menuitems.cfm">
	
<div id="page-content">
	<h3 class="title">Enter Parameters for Analysis:</h3>

<!--- This is where we will begin to build page's dynamic content  --->


<!--- start form --->
<cfform name="SetAnalysisPeriod" method="post" class="noprint">
      <table width=100%>
      <tr>
	  <td width=5% align="left">Date:&nbsp; </td>
      <td width=20%>
            <cfinput type="datefield" name="EndDate" size="13" validateat="onsubmit" validate="usdate"  required="yes"  value= #now()# message="You must enter a 'FROM' date">
      </td>
      <td width=20% align="right">Number of Periods:&nbsp; </td>
      <td width=20%>
             <cfselect name="NumPer">
                <option value="" selected>-- Select Number --</option>
				<option value="1">1</option>
				<option value="2">2</option>
				<option value="3">3</option>
				<option value="4">4</option>
				<option value="5">5</option>
				<option value="6">6</option>
				<option value="7">7</option>
				<option value="8">8</option>
				<option value="9">9</option>
				<option value="10">10</option>
				<option value="11">11</option>
				<option value="12">12</option>	
			</cfselect>
      </td>
      <td width=10% align="left">Periods:&nbsp; </td>
      <td width=20%>
            <cfselect name="Periods">
                <option value="" selected>-- Select Periods --</option>
				<option value="w">Week</option>
				<option value="m">Month</option>
				<option value="q">Quarter</option>
				<option value="y">Year</option>			   		
			</cfselect>
      </td>
      <td width=10%><cfinput type="Submit" name="submitit" value="Go" width="100"></td>
     </tr>
     </table>
</cfform>
<!--- This section uses the inputs from cfform.setAnalysisPeriod to build and populate the table  --->

 <cfset workingdate = CreateODBCDateTime(#dateformat(EndDate)#)>
	<cfswitch expression="#Periods#">
    <cfcase value="m">
        <cfloop index="i" from="0" to="#NumPer#">
			<cfset "Per#i#" = #dateformat(DateAdd("#Periods#",-i, workingdate),"mmm yyyy")#> <!--- This date format works --->
		</cfloop>
    </cfcase>
    <cfcase value="q" >
        <cfloop index="i" from="0" to="#NumPer#">
			<cfset "Per#i#" = #dateformat(DateAdd("#Periods#",-i, workingdate),"mmm yyyy")#> <!--- Need to work this in the format to provide Q1 yyyy, Q2 yyyy... --->
		</cfloop>
    </cfcase>
    <cfcase value="y" >
        <cfloop index="i" from="0" to="#NumPer#">
			<cfset "Per#i#" = #dateformat(DateAdd("#Periods#",-i, workingdate),"yyyy")#>	<!--- While this gives me the Year as a column head, it is all current year --->
		</cfloop>
    </cfcase>
    <cfcase value="w" >
        <cfloop index="i" from="0" to="#NumPer#">
			<cfset "Per#i#" = #dateformat(DateAdd("#Periods#",-i, workingdate),"mm/dd/yyyy")#> <!--- Format is correct but the dates it spits out are not by week, may need a first of the week code --->
		</cfloop>
    </cfcase>
    <cfdefaultcase>
        <cfloop index="i" from="0" to="#NumPer#">
			<cfset "Per#i#" = #dateformat(DateAdd("#Periods#",-i, workingdate),"mmm yyyy")#>
		</cfloop>
    </cfdefaultcase>
</cfswitch>

<cfoutput>
#NumPer#
#periods#
</cfoutput>

<cfquery datasource="DASHBOARD" dbtype="ODBC" name="qtesting">
	TRANSFORM Count([DatatblQDLog].[QDLogType]) AS CountOfType
	SELECT DatatblQDLog.QDLogType
	FROM DatatblQDLog
	GROUP BY DatatblQDLog.QDLogType
	PIVOT 'Per' & DateDiff('#periods#',QDLogDate, #workingdate#) In ('Per0','Per1','Per2','Per3','Per4','Per5','Per6','Per7','Per8','Per9','Per10','Per11','Per12');

</cfquery>


<div>
<table class="datatable">
	<tr>
		<th>LOGTYPE</th>
		<cfloop index="i" from="#NumPer#" to="0" step="-1">
			   	<cfset hdr = #EVALUATE('Per'&i)#>
				<th><cfoutput>#hdr#</cfoutput></th>
			</cfloop>
		
</tr>

<cfoutput query="qtesting">
<tr>
	<td>#QDLOGTYPE#</td>
	<cfloop index="i" from="#NumPer#" to="0" step="-1">
		  	<cfset rowdata = #EVALUATE('Per'&i)#>
			<td><cfif rowdata is "">0
				<cfelse>#rowdata#</cfif></td>
		</cfloop>
	

</tr>
</cfoutput>
</table>
</div>
<!--- End dynamic code --->
		<div class="entry">
			<div  style="font-size: 11px">
			</div>				
		</div>
<!-- All content will end here -->

<!--- This includes the formatted footer --->
<cfmodule template="../includes/footer.cfm">




Was This Post Helpful? 0
  • +
  • -

Page 1 of 1