Changing Values from one Table will change Values in the other Table

[Wendor]

Hi, I'm new here and I didn't found a similar topic so I decided to write here.
I have a problem with the database in Access 2013.
My database keep information about removing \ adding \ moving of products from some locations and when product was transferred \ bought \ sold , in what quantities and into what location.
You can also check the current location of the product.
I have tables:

Table locations
Location..... Product code.........Qty
CB08D...........BL0088........... 430.00

Table datahistory
ID........Product code.............Qty.....Location........... Date
1 ...........BL0088................. -20....... CB08D..... 10/05/2016 15:00

When the user wants to move product to another location have to use a form and enter data: product code, location, Qty
(for example, if he sell 20 pieces of a product from a particular location he typing -20, if he buying he have to type +20).
Data in the table datahistory is readed automatically with function Now ().
Do you know how I can set the automatic counting of the values ​​in the table locations by entering a Qty, product code and location in the table datahistory?
For example. When the user has sold 20 pieces of some product access counting automaticly 430-20 = 410 and give the value of 410, and when the user buy 20 units of the product for warehouse access will count 430 + 20 = 450.
Yust for your information I want to keep data from table datahistory to have record what and when has been sold / purchased / transferred.
Many thanks in advance for your help

 

[plog]

Do you know how I can set the automatic counting of the values ​​in the table locations by entering a Qty, product code and location in the table datahistory?

You don't. Instead, you build a query that determines that for you. You don't store values you can deduce. You simply add up all your Qty values and that determines the inventory at a location for a product.

 

[TheGhostOfRain]

Table locations
Location..... Product code.........Qty
CB08D...........BL0088........... 430.00

Table datahistory
ID........Product code.............Qty.....Location........... Date
1 ...........BL0088................. -20....... CB08D..... 10/05/2016 15:00

Can you show a diagram of your relationships.

 

[Wendor]

Thank you for all your answers.


Plug: Could you please tell me when I creating query and adding quantities what function or code I should use?

TheGhostOfRain: I don't have relationships. I know I should but when I created them it start showing me wrong values so I removed them.Do you have suggestions what Relationships I should have? I know I doing something wrong but it is hard to find what becouse I didn't used access 9 years :-/

 

[plog]

You would use the SUM function of SQL. What you would want is a Totals query:

https://support.office.com/en-us/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

 

[TheGhostOfRain]

TheGhostOfRain: I don't have relationships. I know I should but when I created them it start showing me wrong values so I removed them.Do you have suggestions what Relationships I should have? I know I doing something wrong but it is hard to find what becouse I didn't used access 9 years :-/

I believe that you should have all your relationships in place. This will allow you to see a map of what is happening.

If you can't join the tables, it may be because the design of your tables is incorrect. "it start showing me wrong values " This makes me feel that you do have some incorrect table designs. Most likely with the Primary and Foreign keys.

You have not given enough information for me to make any suggestions. Perhaps you could make a copy of the database, then delete all the information you have in the tables bar 2 or 3 records in each .

The post it here but don't forget to do a compact and repair before posting

 

[gemma-the-husky]

@wendor

just to make it clear, the best way of managing inventory is not to try to store the current balance on hand, but to evaluate it by adding together all the movements, both in and out.

you can try to store the balance on hand, but it is extra work, and there are lots of circumstances in which the balance can change, and if you forget to handle any of them, you will have a difference between the stored balance, and the calculated balance. You might even just change a record directly in a table, which is bound to cause an error.

 

[Wendor]

Thank you for all your suggestions. All of you have a bit of right. I changed relationships and I created a right query. Now I almost getting what I want:

Result of Query:
Location.....Product Code...... LocationQTY........SumOfQTY...TOTALQTY
CB08D....... BL0088..................438.00...............-25.............413
CB03D....... BL0089...................70.50...............-5................65.5
CB13C....... BL0098..................420.00............... 5...............425

Now I want to see all locations and all products with total QTY, rather than just that which have some movements (some data in datahistory).
Do you know how I can do that ? Please see below my query.

My query:
SELECT LocationsOFproducts.[Location], LocationsOFproducts.[Product Code], LocationsOFproducts.LocationQTY, SumDatahistoryQuery.SumOfQTY, [LocationQTY]+[SumOfQTY] AS TOTALQTY
FROM LocationsOFproducts INNER JOIN SumDatahistoryQuery ON (LocationsOFproducts.[Product Code] = SumDatahistoryQuery.[Product Code]) AND (LocationsOFproducts.[Location] = SumDatahistoryQuery.[Location]) AND (LocationsOFproducts.[Product Code] = SumDatahistoryQuery.[Product Code])
ORDER BY LocationsOFproducts.[Product Code];


Just to clarify SumDatahistoryQuery make a Sum of all Stock movements - so if some product will be typed twice it will count it (For example I typed in datahistory form product BL0088 in location CB08D,QTY as -20 and later the same data but with value -5. That's why as a result it showing -25)

 

[plog]

You structure still isn't right. From what I know of LocationsOfProducts, it is an unncessary table. You simply need your data history table.

Table datahistory
ID........Product code.............Qty.....Location........... Date
1 ...........BL0088................. -20....... CB08D..... 10/05/2016 15:00

That table is all you need. What you are doing now is simply storing the initial datahistory record in LocationsOfProducts. There's no need for that. Instead, for each initial allocation of product, you would put a record in datahistory.

 

[Wendor]

Plog I need LocationOfProducts table to know in which location how many pieces of products I have. Without that table I will not know where I keeping what products and how many of them.

In table datahistory I keep only data about stock movement.

 

[plog]

I don't know how to respond politely. So let me simply say:

The post you replied to addresses those issues.

 

[TheGhostOfRain]

Wendor

Are you going to post a cut down version of your Data Base.

It would help us to see the problem more clearly.

I am sure you must have a good reason for not posting if that is the way you want to go.

 

[Wendor]

Thank you for all your answers !!! I resolved all my problems.
We can close that topic now

 

[TheGhostOfRain]

Thank you for all your answers !!! I resolved all my problems.
We can close that topic now

It may be a bit difficult this time, but if you can explain what you did it my help someone else.

Good to hear you are happy with your progress.