Omega Owners Forum
Chat Area => General Discussion Area => Topic started by: TheBoy on 03 June 2010, 20:38:18
-
Sorry, just can't get my head around this, and daft timescales again :-[
3 tables - one is a products table, one a location table, other a stock location table
Products:
ProductID - unique ID
ProductDescription - Product description
Stock:
StockID - unique ID
LocationID - Linked to a locations
ProductID - Linked to product
NumberInLocation - Number in this location
Location:
LocationID - Unique ID
LocationDescription - Name of location
I need to provide a table of:
ProductDescription, Number at Location ID1,Number at Location ID2,Number at Location IDn
-
was heavy on wine :-[ , now solving..
-
Sorry, just can't get my head around this, and daft timescales again :-[
3 tables - one is a products table, one a location table, other a stock location table
Products:
ProductID - unique ID
ProductDescription - Product description
Stock:
StockID - unique ID
LocationID - Linked to a locations
ProductID - Linked to product
NumberInLocation - Number in this location
Location:
LocationID - Unique ID
LocationDescription - Name of location
I need to provide a table of:
ProductDescription, Number at Location ID1,Number at Location ID2,Number at Location IDn
[ :o/ my head hurts :o
-
was heavy on wine :-[ , now solving..
Cheers :y
BTW, has to be SQL, not SPs...
-
assuming all inner join and data existent for stock table at location table and you are using sql server
select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
(select llid=LocationID,LocationDescription from Location) as l
where p.pid=s.spid and s.slid=l.llid
OR
you can also drop to 2 tables if you need fewer fields as you stated
select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s , erase the comma :-[
where p.pid=s.spid
after you need to transpose the result on product description either in excel or using some code..
I can send you a generic .net vb code that I have written for transposition if you need..
reason is for the required transposition you need mdx code for olap which is a bit more complicated and instead I use vb.net..
-
assuming all inner join and data existent for stock table at location table and you are using sql server
select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
(select llid=LocationID,LocationDescription from Location) as l
where p.pid=s.spid and s.slid=l.llid
OR
you can also drop to 2 tables if you need fewer fields as you stated
select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
where p.pid=s.spid
after you need to transpose the result on product description either in excel or using some code..
I can send you a generic .net vb code that I have written for transposition if you need..
reason is for the required transposition you need mdx code for olap
;D i was just going to say that!
-
assuming all inner join and data existent for stock table at location table and you are using sql server
select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
(select llid=LocationID,LocationDescription from Location) as l
where p.pid=s.spid and s.slid=l.llid
OR
you can also drop to 2 tables if you need fewer fields as you stated
select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
where p.pid=s.spid
after you need to transpose the result on product description either in excel or using some code..
I can send you a generic .net vb code that I have written for transposition if you need..
reason is for the required transposition you need mdx code for olap
If an item does NOT exist at a location, it *may* either have a 0 in Numberatlocation, or its possible the record will not exist.
-
assuming all inner join and data existent for stock table at location table and you are using sql server
select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
(select llid=LocationID,LocationDescription from Location) as l
where p.pid=s.spid and s.slid=l.llid
OR
you can also drop to 2 tables if you need fewer fields as you stated
select p.ProductDescription,s.NumberinLocation, (also add extra you want) from
(select pid=ProductID,ProductDescription from Products) as p,
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s,
where p.pid=s.spid
after you need to transpose the result on product description either in excel or using some code..
I can send you a generic .net vb code that I have written for transposition if you need..
reason is for the required transposition you need mdx code for olap
If an item does NOT exist at a location, it *may* either have a 0 in Numberatlocation, or its possible the record will not exist.
select p.ProductDescription,s.NumberinLocation from
(select pid=ProductID,ProductDescription from Products) as p left outer join
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s
on p.pid=s.spid
I hope this will be helpful :y
-
No OLAP - this has to work in Access/Jet as well as MS-SQL.
Its a VB (VB6 :-[ - its old, and the .Net converter says NO!) app. Basically need to fill a (read only) Flexgrid:
Description Loc1 Loc2 Loc3 Loc4.... ....Loc X
Item1 0 0 1 1 0
Item2 1 4 2 0 0
Currently, I'm populating the Description (and price and other stuff that is unimportant at this point), then repeatedly going back to the DB to populate each Location - this is particularly unkind to the network, and especially to the DB server
-
No OLAP - this has to work in Access/Jet as well as MS-SQL.
Its a VB (VB6 :-[ - its old, and the .Net converter says NO!) app. Basically need to fill a (read only) Flexgrid:
Description Loc1 Loc2 Loc3 Loc4.... ....Loc X
Item1 0 0 1 1 0
Item2 1 4 2 0 0
Currently, I'm populating the Description (and price and other stuff that is unimportant at this point), then repeatedly going back to the DB to populate each Location - this is particularly unkind to the network, and especially to the DB server
ok then.. will check my code.. it can be converted to vb6 simply me thinks.. need some time.. :y
-
the record will exist but and its a big but it will be transposed too far down the numeric synergy to be recogniseable as even a distinct binary code,olap the uplink and restat the product address and then i do believe a restart will sort you out :y
-
the record will exist but and its a big but it will be transposed too far down the numeric synergy to be recogniseable as even a distinct binary code,olap the uplink and restat the product address and then i do believe a restart will sort you out :y
That makes more sense than the mgmt rubbish I have to listen to all day long ;D
-
initial step
for x -axis
(will not stress the network as it will work locally on server and only return the y-axis values for numberinlocation)
select distinct NumberinLocation from (
select p.ProductDescription,s.NumberinLocation from
(select pid=ProductID,ProductDescription from Products) as p left outer join
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s
on p.pid=s.spid ) as x order by ..
now process the results from left to right in a grid with a loop.. or better place in a single dimension array..
edit : forgot that one and corrected the axis
for y-axis
select distinct ProductDescription from (
select p.ProductDescription,s.NumberinLocation from
(select pid=ProductID,ProductDescription from Products) as p left outer join
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s
on p.pid=s.spid ) as x order by ..
this also on a single dimension array
-
process the main query once and assign into a 2d array (matrix)
txt="select p.ProductDescription,s.NumberinLocation from
(select pid=ProductID,ProductDescription from Products) as p left outer join
(select spid=ProductID,slid=LocationID,NumberinLocation from Stock) as s
on p.pid=s.spid "
read from database query txt in a loop like
txt = "select ............."
Set rs = New ADODB.Recordset
rs.Open txt, adoconnection 'that you define and connect through
index=0
While rs.EOF = False ' rs is the recordset
index=index+1
arry(index,1)= rs!ProductDescription
arry(index,2)= rs!NumberinLocation
rs.MoveNext
Wend
-
now iterate through the matrix for every item and Numberinlocation and write the results in the grid
so you will need no network access..
for .....
for ...
next
next ..
etc..
I used this technique of buffering and using arrays in a bank that had more than thousand branches for calculating daily sums and it was unbelievably fast and practical.. :y
and faster than olap :) (or I'm bad in olap :-[)
sorry cant give the actual vb6 code as the .net code was fairly long and it was generic on any type of table with any kind of fields..
-
Here endeth the sequel sequel? ;)
-
Here endeth the sequel sequel? ;)
yep..ended. if I wrote the actual code would be long enough but brain not performing well at the moment :P :-[
also there are other alternative solutions but too much alcohol in blood ;D
-
Cheers cem :y - I used your SQL, worked out how to manipulate into my flexgrid, seems to work a treat :y
Thank you very much :y
Expect a harder query next time (probably over the weekend) ;D ;D
-
Cheers cem :y - I used your SQL, worked out how to manipulate into my flexgrid, seems to work a treat :y
Thank you very much :y
Expect a harder query next time (probably over the weekend) ;D ;D
your are nearly passing my knowledge limits I hope I can solve next time :-[
Good night :y :y
-
Cheers cem :y - I used your SQL, worked out how to manipulate into my flexgrid, seems to work a treat :y
Thank you very much :y
Expect a harder query next time (probably over the weekend) ;D ;D
your are nearly passing my knowledge limits I hope I can solve next time :-[
Good night :y :y
Oh, its going to be a pig of a query. If you can even think you can get the data out with a single query, then you are a better man than me ;D
I need to look at the tables and relationships again before I can give you a clue ;D
-
Cheers cem :y - I used your SQL, worked out how to manipulate into my flexgrid, seems to work a treat :y
Thank you very much :y
Expect a harder query next time (probably over the weekend) ;D ;D
your are nearly passing my knowledge limits I hope I can solve next time :-[
Good night :y :y
Oh, its going to be a pig of a query. If you can even think you can get the data out with a single query, then you are a better man than me ;D
I need to look at the tables and relationships again before I can give you a clue ;D
I hope I can help..sometimes I use temporary tables to hold the calculated tables if I'm lazy to use matrix.. ;D
-
Cem, that query is now in production use in my Brother's shop :y :y
Ready for the next one? ;D
-
Cem, that query is now in production use in my Brother's shop :y :y
Ready for the next one? ;D
yep.. :y
-
I need a query that will get the total stock value (actual cost, and current retail).
Current retail is easy: = Qty in stock * current retail price.
Actual cost is more tricky, as it needs to ascertain how many in stock, then step back through invoices to work out actual cost paid for those items in stock - ie, last invoice may say £100 cost, previous invoice may say £90. So if there are 3 in stock, 2 were on last invoice, and 2 were on previous invoice, it needs to work out 2 x £100 + 1 x £90. If that makes sense?
Tables:
Products
ProductID - unique key
Description - description of product
RetailPrice - current retail price
CostPrice - current cost price (but not used in this query?)
StockLevel
StockLevelID - unique key
BranchID - link to branches (query needs to simply work for all branches added together)
UnitsInStock - integer for how many in stock at branchid
Invoices:
InvoiceID - unique key
ProductID - link to products
Quantity - integer for number of this item recieved
CostPrice - price for each item on this invoice
Does that make sense?
-
Would it help if I sent you an blank Access DB to show the DB layout?
-
I need a query that will get the total stock value (actual cost, and current retail).
Current retail is easy: = Qty in stock * current retail price.
Actual cost is more tricky, as it needs to ascertain how many in stock, then step back through invoices to work out actual cost paid for those items in stock - ie, last invoice may say £100 cost, previous invoice may say £90. So if there are 3 in stock, 2 were on last invoice, and 2 were on previous invoice, it needs to work out 2 x £100 + 1 x £90. If that makes sense?
Tables:
Products
ProductID - unique key
Description - description of product
RetailPrice - current retail price
CostPrice - current cost price (but not used in this query?)
StockLevel
StockLevelID - unique key
BranchID - link to branches (query needs to simply work for all branches added together)
UnitsInStock - integer for how many in stock at branchid
Invoices:
InvoiceID - unique key
ProductID - link to products
Quantity - integer for number of this item recieved
CostPrice - price for each item on this invoice
Does that make sense?
yep.. will try now.. :y
-
I see one problem, there must be product ID field
in stocklevel table or I cant sum up .. probably you forgot to write :-/
-
Would it help if I sent you an blank Access DB to show the DB layout?
nope not necessary.. only field and table names are adequate..
-
I see one problem, there must be product ID field
in stocklevel table or I cant sum up .. probably you forgot to write :-/
Yeah, my bad, there is a ProductID in table StockLevel...
-
I see one problem, there must be product ID field
in stocklevel table or I cant sum up .. probably you forgot to write :-/
Yeah, my bad, there is a ProductID in table StockLevel...
now the simple one currentretail calculation
assuming you dont want calculate per branch instead global otherwise we must use Branchid in group by
select p.ppid,p.Description,p.Retailprice,curretail=p.Retailprice * s.cumstockforprod from
(select ppid=ProductID,Description,RetailPrice,CostPrice from Products) as p,
(select spid=ProductID,cumstockforprod=sum(UnitsInstock) from StockLevel group by ProductID) as s
on p.ppid=s.spid
and now solving the other..
-
Blimey, Jamie........you fluent in upper mongolian too???
I understand none of this!!
-
for calculating exact values I need another table
which defines for a product from which price how many has been sold to design the query..
I have experienced same problem in the past so I hold
the sold numbers and their prices in another seperate table.. :-/
as actually for a same product if the price is different its something different ..
otherwise it will be approximative..
but if not available I can use some averaging methods..
-
ok checking another method..first in first out-fifo
-
as the invoice id is an incremental number
we can use that.. but you will also need to code in vb6..preparing..
-
Here is :y
ps: all connections must be opened simultaneously
like
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
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 Quantity,CostPrice from Invoices where ProductID=" & rs1!ProductID & " order by InvoiceID desc" ' this part is critical
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
-
I'm awfully glad I didn't respond to the original post. It's not really an SQL problem.
Fairly simple to solve in PL/SQL, but you haven't got Oracle. Pity about that. :)
-
SQL 2000 - 2005 also have extra properties that I did not use..and remember stored procedures are not allowed..
-
got halfway through this post and fell asleep!!!
if you fellas have to work this out daily then i dont envy any of you, my life is much simpler even with all the car problems we get!! all i do is undo nuts and bolts and repair and paint body panels, life is for enjoying!! lolol clever chaps on here!!!
-
got halfway through this post and fell asleep!!!
if you fellas have to work this out daily then i dont envy any of you, my life is much simpler even with all the car problems we get!! all i do is undo nuts and bolts and repair and paint body panels, life is for enjoying!! lolol clever chaps on here!!!
unfortunately yes.. and sometimes you are expected to write more than a thousand lines per day during development period.. :(
and agree your idea.. life is for enjoying :y
for my next life I hope I can remember that :'(
-
got halfway through this post and fell asleep!!!
if you fellas have to work this out daily then i dont envy any of you, my life is much simpler even with all the car problems we get!! all i do is undo nuts and bolts and repair and paint body panels, life is for enjoying!! lolol clever chaps on here!!!
unfortunately yes.. and sometimes you are expected to write more than a thousand lines per day during development period.. :(
and agree your idea.. life is for enjoying :y
for my next life I hope I can remember that :'(
hope so to Cem, what you do would give me a major headache everyday, I take my hat off to you :y
-
Jaime , in sql 2005 this transposition or pivoting is a bit easier.. :y
http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx
-
got halfway through this post and fell asleep!!!
if you fellas have to work this out daily then i dont envy any of you, my life is much simpler even with all the car problems we get!! all i do is undo nuts and bolts and repair and paint body panels, life is for enjoying!! lolol clever chaps on here!!!
unfortunately yes.. and sometimes you are expected to write more than a thousand lines per day during development period.. :(
and agree your idea.. life is for enjoying :y
for my next life I hope I can remember that :'(
hope so to Cem, what you do would give me a major headache everyday, I take my hat off to you :y
Thanks Omegatoy :y :y :y :y
-
Cem - as there is replication going on, we cannot guarantee that InvoiceID is in order. There is a date field in Invoices though.
Actually, I may have given you misinformation about tables....
Invoice:
InvoiceID
InvoiceDate
InvoiceDetails:
InvoiceDetailID
InvoiceID
ProductID
CostPrice
Quantity
(tables have other stuff, doubt relevent though)
We can assume proper stock rotation, so that those left in stock are the most recently invoiced.
-
Jaime , in sql 2005 this transposition or pivoting is a bit easier.. :y
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 :(
-
a bit busy day sorry, will arrange the code accordingly the first time I'm available :y
-
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..
-
Jaime , in sql 2005 this transposition or pivoting is a bit easier.. :y
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.. :y
-
No hurry, can't code this in for a few days anyway....
-
for pivoting I'll prepare the tables and some data
cant write it without testing..
-
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
-
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..
(http://i181.photobucket.com/albums/x80/mecdv6/productcount.jpg)
-
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!!!!
-
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 :D ;D