Solved

visual basic 6 database calculation solution

Posted on 2004-03-25
225 Views
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
Question by:peanut1010
• 5
• 4
• 3
• +3

LVL 8

Expert Comment

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

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)
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

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

LVL 53

Expert Comment

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

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

To venuraib

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

Author Comment

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

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

LVL 8

Expert Comment

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

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

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

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

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

anyone

0

LVL 3

Expert Comment

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")
if cItem <> "" And nQty > 0 then
cSQL ="SELECT * FROM TR_Item" & _
"WHERE Item='" &
Else

end if
End Sub
0

LVL 3

Accepted Solution

Phoenixric earned 50 total points
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")
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

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

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 …