Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

visual basic 6 database calculation solution

I have a database that looks like this


item qty price

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


what i am looking for is a code that will do the following

I need it to pull 4 tooth brushes and eliminate one record so it would look like this

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


and lets say I wanted 3 it would look like
tooth brush      2      1.25
hair brush        2       3.25
paste               1      .99

basically look at each record and pull out stock until qty need is met.

and to save time no this is not homework.
this is in visual basic 6.

I am new to this and have no idea where to start.


0
peanut1010
Asked:
peanut1010
  • 5
  • 4
  • 3
  • +3
1 Solution
 
SNilssonCommented:

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.
0
 
DhaestCommented:
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
0
 
SNilssonCommented:

That is if you are assuming there is a ID, as now the table only hold three columns.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DhaestCommented:
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 ?)
0
 
venuraibCommented:
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>
 








0
 
DhaestCommented:
To venuraib

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

http://www.experts-exchange.com/Programming/help.jsp#hi99
0
 
peanut1010Author Commented:
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).

0
 
peanut1010Author Commented:
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.


0
 
SNilssonCommented:
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
0
 
bear23Commented:
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


0
 
SNilssonCommented:

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.
0
 
peanut1010Author Commented:
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.


0
 
peanut1010Author Commented:
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....
0
 
peanut1010Author Commented:
anyone

0
 
PhoenixricCommented:
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      
0
 
PhoenixricCommented:
I apologized if Its happend that I have posted an un-finished comment. Someone has just click the Submit button when I stood to answer the phone. . . Very bad. .


Lets continue. 'I assumed you already have a knowledge in DAO coding or if new to that better used a data control or an ADO data control

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='" & cItem & "' "
                  data1.Recordsource = cSQL
                  if Not data1.Recordset.EOF then
                     Do until data1.Recordset.EOF
                         if  data1.Recordset![Qty] <= cItem Then
                             cItem =  cItem - data1.Recordset![Qty]
                             data1.Recordset.Edit   ' Or either Delete this record if you really choose to.
                             data1.Recordset![Qty] = 0
                             data1.Recordset.Update
                         Elseif  data1.Recordset![Qty] => cItem Then                            
                             data1.Recordset.Edit
                             data1.Recordset![Qty] = data1.Recordset![Qty] - cItem
                             data1.Recordset.Update
                             cItem =  0
                         End If
                         if  cItem = 0 then
                             Exit Do
                         End If
                     data1.Recordset.Movenext
                     Loop
                     Msgbox "All Done"
                  else
                        Msgbox "No matching items found!"
                  End if  
     Else
          Msgbox "Please Enter a correct value"
     end if
End Sub      

Hope this one helps. .  get back to me later if stil have problems. Gud luck.
0
 
PhoenixricCommented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now