Web Development

Ask something
Discussion Locked

Relational Database Persistent Data

0 helpful
12 replies

by maniskid

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm building a relational database for a small high school lab for the purposes of stock controlling (and recording where everything is).

I am stuck (wondering) on how to keep previous years data while still being able to update the data as it is seen in the tables, I added a date field to record when the data changes.

What I want to do is to record the changes in stock from year to year and if the stock is not updated, the last record of the stock.

12 replies

by sl-campbell

I could go into into the need to have a table to add stock, a table to record stock removed, a table that lists all items, a table that lists all locations, a table that shows the links between items and locations, if an item can be in more than onelocation, the need to record items received on what date, amount received, in which location, create unique ids for each shipment, and when an item is taken out, then only by a last-in first-out basis, etc. But this would go to far.
In order to simplify this would mean to just use a second table, which is linked to the main table, that has 3 fields: Item number, date, and amount that can be entered as a plus or minus. Stock adjustment is then only made through a 2nd form that uses the item's static data table to show info for that item, and form fields to input the stock adjustment and date, and a command button to save the data to the 2nd table - this could also be done all on one form, using the current form that you have, but you stillneed a 2nd table. When the command button is clicked to add or take stock, the item number, amount +/- and date is stored as a record in the second table. Then the total balance can be calculated by adding everything up for that item. And the balance on a certain date is just as easy, except you add to the criteria the date in question.

by sl-campbell

If you want a more complex scenerio then let us know and an outline will be given. As for a query to calculate the balance you can go to the Qury builder and select the second table and then under VIEWS click SQL and add the following code:

(Assuming the 2nd table is call:Inventory Adjustments and the field names are the same as I used)

PARAMETERS [Item#] Text, [Date Start] DateTime, [Date End] DateTime;
SELECT [Inventory Adjustments].[Item number], Sum([Inventory Adjustments].Amount) AS [Current Balance]
FROM [Inventory Adjustments]
WHERE ((([Inventory Adjustments].DateAdjusted) Between [Date Start] And [Date End]))
GROUP BY [Inventory Adjustments].[Item number]
HAVING ((([Inventory Adjustments].[Item number])=[Item#]));

But, one additional remark - IF this is a multi-user enviroment and IF the database table isn't accessed by more that one person at the same time too often, OR IF this a a single user database, then it is perfectly alright to have IN ADDITION to the above a static field in the main Item/Article table that saves the current SOH. When the user clicks the button to post withdraws or deliveries/additions to the stock, then the static SOH/BOH field in the main table can be re-calculated and saved. Thewouldn't however be of any tremendous advantage in the above scenerio.

by sl-campbell

You would then need to just add to the stock movement table(s) the ArticleID, LocationID, Amount Issued/received, Date. Then you can always link up to a certain article in a certain location for a certain date to obtain the current balance or the movement with-in a given time period - dynamically.
If you still want a static field for the BOH/SOH then whenever an item it received/issued, these fields, (one in the Artikel table and one in a Link table ArticleLocationLink will need to be updated,and then a proceedure to refresh these values in case they get out of place.

Tables needed:

>Article/Stock
>Location
>ArticleLocationLink (Fields ArticleID, LocationID, SOH) This table itendifies all the locations that an article can(only) be located in. You will need a link form so the user can assign/unassign an artcle to a certain location (need ref. integ. check so an item cannot be unassign once it has ever been logged in as received/issued)
>Received (ArticleID, LocID, Amount, Date)
>Issues (Fields same as above plus IssuedTo)
If you use 2 tables here, then create a unique receipt ID field using something like a combination of the ItemID+LocID+DateTime down to the second, so that the issues can be easily tracked back to the receipts.

The last 2 can be made into one, but better is using 2.

Then consider having another issue type table to record bad/damaged stocks or faulty receipts(which can be removed from the receipt table as long as no issues have been made)

by bob sellman

The solution mentioned in the first answer will give you what you need in the simplest form. But I do want to caution you to avoid falling into the trap of calculating and saving (in your table that lists the stock items) the current amount in stock.

The second table (where you enter the stock item, date, and quantity) could also include a remarks column if you want to be able to enter comments for some of the transactions. But the most important thing to enter in this second table is the starting amount. So, for example, if you start with 1000 widgets initially on 1/1/2002, there should be an entry indicating that fact.

If withdrawals are entered as minus numbers and additions to stock as plus numbers, then you can determine the current stock quantity using a simple grouping query that sums all of the quantity entries for each stock number in the second table. If you want to display the current stock with the item information (your current table), just add a control on the form that is not bound to any field. Set up the data source using the DLookup function to lookup the current value from the query (or use the DSum function to calculate the total from the second table directly).

Using the second table you can usequeries to determine the activity between any two dates and, obviously the last date of activity for a specific stock. (Use a grouping query where the date is sorted ascending and select last record instead of group by for the date field.)

by maniskid

Initially what I did was to create a few tables called stock, location and suppliers. Then I added another table called StockInHand which is a joining table for the other 3. This table has a field of 'quantity_on_hand' and 'date' fields which are not null. With the second table that you proposed, I created the table and it accepts StockId, Date and an Amount alright but the field called StockInHand.'quantity_on_hand' will not change.

I think that I need to change this so that the quantity_on_hand field relates to a totalled field in the Quantity Adjustments table. That way for each stock in a location there is a stock amount and a history of stock changes.

by sl-campbell

Alot of help was given here. Could you please let us know the status. I will not track this any longer.....

by maniskid

I thought I had closed this question with a hearty thankyou about 2 months ago. Thanks for all your help, I use it at least once a day and I can now utilise some stock control in the lab. All thats left is to use it and gain some historical data.
Once again
Thankyou!

This discussion is closed to new replies.