LINK PRODUCTID TO TRANSACTION BASED ON QTY

[rookiedev]

I AM USING THE INVENTORY CONTROL PROGRAM IN MICROSOFT 97. WHAT I AM ATTEMPTING TO DO IS ALLOW USERS TO SIGN OUT PRODUCTS USING A FORM AND THE VALUE DEDUCT FROM THE QUANTITY. HERE IS THE SET UP.
THE FORM USED TO SIGN THE PRODUCTS OUT USES A COMBO BOX TO ALLOW THE USER TO SELECT THE PRODUCT AND THE DESCRIPTION AND PRICE IS AUTOMATICALLY DISPLAYED.THIS IS BASED ON THE PRODUCTS TABLE.
THE QUANTITIES RECIEVED IS IN THE TRANSACTIONS TABLE. HERE IS WHAT THE TRANSACTIONS TABLE LOOKS LIKE.

Date # Ordered # Received# Sold Unit Price:
10/29/2001 25 25 12 $2.60
9/21/2000 15 15 5 $2.59

I NEED THE VALUE BEING SIGNED OUT TO DEDUCT FROM THE INVENTORY BY ADDING TO THE NUMBER SOLD AND IF THERE ARE NOT ENOUGH TO DEDUCT FROM THAT TRANSACTION-GO TO THE NEXT TRANSACTION.
I HAVE ASKED FOR HELP DOING THIS BEFORE AND GOTTEN VERY GENERAL ADVICE. UNFORTUNATELY I DO NOT HAVE ANY EXPERIENCE WITH LOOPING THROUGH RECORDS AND SO I WAS UNABLE TO DO ANYTHING PRODUCTIVE. THERE MUST BE A WAY FOR THE VALUE OF 5 SOLD TO BE UPDATED WHEN 10 MORE ARE USED THEN DISPLAY THE NUMBER 15. IS THERE NOT????
ANY HELP WOULD BE APPRECIATED.
ROOKIE DEV

 

[PWise]

how did the qty 12 and 5 get there in the first place
and what happand to the other 10 from 9/21/2000

 

[rookiedev]

Hi pwise-
What happeneds now is that the craftsmen use a different database that is linked to the Products table and they select the products they are using on a job and enter the quantity they are taking. Our warehouse manager then runs a report from this database that tells him what has been used.
He uses this report to manually enter the number used. If someone uses a widget...he opens the Inventory database and goes to the product widget and would then add the number used to the sold field. If we have already used or "sold" 5 he would add the one widget sold and type in the number 6.

We have an $800,000 inventory and 35-40 people taking products out of the warehouse every day so you can see that this is a very time consuming endevor. I badly need to figure out how to automate this process.
Here is the catch....Everything that I have tried has failed because to automate the signing out of the product it somehow has to be linked to a specific transaction. When it is on the shelf there is no way that I am aware of to make that information available. If I have purchased 2000 screws that are in a bin there is no way to identify that 1000 of them were purchased under one transaction and the other 1000 under another.

I used the database wizard in Access 97 to start this project if you think taking a look at the way that is set up will help.

I have explained this quandry several times before and it seem as if it is just to much for people to wrap there minds around after I explain it. I usually do not get any further assistance after that. I appreciate your response and any help you can give me.

Grateful..
Rookie Dev

 

[PWise]

if you want this automated and wroking right this information should be broken into 5 tables
item table
itemid itemname
ordered table
orderid po# item qty price
recived table
recevid po# item qty
sales table
salesid itemid qty price
distribution table
distid salesid itemid qty recivedid
the form for ordering is prety stragh forward enter a new record
orderid item qty price
auto# item# from item tbl xxxx xxx.xx
to form for reciveing if you know on what po you recived to add a record all you need to check in code is if you have that item on that po
recivedid po# item qty
auto# orderid itemid xxx
now for the sale form
your sales id will be auto genareted
all you have to enter is item and qty
and in code
put qty from varible
1)select witche reciver layers have not been sold
out
2)do till tour sale is complety distribtute
check if amount not distribute less or equle to top open reciver
add to distribution table
distid salesid itemid qty recivedid
auto# auto# from form form form from var from select
exit sub
if more then top open reciver
add to distribution table
distid salesid itemid qty recivedid
auto# auto# from form form form not dist from select
subtract qty not dist from var
move to next reciver and
loop
create queries
sum sales by recivers

sum recivers by orders and join to sum sales

with order tables and join to sum recivers

hope this helps

 

[rookiedev]

pwise-
WOW,flew right by me with that explaination. Can you help me in stages?
Are you saying that in addition to the Products Table and the Transactions table I need to set up an additional 5 tables?
Tell me if I am understanding the first part of what you wrote:
The Products table should take the place of the Item Table right? It already contains the ProductId and ProductName fields along with additional information.

The Transactions table should take the place of the Order Table?
It already contains the TransactionId or "orderid" po#,ProductId or "item", Quantity or "qty", UnitPrice or "pricefields" along with additional information.

If the Transactions table already contains the Recieved information do I still need the additional table?

Thanks,
RookieDev


If I am following what you are saying I need to create an Item Table that just contains the ItemId and ItemName fields

 

[PWise]

you have to take the reciveing information out of the order table and make a new table except if you never have more then receving on 1 po line

 

[rookiedev]

Ok!
Can I do this using a make table query or something? I have an outrageous number of POs and items at this point.

Rookie Dev