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.execute (" 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!!

[nico5038]

I've started with changing your table structure. (Check the database relations!)
A tblAgent has been added to record the UpgradeQuantity.

The table tblBalance has an additional date and the NormalBalance has been dropped as "Starting balance as of 1 January" can be recorded on that date as a DateUpgrade qty while dropping the older dates.

Opening the form triggers the warning and a Yes will add the rows with the upgrade qty.

Finally the Balance on the form is made visible after selecting the Agent.

To finalize this you would need an additional FinalBlance being the Dsum() of the tblBalance minus the Dsum() of the tblLeave qty.

Nic;o)

[MMcCarthy]

For more information on Table Structures check out this insights article.

Database Normalization and Table Structures

[Mr Hero]

A million thanks for your help. I appreciate your patience with me, as I am still learning and trying to wrap my head around Access.

[nico5038]

Patience is my middle name :-)

Success with your application !

Nic;o)

[Mr Hero]

I have made some progress, I ended up making some changes due to a recent step back... I do like the problem solve phase. However, It is the implementation part that always gets me confused. Is there way, I could convert some of this process into an event? or it is a best practice to use a qry in conjuction with VBA?

[nico5038]

You can't change processes into events, you need to use the event to trigger a process or give the user a button to activate a process.

Queries from VBA are in general slower as stored queries, thus I prefer to store them, but use VBA queries when there's too much flexibility needed.

Nic;o)

[NeoPa]

Nico:
Patience is my middle name :-)

That's right. How many times have I heard them say, while pointing at Nico :- "That's 'im. Patience."

[Mr Hero]

Thanks Nico! I appreciate you taking the time to explain some these issues.

I have a question, can I use type casting in a event to store without getting a data type mismatch?

[nico5038]

Looks obvious to me, that you make sure that the data to be inserted is in the proper format. In Access you use "nothing" for numbers, quotes (") for text and hashes (#) for the date datatype. The verification can be done by code, the type is determined by the used separator.

Nic;o)

[Mr Hero]

Thank you.