query results to a specific cell

[mercmannick]

Sub update_data()
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset, r As Long
'additional declarations to suit new syntax
Dim MyConn
Dim sSQL As String
Dim i As Integer

sSQL = "SELECT MRP_CODE.CELL, Count(IMFINTERNAL.[Short Mat]) AS [CountOfShort Mat] " _
& "FROM IMFINTERNAL INNER JOIN MRP_CODE ON IMFINTERNAL.[Short MRP] = MRP_CODE.MRP_CODE " _
& "WHERE (((IMFINTERNAL.[Days Late]) > 0)) " _
& "GROUP BY MRP_CODE.CELL;"
    ' connect to the Access database
    ' and axx of IMFINTERNAL_BY_CELL qry
   
    Set Cn = New ADODB.Connection
    MyConn = "C:\db1.mdb"
   
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
        .Execute sSQL
    End With
          
        Sheets("Data").Activate
            spe = Cells(Rows.Count, 3).End(xlUp).Row ' the next row in the worksheet for speed cell
            E = Cells(Rows.Count, 7).End(xlUp).Row ' the next row in the worksheet for cell e
            F = Cells(Rows.Count, 11).End(xlUp).Row ' the next row in the worksheet for cell f
            G = Cells(Rows.Count, 15).End(xlUp).Row ' the next row in the worksheet for cell g
            W = Cells(Rows.Count, 19).End(xlUp).Row ' the next row in the worksheet for cell w
            S15 = Cells(Rows.Count, 23).End(xlUp).Row ' the next row in the worksheet for s15
            S70 = Cells(Rows.Count, 27).End(xlUp).Row ' the next row in the worksheet for s70
            S17 = Cells(Rows.Count, 31).End(xlUp).Row ' the next row in the worksheet for s17
            

End Sub

'THIS IS WHERE I NEED HELP THE QUERY ABOVE OUTPUTS LIKE THIS
'Cell F 98
'Cell G 6
'S70 28
'Speed 20
'IS THERE A WAY OF FROM QUERY ABOVE TO PUT THE VALUES ie
'CELL F SHOULD GOTO F = Cells(Rows.Count, 11).End(xlUp).Row
'Cell G SHOULD GOTO G = Cells(Rows.Count, 15).End(xlUp).Row
'ETC...........BUT IF THERE IS NO OUTPUT ON A SPECIFIC CELL PUT A 0 IN SO NEXT TIME RUN all the rows match in line
'THEY WILL ALL BE ON SAME ROWS


also posted on mr excel http://www.mrexcel.com/board2/viewtopic.php?t=221276
thanks

Merc

[mdmackillop]

I don't know how your data is presented. Something like this could be used to analyse results and set values. Also, how is your Null result returned? With the initial string?

[VBA]
Select Case Split(data, " ")(0)
Case "Cell"
Select Case Split(data, " ")(1)
Case "E"
E.Value = Split(data, " ")(2)
Case "F"
F.Value = Split(data, " ")(2)
Case "G"
G.Value = Split(data, " ")(2)
Case "W"
W.Value = Split(data, " ")(2)
End Select
Case "S15"
S15.Value = Split(data, " ")(1)
Case "S17"
S17.Value = Split(data, " ")(1)
Case "S70"
S70.Value = Split(data, " ")(1)
Case "Speed"
spe.Value = Split(data, " ")(1)
End Select

[/VBA]

[mercmannick]

if there is no value on null strings it dosent give any output on query, so if it isnt in output of query then put a 0 in col

Thanks
enclosed is sample with the query results
Merc

[mdmackillop]

A bit simpler with the layout.
[vba]
see below[/vba]

[mercmannick]

mdmackillop

that is cool puts values in the right cells, how do i incorporate this with my query

Many Thanks

Merc

[mdmackillop]

Hi Merc,
I'm having problems with the database connection on this line
[VBA]
Dim Cn As ADODB.Connection
[/VBA]What references are you using.
Regards
MD

[mercmannick]

mdmackillop

M$ active x data objects

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset, r As Long
'additional declarations to suit new syntax
Dim MyConn
Dim sSQL As String
Dim i As Integer

sSQL = "SELECT MRP_CODE.CELL, Count(IMFINTERNAL.[Short Mat]) AS [Xlodbc.xlaCountOfShort Mat] " _
& "FROM IMFINTERNAL INNER JOIN MRP_CODE ON IMFINTERNAL.[Short MRP] = MRP_CODE.MRP_CODE " _
& "WHERE (((IMFINTERNAL.[Days Late]) > 0)) " _
& "GROUP BY MRP_CODE.CELL;"
    ' connect to the Access database
    ' and axx of IMFINTERNAL_BY_CELL qry
   
    Set Cn = New ADODB.Connection
    MyConn = "C:\db1.mdb"
    
   
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
        .Execute sSQL
    End With

this works cool , just trying to capture the values from query and pass onto your code now

Thanks

Merc

[mdmackillop]

I've not done much on Excel/Access interaction, so a bit of a learning experience. Give this a try

[vba]
Option Explicit
Sub Update_Data()
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset, r As Long, c As Long
Dim MyField
'additional declarations to suit new syntax
Dim MyConn
Dim sSQL As String
Dim i As Integer
Application.ScreenUpdating = False
sSQL = "SELECT MRP_CODE.CELL, Count(IMFINTERNAL.[Short Mat]) AS [Xlodbc.xlaCountOfShort Mat] " _
& "FROM IMFINTERNAL INNER JOIN MRP_CODE ON IMFINTERNAL.[Short MRP] = MRP_CODE.MRP_CODE " _
& "WHERE (((IMFINTERNAL.[Days Late]) > 0)) " _
& "GROUP BY MRP_CODE.CELL;"
' connect to the Access database
' and axx of IMFINTERNAL_BY_CELL qry


Set Cn = New ADODB.Connection
MyConn = "C:\db1.mdb"

With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set Rs = .Execute(sSQL)
End With

'Write to results area
r = 20
c = 3
Do Until Rs.EOF
For Each MyField In Rs.Fields
Cells(r, c) = MyField
c = c + 1
Next MyField
Rs.MoveNext
r = r + 1
c = 3
Loop

'Get results and write to table
SetCells
Application.ScreenUpdating = True
End Sub


Sub SetCells()
Dim DataRow As Long, Cel As Range, Cols, c
'Get next row to fill
DataRow = [C3].End(xlDown).Row + 1
'Fill data
For Each Cel In Range([C20], [C20].End(xlDown))
Select Case Cel
Case "Speed"
Cells(DataRow, "C") = Cel.Offset(, 1)
Case "Cell E"
Cells(DataRow, "G") = Cel.Offset(, 1)
Case "Cell F"
Cells(DataRow, "K") = Cel.Offset(, 1)
Case "Cell G"
Cells(DataRow, "O") = Cel.Offset(, 1)
Case "Cell W"
Cells(DataRow, "S") = Cel.Offset(, 1)
Case "S15"
Cells(DataRow, "W") = Cel.Offset(, 1)
Case "S70"
Cells(DataRow, "AA") = Cel.Offset(, 1)
Case "S17"
Cells(DataRow, "AE") = Cel.Offset(, 1)
End Select
Next
'Check and fill blanks
Cols = Array("C", "G", "K", "O", "S", "W", "AA", "AE")
For Each c In Cols
If Cells(DataRow, c) = "" Then Cells(DataRow, c) = 0
Next

End Sub

[/vba]

[mercmannick]

mdmackillop

Superb thank you so much, christ if you only just learnt that then i need some serious tuiton lol

Many Many thanks

Merc