Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[cem_devecioglu]

dim adoconnection1 As New ADODB.Connection
dim adoconnection2 As New ADODB.Connection
dim adoconnection3 As New ADODB.Connection

dim rsl As New ADODB.Recordset
dim rs2 As New ADODB.Recordset
dim rs3 As New ADODB.Recordset

' here other variables used must be declared also



cp1 = "Provider=sqloledb;uid=sa;pwd=password;server=servername;database=dbname ;"

    Set adoconnection1 = New ADODB.Connection
    adoconnection1.CommandTimeout = 2000
    adoconnection1.ConnectionTimeout = 10
    adoconnection1.Open cp1
   

    Set adoconnection2 = New ADODB.Connection
    adoconnection2.CommandTimeout = 2000
    adoconnection2.ConnectionTimeout = 10
    adoconnection2.Open cp1
   
   
    Set adoconnection3 = New ADODB.Connection
    adoconnection3.CommandTimeout = 2000
    adoconnection3.ConnectionTimeout = 10
    adoconnection3.Open cp1



gridrow=0

txt1="select ProductID,Description,RetailPrice,CostPrice from Products order by ProductID" 'read every product id

Set rs1 = New ADODB.Recordset
rs1.Open txt1, adoconnection1


While rs1.EOF = False   

  txt2="select cumstockforprod=sum(UnitsInStock) from StockLevel where ProductID=" & rs1!ProductID
  Set rs2 = New ADODB.Recordset
  rs2.Open txt2, adoconnection2
  cumstockforprod=rs2!cumstockforprod ' get cumulative stock for that product

  suminvoicecount=0
  sumactualcost=0.0


txt3="select Invm.imid,Invm.Invoicedate,Invd.ProductID,Invd.InvoiceDetailID,Invd.CostPrice,Invd.Quantity from "
txt3=txt3 & " (select imid=InvoiceID,InvoiceDate from Invoice) as Invm, "
txt3=txt3 & " (select idid=InvoiceID,ProductID,InvoiceDetailID,CostPrice,Quantity from InvoiceDetails) as Invd "
txt3=txt3 & " where Invm.imid=Invd.idid and Invd.ProductID=" & rs1!ProductID & " order by Invm.InvoiceDate,Invd.InvoiceDetailID desc " 
'if Invoicedate includes hour, minute, second details InvoicedetailID can be omitted but "desc" is the critical part as you already know..



  Set rs3 = New ADODB.Recordset
  rs3.Open txt3, adoconnection3

  while rs3.eof and suminvoicecount<cumstockforprod
    if suminvoicecount+rs3!Quantity <=cumstockforprod then

       suminvoicecount=suminvoicecount + rs3!Quantity
       sumactualcost=sumactualcost + rs3!quantity*rs3!CostPrice
   
    else

       suminvoicecount=suminvoicecount+(cumstockforprod-suminvoicecount) '
       sumactualcost=sumactualcost + (cumstockforprod-suminvoicecount)*rs3!CostPrice

    end if
      
    rs3.movenext
  Wend

   '  now write to grid or print
 
   
    gridrow=gridrow+1  '

    grid.rows=gridrow+1

 
   grid(gridrow,1).text=rs1!Description
   grid(gridrow,2).text=sumactualcost
   
   

 rs1.MoveNext

Wend


and I must note as I have no data handy I cant test it..

[cem_devecioglu]

Jaime , in sql 2005 this transposition or pivoting is a bit easier.. 

http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

Sadly, this also has to work in Jet/Access, so unlikely I can use SQL2K5 specific functions

I'll write a pivoting code with the supplied fields as soon as possible but probably tomorrow..

[TheBoy]

No hurry, can't code this in for a few days anyway....

[cem_devecioglu]

for pivoting I'll prepare the tables and some data

cant write it without testing..

[cem_devecioglu]

code for pivoting

Dim adoc1 As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Command1_Click()
Dim cp1 As String
Dim txt As String
Dim countx As Integer
Dim county As Long
Dim xcord As Integer
Dim ycord As Long
Dim locidmatrix(500) As Integer
Dim prodidmatrix(10000) As Long  ' space is only limited to virtual ram >=array size * 4bytes
Dim locprodmatrix(500, 10000) As Long



' change with necessary access connection string..

cp1 = "Provider=sqloledb;uid=sa;pwd=yasak;server=IULIA-PC;database=testdb;"

    Set adoc1 = New ADODB.Connection
    adoc1.CommandTimeout = 2000
    adoc1.ConnectionTimeout = 10
    adoc1.Open cp1
       
grid1.ColWidth(0) = 2000 ' product names column with in twips scale

'now prepare x axis for grid or table

txt = "select slid,locationDescription from " ' check the double quotes there must be one extra space on the right side
txt = txt & " (select LocationID,LocationDescription from Location) as loc, "
txt = txt & " (select distinct s.slid from "
txt = txt & " (select pid=ProductID,ProductDescription from Products) as p left outer join "
txt = txt & " (select spid=ProductID,slid=LocationID,NumberInLocation from Stock) as s "
txt = txt & " on p.pid=s.spid) as DisLocID "
txt = txt & " where loc.LocationID=DisLocID.slid order by LocationDescription"

countx = 0 ' x coordinate items count initialized
grid1.Cols = 1 ' arrange grid columns accordingly

Set rs = New ADODB.Recordset
rs.Open txt, adoc1

While rs.EOF = False
  countx = countx + 1
  grid1.Cols = grid1.Cols + 1
  grid1.TextMatrix(0, countx) = Trim(rs!LocationDescription)
  locidmatrix(countx) = rs!slid 'id s will be necessary as we use a different order then id
 
  rs.MoveNext
Wend

'now prepare y axis for grid or table


txt = "select ProductID,ProductDescription from "
txt = txt & " (select ProductID,ProductDescription from Products) as p, "
txt = txt & " (select distinct ProdID=ProductID from Stock) as s "
txt = txt & " where p.ProductID=s.ProdID order by ProductDescription "

county = 0 ' y coordinate items count initialized
grid1.Rows = 1 ' arrange grid rows accordingly

Set rs = New ADODB.Recordset
rs.Open txt, adoc1

While rs.EOF = False
  county = county + 1
  grid1.Rows = grid1.Rows + 1
  grid1.TextMatrix(county, 0) = Trim(rs!ProductDescription)
  prodidmatrix(county) = rs!ProductID
   
  rs.MoveNext
 
Wend


'prepare actual data table

txt = "select p.pid,s.slid,s.NumberInlocation from "
txt = txt & "(select pid=ProductID,ProductDescription from Products) as p left outer join "
txt = txt & "(select spid=ProductID,slid=LocationID,NumberInLocation from Stock) as s "
txt = txt & " on p.pid=s.spid order by p.pid,s.slid"

Set rs = New ADODB.Recordset
rs.Open txt, adoc1

While rs.EOF = False
  locprodmatrix(rs!slid, rs!pid) = rs!NumberInlocation  ' location and product ids are used as indices
  rs.MoveNext
 
Wend


' now pivot the table with the help of matrix and loop
For ycord = 1 To county
     For xcord = 1 To countx
         grid1.TextMatrix(ycord, xcord) = locprodmatrix(locidmatrix(xcord), prodidmatrix(ycord))
         
     Next xcord
Next ycord


End Sub

[cem_devecioglu]

and here is how the actual grid looks with some test data


if you need I can supply also a code that you can transfer the pivoted table to excel also which will be helpful for printing purposes..

[Martin_1962]

That is something I could knock up using ISAM access and BBrowser in VO in minutes!

I think I'll pass on SQL for now!!!!

[cem_devecioglu]

That is something I could knock up using ISAM access and BBrowser in VO in minutes!

I think I'll pass on SQL for now!!!!

this is for access not for sqlserver Martin..

and by the way chronometer already started