Help with MS Access?

Question

Hey everyone.
Im creating a simple database for toner management, currently have a table that records the issue of toner, but now I have been asked to enable a stock control part to it. I know I have to create another table for toner qty of toner in storage. How would I be able to get access to update the qty table when one toner is issued?
Tables used are TblTonerIssue and TblToner, I know they would have to be linked via relationship and know that it would be the toners own model number as primary key in TblToner and a foriegn key in the other. Can anyone help?

Answer 1

Yes you would use a relationship between the two tables to help update it, but you will also need to creat an update query.

Go to Queries in your access database, the click "Create query in Design view". Add the tables that you want to work with in the query. Then from the Access menu bar click Query, then Update Query. What an update query does is looks for certain records then updates those records with the data you want.

You'll want to specify the fields you are using to filter the list and that you are using to update your data. Right click in the criteria section and click build to help you set up the criteria for filtering. The update to section tells Access what value that you want to update that field to. You can right click and use build for that to, so you can pick the value from a form, table, query or whatever.

After you build the update query, go to the form that you will be working off of and add a command button to the form. Make sure to have the magic wand clicked/selected so Access will help you with setting up what the command button does by starting the command button wizard. Go to the Miscellaneous category then select the run query action. Select the query you just created then work your way through the rest of the wizard. Once your done with the wizard, all you have to do is pretty much click that command button to run the query and update your records.

Answer 2

Personally, I'd do this as a spreadsheet, not a database, if you can. You can get a s/s to do calculations and update, e.g when you enter a figure in let's say, "Issued" the quantity in "Stock" will self-adjust. Requires some planning & mathematical understanding, though.

If you're familiar, use Excel.