How to use a previous value of a field in a Table?

[C.Sica]

Hi,

I am new in MS Access ... so please help me. Thanks in advance!

I have a simple Table with the following structure:

ID
Product
Current_Level
Received
Available

How can I retrive the previous value of the field [Available] and to set this value for the new [Current_Level] ???

Thanks!

 

[Galaxiom]

How do you define the "previous" record?

Moreover, your requirement suggests a normalization error because it is recording the same information in two places.

 

[plog]

Well, the first thing to learn is that in a table, data has no order. There is no first, there is no last, no next, no 45th.

Order exists only when you tell it to exist in either a query, or a domain function that allows you to explicitly define it. So when you are working in a table and want to retreive the 'last' value of anything, it makes no sense.

However, if you are entering data through a form, you can use VBA to store the previous values you want. You can also discern the 'last' record if you can logically deduce what the 'last' record is.

Can you further explain what you are trying to do and provide some sample data to illustrate?

 

[C.Sica]

Thanks for answers.

I just want to make an Inventory for Products.
I have IN and OUT for every product. How can I calculate the current level of every product?

 

[plog]

You're not helping. I didn't have an idea of what you want to accomplish, but at least I knew the fields. Now you've introduced two new terms (IN and OUT) and I have no idea how they fit into your tables.

Provide sample data (including table and field names) and what the ultimate result should be based on that sample data.

 

[C.Sica]

Hello,

thanks a lot for your answers.

Please find in the attachments the file Inventory.

I want to update in the table Inventory the Current_Level for each Product.

Current_Level = Input -Output

I have no ideea how to do this job.

Thanks,

 

[plog]

I'm sure there are other posts that give more in-depth instructions, so try searching for 'Inventory' on this forum. I'll give you a brief overview:

1. You don't store calculated values. Instead you calculate them when you need them. This means 'Inventory' shouldn't be a table in your database. You should instead build a query that adds up all the Input and Output values and tells you how much you have.

2. To do #1, you need both your credits and debits in the same column. Instead of [Input] and [Output] fields you should have a [Quantity] field to hold both of these values. You could simply make Output negative numbers and Input values positive and then add them up to see your inventory. Or you could always add positive numbers and determine if its an input or output by adding a new column for [TransactionType] which would tell you if that record's [Quantity] field should be added or subtracted to determine inventory.

Again, you should be running a query to determine this, your table needs to be reconfigured to do so, and there are a lot of posts on here dealing with this issue.

 

[C.Sica]

I saw the same situation on Northwind template ... but I want to understand how it works.

... thanks