Accumulating Items 1

[RobotMush]

Is there a way to keep a running total of items on hand. I have a problem that I have to keep track of what I Have on hand to what I have coming in and what I have going out.

A formula would be...
(New Items In)+(Items on hand)=(Total Items)

and
(Total Items)-(Items Sold)=(Items on Hand)

Having mushed my brains at this point, I am needing help in eather rethinking the formulas or how to setup the queries.

Any help would be greatly appreciated.

Thank You

RobotMush (Technical User)

 

[gol4]

the formulas would depend on what your table structure looks like

if item table looks like

itemid - datein - datesold ...

then items on hand = count of item id where datesold is null

 

[RobotMush]

My tables are set up as...

tblProduct
Product, Cost, Num - where Num is the Number on Hand

I have a form set up that is attached to the query that is set to the above table that allows me to add the Products as we get them in.

I also have the following tables
tblSold
Product, #Sold, Sale Price - where #Sold is the Number to be subtracted from the Num field in tblProduct

The queries are set up the same way as the tables
qryProduct
Product, Cost, Num
and
qrySold
Product, #Sold, Sales Price

Does this help?

RobotMush (Technical User)

 

[VBACT]

Declare a Static Variable and add to its value

 

[gol4]

I guess I am a bit unclear

are you running an update query to change Num - where Num is the Number on Hand each time you enter a record into
tblSold

Product, #Sold, Sale Price - where #Sold is the Number to be subtracted from the Num field in tblProduct

Is that what I am reading, or is that what you are wanting to do here?

 

[RobotMush]

VBACT
I think you are giving me the information I need to get the Program to do what I am wanting. However, I have had to study up on Static Variables as I have not heard of them. Now I'm needing to know where or how to use them.

I can guess that I would place it in my querie in a field. Using the AfterUpdate event I would have the program typed in to handle the updates.

I'll try this and if I'm typing in the wrong keys I'll be getting back to the drawing board on this.

gol4
I'm not using update or create queries as I'm pretty sure that I can VBA program the Database to do what I am needing to be done. I am just not sure as to how to get it done, If I'm going about it wrong or If it can be done.

Thanks for your help, both of you.

RobotMush (Techinical User)

 

[TheAceMan1]

RobotMush . . . . .

Can you give a sample listing of how it should look?

See Ya! . . . . . .

 

[RobotMush]

ACEMAN!!!! Glad you are willing to put your $1.95 in. (used to be 2 cents but due to inflation...)

Below is the SQL view of my Main Query, it has All the fields that I am working with at this time.


SELECT tblStartup.Product..List of products we deal with
tblStartup.[Prod Cost].....Full Cost of Product
tblStartup.ItmProd.........How many single Items in Product
tblStartup.ItmCst..........Prod Cost/ItmProd=ItmCst
tblStartup.[Row#]..........Row # Of Vending Machine
tblStartup.RowCnt..........How many items can be in Row
tblStartup.SPrice..........Selling Price of an Item
tblStartup.InVnd...........What I have in the Vending Mach.
tblStartup.Sold............How many Items have been sold
tblStartup.InStore.........What Items are left in Storage
tblStartup.GrossSale.......Sold*SPrice=GrossSale
tblStartup.NetSale.........(GrossSale-(ItmCst*Sold))=NetSale
FROM tblStartup;

From the above I am going to create the equations for ItmCst,GrossSale,and NetSale to place the results in the table fields of the above.

The main thing I am wanting to do is...When I take items from my storage to restock the vending machine, that it will automatically figure how many items I have left in storage.

I hope this give you sufficent food for thought.

Thanks for weighing in on this Ace.

RobotMush (Technical User)

 

[TheAceMan1]

RobotMush . . . . .

I wasn't clear . . .

A sample listing of what it would look like in the form or query, as far as the accumulation you speak of!

See Ya! . . . . . .

 

[RobotMush]

Oooopsie!

Ok, here is a query of what I am wanting to do. Up to a point.

SELECT
tblStartup.DatePurchase
tblStartup.DateSoldOut
tblStartup.Product
tblStartup.ItmProd
tblStartup.Restock
[ItmProd]-[Restock] AS InStore
FROM tblStartup;

The DatePurchase and DateSoldOut is to help me see if the Product is moving well or need to be changed out.

ItmProd is how many items are in the Product... I.E. A Box of 33 Items for the Product Purchased.

Restock is what I have taken out of the ItmProd. This gives me the InStore Calculated Field.

What I would like to do is that InStore value will replace the ItmProd next time I open the Database and therefore have the Restock Field being 0.

Do you understand?

RobotMush (Technical User)

 

[TheAceMan1]

RobotMush . . . . .

You should be able to do it with an [blue]Update[/blue] query.

Whats the [blue]PK[/blue] of tblStartUp?

See Ya! . . . . . .

 

[RobotMush]

Ace, after trying for about a week of setting up my tables in Access, I went to a Excel to create a spreadsheet of what I was wanting, then imported it to Access as the tblStartup.

Thanks for the heads up on an Update Query, I'll study up on it to find out what and how to make it work.

RobotMush (Technical User)

 

[TheAceMan1]

RobotMush . . . . .

[green]All good things in their time too![/green]. I didn't fully understand what you wanted until the end.

Here's the [purple]SQL[/purple] . . . just copy/paste in [blue]SQL View[/blue] of a new query in [blue]Design View[/blue] (don't select any tables).

[blue]UPDATE tblStartUp
INNER JOIN (SELECT tblStartUp.ID, tblStartUp.ItmProd, tblStartUp.Restock, [ItmProd]-[Restock] AS InStore FROM tblStartUp) AS A
ON tblStartUp.ID = A.ID
SET tblStartUp.ItmProd = [A].[InStore], tblStartUp.Restock = 0;[/blue]

[purple]Cheers! . . .[/purple]

See Ya! . . . . . .

 

[RobotMush]

Thanks Ace, While the acctual Cut Paste does not work due to a few glitches in the names of tables or fields. I do get the gist of what you are telling me. I have also read up in my "Access 2000 Bible" and my "Begging Access 2000 VBA" as well as the ever popular and wonderful "F1" key.

Thanks to your heads on on the Update Query I should be able to muddle through and get the job done. The update was the main thing that was holding me up.

Thanks so much Ace. You have earned yourself a *

RobotMush (a very happy (Techincal User)

 

[PHV]

Why using a subquery ?
Doesn't this suffice ?
UPDATE tblStartUp
SET ItmProd = ItmProd - Restock, Restock = 0
WHERE Restock <> 0;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobotMush]

From reading the entry, it seems that anytime that restock has something put into it, it will update itself. I am not wanting this. I am wanting to be able to update the database when it is time to.

Thanks for the help though PH

RobotMush (Technical User)