visual basic 6 database calculation solution

Why does your data base hold two instances of 'tooth brush' in the first place, if you are going to use it I would clean up the db logic first.

My first question for you is: Why do you have 2 records with tooth brush ? If you had only one, it's much easier to perform the task.
Then you could make a vb-project with the wizard from vb, which will make a complete project for you, so you can change the values on your screen.
In that case there is just a update to perform the task. Now it's very complex and not easy to do.

A small possible example. Remember that you have to set the correct names and variables. This is just a small example of how you could try it.

extract 4, "Tooth brush"

Private Sub Extract(intToDelete as integer, strProduct as string)
 dim rs as adodb.recordset
 set rs = YourConnection.execute "select ID, item,qty,price from YourTable where item ='" & product & "'"
 while rs.eof = false and intToDelete > 0
     intQtyRecord = rs.fields("qty")
     if intToDelete < intQtyRecord then
         YourConnection.execute = "update YourTable set qty = " & intQtyRecord - intToDelete & " where ID = " & rs.fields("id")
         intToDelete = 0
     elseif intToDelete = intQtyRecord then
        YourConnection.exectue = "delete YourTable  " where ID = " & rs.fields("id")
        intToDelete = 0
     else
        intToDelete = intToDelete - intQtyRecord
        YourConnection.exectue = "delete YourTable  " where ID = " & rs.fields("id")
        rs.movenext
     end if
  wend
  if intToDelete > 0 then
     msgbox "Not enough " & product
  end if
end Sub

That is if you are assuming there is a ID, as now the table only hold three columns.

Yes, I know, but if he hasn't got any ID in his table, then it's badly modelled (in any case, because why have 2 records for a tooth brush ?)

Hi Why don't you do it straight away with SQL

If your table is like this

Product      Qty           Price
xxxxxx        xx             xxxx.xxx

write a SQL like this

select Product ,count(Qty)
from ProductTbl
group by Product

then you know the available qty
again sort your product by product name then by Qty
like this

select Product ,Qty
from ProductTbl
group by Product, qty desc

then gets the first record ans see whats the qty is
like that you can take out your product

Hope this will work for you

Thank you
If you are intresting on this solution contact me  <e-mail edited out by CetusMOD>
 

To venuraib

It's against the EE-guidelines to post your email-adress !!!

https://www.experts-exchange.com/Programming/help.jsp#hi99

the reason for multiple records is because they are in multiple locations for storage. Because of how much can be stored in one location.

So I have to keep it that way.

I am going or try with your help to have a code that will tell me the locations and how much to take from each. that is why I need to erase the quantiy after(record).

Dhaest, I am lost with your code where or how does it know how many I need


I figure that product was what I wanted so I did this

product = Tooth

but I am lost with the deletion of used product

Just in case I got anyone confussed.

I have multiple locations for the same products. I want ot be able to enter the product and qty of what I want and then I need the code to look for the product and start gathering what I need and eliminating the record if needed or leaving what is remaining until my qty is met.

Ex.

table; each line is a record
tooth brush      2      1.25
hair brush        2       3.25
tooth brush      3       1.25
paste               1      .99


I want 4 tooth brush. so the code would look for the record with the most(I just decided on that,if too hard don't need) now since there is only three it will take the 3 and delete the record since they are all gone. Now I need 1 more so I look to see if I have any more if I don't then state no more. There seems to me 2 more so I more to that record and substract 1 and now in that location I have 1 more tooth brush left so it would update that and now my total is 4 so the code would say done.

and the table data would look like this
tooth brush      1     1.25
hair brush        2       3.25
paste               1      .99

I don't know if this helps or not I tried to break it down to make it easier to understand. It is a really confussing thing.

First get your the sum of the selected item:

Select item, Sum(qty),  price From ProductTbl where item = 'tooth brush'
Group by item, price

This will give you the total of the item 'tooth brush' save this as a variable lets say X.
Save price and Item name in Y and Z

Next delete all tooth brush:

Delete from ProductTbl where item = 'tooth brush'

Then:
Take the 4 - the variable X this will leave you with one (X = X - 4 )

INSERT ProductTbl VALUES (Z, X, Y)

There you go

the problem with that is you deleting all records of tooth brush


reason why is lets say the table looks like this

tooth brush      2      1.25
hair brush        2       3.25
tooth brush      3       1.25
paste               1      .99
tooth brush      2      1.25


----tooth brushes all together would be 7
----I need 4
----so in would go to the location that has 3, then deletes that because first there is no more and second we still need 1 more
----so the code can either go to 1 or the other location because both locations has 2 in it. so I just select the first record and since I just need 1 the I will substract from the first record and result would be tooth brush   1     1.25
ending result would look like this:


tooth brush      1      1.25
hair brush        2       3.25
paste               1      .99
tooth brush      2      1.25

---your way would be like this

hair brush        2       3.25
paste               1      .99
tooth brush      3    1.25

won't work right, do you understand. has to remain in the same location.....or record

Unless there are more columns than the three stated or if price varies, I see no reason why they cant be summarized in one record.

yes there are reason. there is another column or field that states the location in the store storage area. If you summarize it into one then the employee will not know where the products are. It has to be as stated before.

thanks bear for clearing things up. I was not sure if I did a good enough job or not.

For those here, bear and I work together and we are both trying to solve this problem....

anyone

Hi peanut1010,
   I would suggest if you can normalized your database, cause your database is the soul foundation of your programming later on. .  So its a good practice in database manipulation to have your database normalized by having a Unique Key in the primary table with the same key in another table which you can say have a relationship. This would be easier to manipulate. .  Anyway, for now I think you'll be sticking for what you already have , the database structure and contents abcourse. . :-)

Lets have a clearer visualization of your database content :
I undertstand that you have a table with fields like the following :
Lets say the table name is TR_Item (TR means transaction table)

Item                Qty    Price     Location
tooth brush      2      1.25       Store 1
hair brush        2       3.25      Store 1
tooth brush      3       1.25      Store 2
paste               1      .99        Store 2
tooth brush      2      1.25       Store 3

First question in mind? why do you have to delete the record if it is sold out? Why not edit the record anyway. It is easier to the employee to see the record sold out (qty=0) . Unless you have another table to look up for updates after each edit and delete. . .

Secondly? how do you maintain a proportion product description? e.g. tooth brush. .  Have you bound it in a combo box or a text box which cannot be modified once entered by the user in your add,edit delete item maintanance form? Cause if its not the case, you soon to have problems. . . otherwise you have set the cascade update and cascade delete in the database structure irself.

Using the table above , lets say I'll need to pick 4 tooth brush
' I dont have an idea with your user interface, but lets use one command button for this
'But much better if you use a bound combo box for the item selection

Private Sub Command1_Click()
     Dim cSQL as String, _
           cItem as String
     Dim nQty as Integer
       
      cItem = InputBox("Please enter an exact item description")
      nQty = InputBox("Qty Please!")
     if cItem <> "" And nQty > 0 then
        cSQL ="SELECT * FROM TR_Item" & _
                  "WHERE Item='" & 
     Else

     end if
End Sub      

Pls bear with me, used nQty instead of cItem starting from the do loop body, sorry for overlooking. I used to write the code directly to this applet . . A little busy this day. .  Thanks.