Solved

visual basic 6 database calculation solution

Posted on 2004-03-25
18
225 Views
Last Modified: 2013-12-04
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
Comment
Question by:peanut1010
  • 5
  • 4
  • 3
  • +3
18 Comments
 
LVL 8

Expert Comment

by:SNilsson
Comment Utility

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
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
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
 
LVL 8

Expert Comment

by:SNilsson
Comment Utility

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

Expert Comment

by:Dhaest
Comment Utility
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
 

Expert Comment

by:venuraib
Comment Utility
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
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
To venuraib

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

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

Author Comment

by:peanut1010
Comment Utility
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
 

Author Comment

by:peanut1010
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 8

Expert Comment

by:SNilsson
Comment Utility
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
 

Expert Comment

by:bear23
Comment Utility
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
 
LVL 8

Expert Comment

by:SNilsson
Comment Utility

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
 

Author Comment

by:peanut1010
Comment Utility
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
 

Author Comment

by:peanut1010
Comment Utility
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
 

Author Comment

by:peanut1010
Comment Utility
anyone

0
 
LVL 3

Expert Comment

by:Phoenixric
Comment Utility
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
 
LVL 3

Accepted Solution

by:
Phoenixric earned 50 total points
Comment Utility
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
 
LVL 3

Expert Comment

by:Phoenixric
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This article will show, step by step, how to integrate R code into a R Sweave document
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now