Table updates as form inputs into another table.

[Mr Hero]

I have 2 tables, one table is for the form to input records; and the other table is for keeping balances. How can I get my Balances table to update as new entries (+ or -) is entered in the input table? Any assistance is greatly appreciated!!!! =D

[nico5038]

Guess you need to start with reading the normalization article you can find at:

http://bytes.com/topic/access/insights/

as it's not necessary to update your balance table.
The proper balance can be deducted when needed.

Nic;o)

[Mr Hero]

Hi Nic;o),

I am not sure what you mean by "The proper balance can be deducted when needed." Can you explain a little.

Thanks!

[Mr Hero]

Nico,

I was able to find the Normalization article. The theories of practice makes sense. I am not sure how this will solve my question.

I have created the input table, in which it has all the records for me to pull into a report. The reason I was wonder if the Balance table can be updated, is because I have more then one type of Item. i.e. Chairs has 100, windows has 100, etc. a total of 10 items. How would I update this quantity based on add ins to the stock and removal? There is no specific sequence a record is entered. Lets say I make an entry of 2 Chairs. Since I am adding 2 chairs then how would I create something to update my balance?

[nico5038]

For getting the balance you'll have to sum the created/bought Item quantity (Let's call it qtyIn in tblIn) and to subtract the delivered quantity (Let's call that qtyOut in tblOut).

As both the tblIn and tblOut will hold the ItemID (chair, etc.) you can JOIN these two tables to the tblItems by the ItemID.

By making the query a group by query you can sum the qtyIn and qtyOut for every different Item and by subtracting them the balance is known.

Getting the idea ?

Nic;o)

[Mr Hero]

That makes sense. =D

Okay, I have one more question. Every two weeks I need this qtyIn to increase by 6. Will I need to write some Code to make this happen? or is it possible to have a qry to this process?

[NeoPa]

A query can do the update, but scheduling such things is another matter of course. Your specification of every two weeks is far too ambiguous to work from though.

[Mr Hero]

Well, that is interesting, what if I added to a form on the onOpen event to run a query and use the DateDif to calculate the amount of days from the begining of the yr.

[nico5038]

There's a big disadvantage in automating such an append, as people tend to get the idea they lose control.

I would probably test when starting the application or the last update has been over two weeks ago to pop-up a confirmation for for the user to agree to write the new quantity to the database. I would also have a control table where the user can set their increment, thus any future changes don't imply changing the code.

This code will of course trigger an append query to insert the quantity.

When you add these quantities into the future, then don't forget to make the query for calculating the balance dependent on the current date.

Nic;o)

[Mr Hero]

Hmm... Thanks for the guidance, I will continue and see how this turns out.

[Mr Hero]

Okay, I am not having much luck. Do know where I can look at an example?

[nico5038]

Just make sure you have in the tblIn a date/time field [LastUpdated] with Now() as default value.
Now you can use a DMAX() to check the difference between the current Now() and the max stored value.
Next use the command:
currentdb.execu te (" here goes your query")
to insert the rows.

Nic;o)

[NeoPa]

This is exactly what I was referring to when I talked of the ambiguity of scheduling the job every two weeks.

Nico has explained the solution, that I would think is probably the better one, well.

Let us know if you manage to get that working or whether you need further clarification. Remember, we don't know how much you know, so unless you tell us we will assume you have it all ok now.

BTW. The article referred to can be found at Normalisation and Table structures, and is well worth reading.

[Mr Hero]

I have hit a few stops. Nevertheless, I think I know where I went wrong.

I have a little experience with Access. However, I would say my experience level is intermediate.

Thanks for taking the time to assist me. =D I will keep you posted on as I attempt to make this work. Thanks.

[Mr Hero]

Okay, it seems to me that I am lost... I wasn't sure how to use the DMAX() function in my qry. I have attached a sample where I am testing everything that I am suggested. I really would appreciate some assistance. Thanks!!