Newby needs a little coaching here with statement 1

[ribhead]

I am trying to retrieve some information from an external data source but I keep getting a General ODBC Error. I'm pretty sure it has to do with my WHERE STATEMENT but I'm struggling.

Next question can I do mathematical operations to records in the fields? I want to calculate the inverse of each record in the Rate field but again I struggle.

Perhaps this can't be done if so please let me know but be gentle I have feelings too.

Thanks for reading

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[ribhead]

Perhaps it would be nice of me to post some code? Duhh

P.S. I am trying to do this from XL

Sub Please_Help()

Dim pnum As String
pnum = 8800463
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=AMES01;", Destination _
:=Range("A4"))
.CommandText = Array( _
"SELECT DPNEW.MFRFMR02 AS Part_Number, DPNEW.MFRFMR03 AS Operation, DPNEW.DESC AS Machine, DPNEW.MFRFMR0P AS Rate" & Chr(13) & "" & Chr(10) & "FROM AMES01.SASUSIO.DPNEW DPNEW" & Chr(13) & "" & Chr(10) & "WHERE(Part_Number='" & pnum & "')")

.Name = "Query from ames01"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[ribhead]

O.K. I quit struggling and got it to work. I still would like to know how I can take the inverse of each record in the field MFMROP example:SUM(1/MFMROP)


I wouldn't know if this is a dumb post but if I can make someone laugh then that is truly something to be proud of.

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[bartsimpson]

It looks like you're doing a grouping operation to use the SUM operator.
Make sure that the MFMROP field is always non zero by adding a WHERE clause within your SQL :

WHERE MFMROP <> 0

 

[ribhead]

Thanks for the post but I am trying to teach myself here so I may need a little more info. The MFMROP is a field that contains hours per piece. I need to inverse that number to get pieces per hour. Any further assistance would be much appreciated

"SELECT DISTINCT MFRFMR02, Sum(1/(MFRFMROP) as Rate FROM DPNEW WHERE " & _
"(MFRFMR08 Like '" & strdept & "' AND MFRFMR03='020A') GROUP BY MFRFMR02"



Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[bartsimpson]

Just add the MFMROP <> 0 AND .... immediately after the WHERE clause. If you have values in this field that aren't zero, then you will get a divide by zero error that will stop the query ( and as SQL Server does things in advance of what you see, the values that are returned may not be the ones that cause the divide by zero error ).

 

[ribhead]

Below is my code. I'm stil not understanding why it's not working. I'm a dabbler and have been learning this stuff on my own. So any further assistance would be great.

Sub My_Sum()
Dim strdept As String
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

strdept = "124%" 'This will come from an option button.


'Connecting to AS400 in XL

cnt.Open "DSN=AMES01;"
strSQL = "SELECT DISTINCT MFRFMR02, Sum(1/MFRFMROP) FROM DPNEW WHERE " & _
"(MFRFMROP <> 0 AND MFRFMR08 Like '" & strdept & "' AND MFRFMR03='020A') " & _
"GROUP BY MFRFMR02"
Set rst = cnt.Execute(strSQL)
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = nohting

End Sub

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[zhavic]

At a first, remove the DISTINCT clause from your statement, you are grouping rows for the MFRFMR02 column, so the rows
in result will be always distinct.

At the second, try to use SUM( 1.0 / MFRFMROP ), the SQL server may decide the result column as integer if you use SUM( 1 / MFRFMROP ), so than you can get only values of 0 or 1


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[bartsimpson]

It may also be an idea to begin slowly, and work your way up to the full statement :

select * from dpnew

and then add the where statement:

WHERE MFRFMROP <> 0 AND MFRFMR08 Like '" & strdept & "' AND MFRFMR03='020A'

then add a group:

select sum(MFRFMROP)
.....
GROUP BY 
MFRFMR02

finally modifying the sum to :

select sum(1.0 / mfrfmrop)

You can then see exactly what's going on, and where your mistakes are by splitting the construction of the query down into these discrete steps.

Keep it simple, and keep it as clear as possible. Add comments into big queries to remind yourself what's going on when you have to modift the query several months later. Do this by :

-- the two dashes mean the rest of the line is a comment and is not processed

 

[ribhead]

Thanks for the help Bart. I take it you are talking about writing code in SQL. I am writing all my code in either Access or XL VBA. Not that it makes a difference because you are right I need to add comments. This was the code that I was working on.

If Me.optpiecesperhour.Value = True Then
strSQL = "SELECT TRIM(MFRFMR02),TRIM(ITMDESC)," & _
"TRIM(MFRFMR03),TRIM(MFRFMR08),TRIM(DESC)," & _
"ROUND(SUM(1/MFRFMR0P),2),ROUND(SUM(1/MFRFMR0Q),2)" & _
" FROM DPNEW " & _
"WHERE (MFRFMR0P<>0 AND MFRFMR02 ='" & strpartnumber & "')" & _
" GROUP BY MFRFMR02,ITMDESC,MFRFMR03,MFRFMR08,DESC"
Else
strSQL = "SELECT TRIM(MFRFMR02),TRIM(ITMDESC)," & _
"TRIM(MFRFMR03),TRIM(MFRFMR08),TRIM(DESC)," & _
"MFRFMR0P,MFRFMR0Q" & _
" FROM DPNEW " & _
"WHERE ( MFRFMR02 ='" & strpartnumber & "')"
End If

I think I need to work on indentation. Geoff(xlbo) already hinted to me to work on it.

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.