[RESOLVED] Stock Balance

[muzamilsumra]

Hi Guys,
I have 2 tables.

Table 1 - tblProducts

PID	PARTNO	DESCRIPTION	BRAND	OPSTOCK	DATECREATED
1	00001	Test1	Brand1	1000	1/1/2015
2	00002	Test2	Brand2	500	1/1/2015

Table 2 - tblSales

SID	PID	PARTNO	DESCRIPTION	BRAND	QTY	SALESDATE
1	1	00001	Test1	Brand1	100	2/1/2015
1	1	00001	Test1	Brand1	250	3/1/2015

My only question is how do i attain the balance as on a particular date of both the items and show them on a datagrid using sql statement.

if i choose the date 1/1/2015 then the balance on Test1 should be 1000 and Test2 should be 500
if i choose the date 2/1/2015 then the balance on Test1 should be 900 and Test2 should be 500
if i choose the date 3/1/2015 then the balance on Test1 should be 650 and Test2 should be 500 and so on..

I am having a problem with this because there is no sales for Product 2 in the sales table the sql query that i try to write gives me some null error.

Any help should be appreciated.

[SamOscarBrown]

when you do your query and try to return a value and you are getting a 'null error', include in your display of the value using an IsNull statement, like:

If NOT isNull(rs!QTY) then '
    display the value.....
end if

[DataMiser]

Can't say what problem you are having without seeing the code you are using

[muzamilsumra]

ok this is the actual project code that i am doing and this is how it goes..

I am sorry for not using line breaks in the long sentences as i am unable to do so.

This code displays the results in a MSFLEXGRID.

What i would like to know is...is there any better way to do this and what are the flaws in this code?

'Select everything from the products Table
SqlProducts = "select opstock from products where datecreated <='" & Calendar2.Month & "/" & Calendar2.Day & "/" & Calendar2.Year & "'" ' where productid='" & txtPID.Text & "'"
Set ProductsRS = cn.Execute(SqlProducts)

'always revert the rows of the flexgrid back to 1
MSFlexGrid1.Rows = 1

'increment the rows in the flexgrid to the number of rows available in the productsrs
MSFlexGrid1.Rows = MSFlexGrid1.Rows + ProductsRS.RecordCount

For i = 1 To ProductsRS.RecordCount
    'Do the total of all the items sold starting from when they were created until the date chosen in the calendar
    SqlCS = "select sum(qty) from tblcs where cashsaledate <='" & Calendar2.Month & "/" & Calendar2.Day & "/" & Calendar2.Year & "' and productid='" & i & "'"
    Set CSRS = cn.Execute(SqlCS)
        
        If Not IsNull(CSRS(0).Value) Then
        SqlProducts = "select Partno,description,brand,opstock from products where productid ='" & i & "'"
        Set ProductsRS = cn.Execute(SqlProducts)
        'Productid
        MSFlexGrid1.TextMatrix(i, 1) = i
        'PartNo
        MSFlexGrid1.TextMatrix(i, 2) = ProductsRS(0).Value
        'Description
        MSFlexGrid1.TextMatrix(i, 3) = ProductsRS(1).Value
        'Brand
        MSFlexGrid1.TextMatrix(i, 4) = ProductsRS(2).Value
        'Stock Balance when some sales have been made
        MSFlexGrid1.TextMatrix(i, 5) = Val(ProductsRS(3).Value - CSRS(0).Value)
        Else
        SqlProducts = "select Partno,description,brand,opstock from products where productid ='" & i & "'"
        Set ProductsRS = cn.Execute(SqlProducts)
        'productid
        MSFlexGrid1.TextMatrix(i, 1) = i
        'PartNo
        MSFlexGrid1.TextMatrix(i, 2) = ProductsRS(0).Value
        'Description
        MSFlexGrid1.TextMatrix(i, 3) = ProductsRS(1).Value
        'Brand
        MSFlexGrid1.TextMatrix(i, 4) = ProductsRS(2).Value
        'Takes the oppening stock balance when the item was created
        MSFlexGrid1.TextMatrix(i, 5) = ProductsRS(3).Value
        End If
Next

[SamOscarBrown]

I've go to head off to a location today without access to VB6....if not solved by this evening, I'll suggest a better way.

But, at first glance, you do not need to do a separate query for getting the number of rows for your grid. You can simply use

msflexgrid1.rows = msflexgrid1.rows + 1

as you go through your recordset populating the grid.

Secondly, unless you have a header row, you'd want to start off with msflexgrid1.rows = 0, not 1.

Also, if any of your values being returned in your last query are null, you probably need the NOT IsNull() statement where you assign those values to your grid cells.

Again, as Arnold Schwarzenegger says: "I'll be back," If someone has solved this for you by then, great, otherwise I'll give you an example.

[DataMiser]

The code really doesn't make much sense to me.

My biggest question is why are you trying to adjust the stock value in such a manner. Typically a program should adjust the stock value as items are sold so that the data in the table reflects the actual qty that is in stock at any given time.

[Phill.W]

My question would be - why are you breaking fundamental rules of database design and duplicating data all over the place?

Table 1 - tblProducts
+-----+--------+-------------+--------+---------+-------------+ 
| PID | PARTNO | DESCRIPTION | BRAND  | OPSTOCK | DATECREATED | 
+-----+--------+-------------+--------+---------+-------------+ 
|   1 |      1 | Test1       | Brand1 |    1000 | 1/1/2015    | 
|   2 |      2 | Test2       | Brand2 |     500 | 1/1/2015    | 
+-----+--------+-------------+--------+---------+-------------+ 

Table 2 - tblSales
+-----+-----+--------+-------------+--------+-----+-----------+ 
| SID | PID | PARTNO | DESCRIPTION | BRAND  | QTY | SALESDATE | 
+-----+-----+--------+-------------+--------+-----+-----------+ 
|   1 |   1 |      1 | Test1       | Brand1 | 100 | 2/1/2015  | 
|   1 |   1 |      1 | Test1       | Brand1 | 250 | 3/1/2015  | 
+-----+-----+--------+-------------+--------+-----+-----------+

Your Sales table needs to know what was sold, when and how much of it. The "when" and "how much" are correctly held in the SALESDATE and QTY fields. The "What" only needs to identify a Product, i.e. you need to hold the [Primary] Key of the relevant products record.

I'm puzzled as to the [Primary] Key on your Sales table.
If SID represents an individual Sale, then how can Sale #1 occur on two different dates?
If SID represents, say, a Salesperson, then it makes more sense, but only in combination with the Date of that sale.

Table 2 - tblSales
+-----+-----+-----+-----------+ 
| SID | PID | QTY | SALESDATE | 
+-----+-----+-----+-----------+ 
|   1 |   1 | 100 | 2/1/2015  | 
|   1 |   1 | 250 | 3/1/2015  | 
+-----+-----+-----+-----------+

This isn't being picky or pedantic; these really are fundamental database concepts that you need to get right, or you're going to hit problem after problem as your database grows.

Regards, Phill W.

[muzamilsumra]

My question would be - why are you breaking fundamental rules of database design and duplicating data all over the place?

Table 1 - tblProducts
+-----+--------+-------------+--------+---------+-------------+ 
| PID | PARTNO | DESCRIPTION | BRAND  | OPSTOCK | DATECREATED | 
+-----+--------+-------------+--------+---------+-------------+ 
|   1 |      1 | Test1       | Brand1 |    1000 | 1/1/2015    | 
|   2 |      2 | Test2       | Brand2 |     500 | 1/1/2015    | 
+-----+--------+-------------+--------+---------+-------------+ 

Table 2 - tblSales
+-----+-----+--------+-------------+--------+-----+-----------+ 
| SID | PID | PARTNO | DESCRIPTION | BRAND  | QTY | SALESDATE | 
+-----+-----+--------+-------------+--------+-----+-----------+ 
|   1 |   1 |      1 | Test1       | Brand1 | 100 | 2/1/2015  | 
|   1 |   1 |      1 | Test1       | Brand1 | 250 | 3/1/2015  | 
+-----+-----+--------+-------------+--------+-----+-----------+

Your Sales table needs to know what was sold, when and how much of it. The "when" and "how much" are correctly held in the SALESDATE and QTY fields. The "What" only needs to identify a Product, i.e. you need to hold the [Primary] Key of the relevant products record.

I'm puzzled as to the [Primary] Key on your Sales table.
If SID represents an individual Sale, then how can Sale #1 occur on two different dates?
If SID represents, say, a Salesperson, then it makes more sense, but only in combination with the Date of that sale.

Table 2 - tblSales
+-----+-----+-----+-----------+ 
| SID | PID | QTY | SALESDATE | 
+-----+-----+-----+-----------+ 
|   1 |   1 | 100 | 2/1/2015  | 
|   1 |   1 | 250 | 3/1/2015  | 
+-----+-----+-----+-----------+

This isn't being picky or pedantic; these really are fundamental database concepts that you need to get right, or you're going to hit problem after problem as your database grows.

Regards, Phill W.

Hi Phill,
for that part of the question i am sorry for the typing error, that is obviously a typing error from my side, that was just an example of what i wanted to do in my project. So the second SID should have been 2.

[muzamilsumra]

The code really doesn't make much sense to me.

My biggest question is why are you trying to adjust the stock value in such a manner. Typically a program should adjust the stock value as items are sold so that the data in the table reflects the actual qty that is in stock at any given time.

lol, that is exactly what i am unable to do MASTER, and so my code is going here and there...but the code that i have posted here above does actually work...i just want to know if there is a better way to do this...

The limitations in this code of mine are quite plenty i guess.

[DataMiser]

Well normally what you would do is when the sale takes place you adjust the inventory accordingly.

So if you sell 10 of item3 this morning at the time of the sale you update two tables. In one table you record the sale along with the qty sold and any other info that you might need and at that same time you reduce the amount of product in inventory by that amount. There is nothing complicated about it simply update Inventory Set QtyOnHand=QtyOnHand-SaleQty where ProductID=ProductSold

As for the code posted actually working... Does it really? I mean it displays a total on the screen but it doesn't update anything. Do you have a field in the db that holds the actual qty on hand or is the only way to know by calculating all the sales and subtracting from the opening balance each time?

[techgnome]

Well normally what you would do is when the sale takes place you adjust the inventory accordingly.

So if you sell 10 of item3 this morning at the time of the sale you update two tables. In one table you record the sale along with the qty sold and any other info that you might need and at that same time you reduce the amount of product in inventory by that amount. There is nothing complicated about it simply update Inventory Set QtyOnHand=QtyOnHand-SaleQty where ProductID=ProductSold

As for the code posted actually working... Does it really? I mean it displays a total on the screen but it doesn't update anything. Do you have a field in the db that holds the actual qty on hand or is the only way to know by calculating all the sales and subtracting from the opening balance each time?

That's debatable... it's fine as long as all you ever need to know is what's currently on hand. In order to find out how much you had on any given date, it becomes tricky because you have to back into it by putting back everything you sold in the meantime. Personally I prefer to treat it more like a ledger... you have pieces coming in (supply) and pieces going out (sales). Then to get the inventory count on any given day, you simply take the sum of all supplies prior to the request date, subtract the sum of sales prior to the date, and that gives you the inventory on any given date. Which is EXACTLY what the OP is looking for:

if i choose the date 1/1/2015 then the balance on Test1 should be 1000 and Test2 should be 500
if i choose the date 2/1/2015 then the balance on Test1 should be 900 and Test2 should be 500
if i choose the date 3/1/2015 then the balance on Test1 should be 650 and Test2 should be 500 and so on..

Unfortunately OpStock is just a field on the product, so we'll have to assume there haven't been any supplies added ...

select P.PARTNO, P.DESCRIPTION, P.BRAND, P.OPSTOCK, P.OPSTOCK - isnull(Q.SoldQty, 0) as RemainingStock
from tblProducts P
left join (select PartNo, sum(QTY) as SoldQty from tblSales group by PartNo) Q on P.PartNo = Q.PArtNo

Something like that/// the sub query needs to be adjusted to filter based on the date... but it's close enough.


-tg

[muzamilsumra]

That's debatable... it's fine as long as all you ever need to know is what's currently on hand. In order to find out how much you had on any given date, it becomes tricky because you have to back into it by putting back everything you sold in the meantime. Personally I prefer to treat it more like a ledger... you have pieces coming in (supply) and pieces going out (sales). Then to get the inventory count on any given day, you simply take the sum of all supplies prior to the request date, subtract the sum of sales prior to the date, and that gives you the inventory on any given date. Which is EXACTLY what the OP is looking for:


Unfortunately OpStock is just a field on the product, so we'll have to assume there haven't been any supplies added ...

select P.PARTNO, P.DESCRIPTION, P.BRAND, P.OPSTOCK, P.OPSTOCK - isnull(Q.SoldQty, 0) as RemainingStock
from tblProducts P
left join (select PartNo, sum(QTY) as SoldQty from tblSales group by PartNo) Q on P.PartNo = Q.PArtNo

Something like that/// the sub query needs to be adjusted to filter based on the date... but it's close enough.


-tg

Awesome brother,
This is what i was looking for, works perfectly so far..Thank you very much.. but would you mind explaining this part "isnull(Q.SoldQty, 0)"

[Phill.W]

... it's fine as long as all you ever need to know is what's currently on hand. In order to find out how much you had on any given date, it becomes tricky because you have to back into it by putting back everything you sold in the meantime.

Those are two completely discrete (and wildly differing) requirements and you cannot [sensibly] satisfy both from the same [set of] table[s]. The processing overheads would make your application excruciatingly slow. (Try throwing a couple of thousand rows into each of your your tables and see how well(?) your application performs).

For Transactional work (i.e. selling stuff), you need to know how much of everything you have now.

For historical queries (what you had when), you need to maintain a [set of] "history" table[s] (a.k.a. your "Ledger") that record[s] the state of things at any point in time. Making changes to the "real-time" tables should automatically update the "history" tables (through triggers) so that these changes are never lost.

Regards, Phill W.

[techgnome]

I don't disagree with most of that... but it is what the OP asked for initially... but to obtain it given the current structure... not going to be pretty that's for sure. HE's got two halves of different methods. Personally I'd go with a complete ledger style design. Once a month, inventory is taken, and you draw a line in the sand and write a transaction record that says "as of the first of the month, this is the inventory balance" ... then that's used as the baseline for all subsequent balance transactions. It's same as how we close out financial books. Not much different from balancing a checkbook each month.

But... presumably the OP can't change his structure, so it is what it is and has to be dealt with as such.

but would you mind explaining this part "isnull(Q.SoldQty, 0)"

Because the join is a left join - so that we get items that haven't been sold - their SoldQty will be null (because there are no matching sales) so that changes the NULL into a 0 value. IsNull is SQLServer, so you'll need to replace it with what ever IsNull function your database provides. I believe it is NZ for Access, IsNull also works in MySQL, not sure about any others.

-tg

[muzamilsumra]

I don't disagree with most of that... but it is what the OP asked for initially... but to obtain it given the current structure... not going to be pretty that's for sure. HE's got two halves of different methods. Personally I'd go with a complete ledger style design. Once a month, inventory is taken, and you draw a line in the sand and write a transaction record that says "as of the first of the month, this is the inventory balance" ... then that's used as the baseline for all subsequent balance transactions. It's same as how we close out financial books. Not much different from balancing a checkbook each month.

But... presumably the OP can't change his structure, so it is what it is and has to be dealt with as such.


Because the join is a left join - so that we get items that haven't been sold - their SoldQty will be null (because there are no matching sales) so that changes the NULL into a 0 value. IsNull is SQLServer, so you'll need to replace it with what ever IsNull function your database provides. I believe it is NZ for Access, IsNull also works in MySQL, not sure about any others.

-tg

Thx again for the clarification, now i understand that tht statement means "if soldqty is NULL then replace it with 0...UNDERSTOOD.

Ill try adding up some thousands of entries and see how the software performs...will get to you guys with the feedback.

[muzamilsumra]

Ok, So i imported 10,000 products in my products table, imported some 2500 sales entries in my tblcs table and some 4000 sales entries tblinv table. To my surprise the system is working as smooth as if it has no entries and giving me the correct balances as on certain dates exactly the way i want it to do.. for your information i am using SQL server 2008 for my project. I dont know if i am missing any point over here that you guys are trying to explain to me.

[SamOscarBrown]

Just for giggles, before you mark this Thread as resolved, how about posting your revised code...it MAY help others in the future, OR, others may still have suggestions on how to improve it even more....

Sammi

[techgnome]

I dont know if i am missing any point over here that you guys are trying to explain to me.

I don't think so. The discussion Phill and I are having is largely academic and relates to the architecture/design of the tables. I don't think either of us it 100% happy with it, but sometimes you gotta work with what you have.

-tg

[IkkeEnGij]

my 2 cents:
regarding the 2 systems proposed here,it does not have to be 1 or 2, it can be 1 and 2
i suppose OPSTOCK means OPENINGSTOCK (is that used for something ?)
add an INSTOCK field to the products table
and the 2 systems become 1, and can even be used to control each other

but the design ?

But... presumably the OP can't change his structure, so it is what it is and has to be dealt with as such.

come on,
simply dropping 3 columns of the sales table already is a big step
but considering the design as it is, it is my opinion that, of the 13 fields in the 2 tables
at least 5 are totally superfluous

muzamilsumra
regarding your code,
the use of a fields ordinal position is a very bad idea
suppose someone changes it ? (could even be by accident)

could you tell us what indexes/keys there are on the tables ?

[muzamilsumra]

my 2 cents:
regarding the 2 systems proposed here,it does not have to be 1 or 2, it can be 1 and 2
i suppose OPSTOCK means OPENINGSTOCK (is that used for something ?)
add an INSTOCK field to the products table
and the 2 systems become 1, and can even be used to control each other

but the design ?

come on,
simply dropping 3 columns of the sales table already is a big step
but considering the design as it is, it is my opinion that, of the 13 fields in the 2 tables
at least 5 are totally superfluous

muzamilsumra
regarding your code,
the use of a fields ordinal position is a very bad idea
suppose someone changes it ? (could even be by accident)

could you tell us what indexes/keys there are on the tables ?

Ok, The Products Table has a key as "PID" the tblCS table has a key as "CSID" and the tblInv table has a key as "INVID" which i have left them to be generated automatically.

[muzamilsumra]

Just for giggles, before you mark this Thread as resolved, how about posting your revised code...it MAY help others in the future, OR, others may still have suggestions on how to improve it even more....

Sammi

Dear Sam this is the code that i have used

This code shows all the products that are created after the selected date on the calendar and the stock available after deducting whatever is sold before the date selected.

SqlProducts = "select p.productid,p.partno,p.opstock,p.opstock - isnull(i.soldqty,0) - isnull(q.soldqty,0) as Stock,p.datecreated" & _
            " from (products p left join (select productid, sum(qty) as soldqty from tblcs where" & _
            " cashsaledate <='" & Calendar1.Month & "/" & Calendar1.Day & "/" & Calendar1.Year & "' group by productid)" & _
            " q on p.productid=q.productid)" & _
            " left join (select productid,sum(qty) as soldqty from tblinv where" & _
            " invoicedate <='" & Calendar1.Month & "/" & Calendar1.Day & "/" & Calendar1.Year & "' group by productid) i on p.productid=i.productid" & _
            " where p.datecreated <='" & Calendar1.Month & "/" & Calendar1.Day & "/" & Calendar1.Year & "'"
Debug.Print SqlProducts
Set ProductsRS = cn.Execute(SqlProducts)
Set DataGrid1.DataSource = ProductsRS

[IkkeEnGij]

Ok, The Products Table has a key as "PID" the tblCS table has a key as "CSID" and the tblInv table has a key as "INVID" which i have left them to be generated automatically.

since i dont see a table tblCS nor a table tblInv , i cannot comment on them
but since there is no index/key on PARTNO of table products,
there can be any number of duplicate PARTNO's in table Products ?

[muzamilsumra]

since i dont see a table tblCS nor a table tblInv , i cannot comment on them
but since there is no index/key on PARTNO of table products,
there can be any number of duplicate PARTNO's in table Products ?

Well in my first post i just gave a sample of what i wanted, that was not my actual project, my actual project was the long code that i posted

SqlProducts = "select p.productid,p.partno,p.opstock,p.opstock - isnull(i.soldqty,0) - isnull(q.soldqty,0) as Stock,p.datecreated" & _
            " from (products p left join (select productid, sum(qty) as soldqty from tblcs where" & _
            " cashsaledate <='" & Calendar1.Month & "/" & Calendar1.Day & "/" & Calendar1.Year & "' group by productid)" & _
            " q on p.productid=q.productid)" & _
            " left join (select productid,sum(qty) as soldqty from tblinv where" & _
            " invoicedate <='" & Calendar1.Month & "/" & Calendar1.Day & "/" & Calendar1.Year & "' group by productid) i on p.productid=i.productid" & _
            " where p.datecreated <='" & Calendar1.Month & "/" & Calendar1.Day & "/" & Calendar1.Year & "'"
Debug.Print SqlProducts
Set ProductsRS = cn.Execute(SqlProducts)
Set DataGrid1.DataSource = ProductsRS

This one includes everything, tblcs and tblinv too.

And as for the partno there is no index/key because the index/key is set to the productid. There can be as many duplicated part no's as possible.

[IkkeEnGij]

And as for the partno there is no index/key because the index/key is set to the productid. There can be as many duplicated part no's as possible.

do i understand that correctly ?
can several different products have the same partno ?
iow: can 1 partno have more than 1 productid ?
if so, then just what is partno ?

and just to see if we are talking about the same thing:
by productid you mean PID as in post #1, right ?
by partno you mean PARTNO as in post #1, right ?

[muzamilsumra]

do i understand that correctly ?
can several different products have the same partno ?
iow: can 1 partno have more than 1 productid ?
if so, then just what is partno ?

Well we can have the same kind of product with the same part no in different BRANDS. we just need to make its productid (PID) unique.

and just to see if we are talking about the same thing:
by productid you mean PID as in post #1, right ?

Yes you're right.

by partno you mean PARTNO as in post #1, right ?

Yes you're right.

[IkkeEnGij]

oh, ok, i see
but, if it is the same product, only from a different brand
should there then not be a table for the brands ?
with a 1 to many relationship between table 'Products' and table 'Brands' ?
or is the same product from different brands really not the very same product ?