i need someone to explain this ERD and how data flow?

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

i need someone to explain this ERD and how data flow?

Post by siamandm »

Hello All,
I tried to understand this diagram but for an unknown reason I get confused and I don't know how does it work, I appreciate if someone explains and shows me how data flows
if you can use this example data for your explanation will be very helpful,
let's say we have an employee John Doe, needs to add (order) 15X Laptop, Lenovo from supplier name ( Computech ) price 1000$/each
and then a customer ( Adrian ) buying 5X laptops, price 1200$/each
ERD inventory.jpg
now i know we need 2 forms one for adding Ordering and one for selling, but how they work and how the systems know 10 laptops left
your help is much appreciated.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: i need someone to explain this ERD and how data flow?

Post by HansV »

This ERD is not correct, as far as I can tell.
- There should be an inventory table.
- There should probably be a purchase order details table.
- The way the relationships on the right hand side of the picture are drawn, is very confusing, since lines overlap.
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: i need someone to explain this ERD and how data flow?

Post by siamandm »

Dear Hans,
Thanks a lot for your reply,
I have recreated the ERD on access on as well, could you please tell me the week points and what is missing, please?
I have used sales(selling products) instead of order as its a bit confusing for me,
inventory db.jpg

many thanks
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: i need someone to explain this ERD and how data flow?

Post by HansV »

The ERD in your first reply is intended to keep track of inventory: products are purchased from suppliers, then sold to customers.
The ERD in your sample database doesn't have any connection between purchases and products.
Do you want to keep track of purchases and sales, or just of sales?
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: i need someone to explain this ERD and how data flow?

Post by siamandm »

Thank you for the reply,
What i want is: having a form an employee add or buy items from a supplier
And then another form to sell items from those which has been added earlier.
Then i want to know how much items left in stock
How to approach this please ?

Regards

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: i need someone to explain this ERD and how data flow?

Post by HansV »

Here is a possible way to do it (it might have to be more complicated in reality):

S0018.png

The tbl_Products table acts as the inventory. A tbl_Purchase record can have several tbl_Purchase_Details records, one for each product purchased, just like a tbl_Sales record can have several tbl_Sales_Details records, one for each product sold.
You'll have to use VBA code in the sales and purchase forms to subtract/add the quantity sold/purchased from/to Product_Qty in tbl_Products.

Keep in mind that this is a complicated subject. It is very easy to mess things up. It might be better to buy a ready-made inventory system!
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: i need someone to explain this ERD and how data flow?

Post by HansV »

You might also be interested in the Inventory database available as a zip file on Ken Sheridan's OneDrive
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: i need someone to explain this ERD and how data flow?

Post by siamandm »

thanks a lot for the file, I will have a look and see if I figure out and learn the idea from it.

many thanks for your continues support.

Regards

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: i need someone to explain this ERD and how data flow?

Post by siamandm »

i have a question on this form please,
stock movement.jpg
the first part is: will put the times what we receive and will be available in stock and ready for sale.
but what are the second and third part for and how to use it, if you can explain using the screenshot, please?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: i need someone to explain this ERD and how data flow?

Post by HansV »

Acquisitions are products that you purchase.

Non-sale disposals are products that haven't been sold, but that you want to remove from the inventory, for example because they are damaged or out-of-date.

Stocktaking means that you check and count all products that are present in your store. Most companies do this once or twice per year, to make sure that the inventory is correct - there will usually be small discrepancies with what is recorded in the database, for example because someone forgot to enter a sale in the database, or accidentally entered an incorrect quantity,
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: i need someone to explain this ERD and how data flow?

Post by siamandm »

Thanks so much for the explanation one last thing as i didnt get the last part fully, why the wrote 90 ?

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: i need someone to explain this ERD and how data flow?

Post by HansV »

That's just an example!
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: i need someone to explain this ERD and how data flow?

Post by siamandm »

In the example they entered 90 because there was 90 items missing in the database and not been added?
Lets say after one year after doing physical check for my stock and i found that we have extra 20 of an item compared to the data base then i will put 20
And if there is 10 shortage i will put -10
Correct?

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: i need someone to explain this ERD and how data flow?

Post by siamandm »

HansV wrote:
01 Dec 2020, 14:45
Here is a possible way to do it (it might have to be more complicated in reality):


S0018.png


The tbl_Products table acts as the inventory. A tbl_Purchase record can have several tbl_Purchase_Details records, one for each product purchased, just like a tbl_Sales record can have several tbl_Sales_Details records, one for each product sold.
You'll have to use VBA code in the sales and purchase forms to subtract/add the quantity sold/purchased from/to Product_Qty in tbl_Products.

Keep in mind that this is a complicated subject. It is very easy to mess things up. It might be better to buy a ready-made inventory system!
Dear Hans, Thanks a lot for the help
the main purpose of my post is learning, as I'm working with access for a while and still not getting many things correctly and this subject is one of them.
and I believe this is a good example which I will learn a lot of things through it,
so I think building projects in real life will be very usfull and better understanding.

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: i need someone to explain this ERD and how data flow?

Post by siamandm »

HansV wrote:
01 Dec 2020, 14:45
Here is a possible way to do it (it might have to be more complicated in reality):


S0018.png


The tbl_Products table acts as the inventory. A tbl_Purchase record can have several tbl_Purchase_Details records, one for each product purchased, just like a tbl_Sales record can have several tbl_Sales_Details records, one for each product sold.
You'll have to use VBA code in the sales and purchase forms to subtract/add the quantity sold/purchased from/to Product_Qty in tbl_Products.

Keep in mind that this is a complicated subject. It is very easy to mess things up. It might be better to buy a ready-made inventory system!
Dear Hans, in my understanding i have to remove Product_Qty from tbl_Products on the right hand, am I correct?

Regards

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: i need someone to explain this ERD and how data flow?

Post by HansV »

No, it would be the quantity that is in stock. When you purchase the product, the Qty from tbl_Purchase_Details should be added to it, and when you sell it, the Qty from tbl_Sales_Details should be subtracted from it.
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: i need someone to explain this ERD and how data flow?

Post by siamandm »

HansV wrote:
03 Dec 2020, 07:44
No, it would be the quantity that is in stock. When you purchase the product, the Qty from tbl_Purchase_Details should be added to it, and when you sell it, the Qty from tbl_Sales_Details should be subtracted from it.
I'm a bit confused now,
let's say I have 20 Iphones in table_products
then using a form linked to tbl_Purchase_Details I will add 10X iPhones how this 10 phones will be added to tbl_products? and what will happen to the 10 in the tbl_Purcase_Details?

from the example, you sent we have one Order details to track the qty that has been sold and table StockAcquisition to add new items like a stock
could you please help me to understand this?
Regards

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: i need someone to explain this ERD and how data flow?

Post by HansV »

The ERD in my reply higher up takes a different approach than Ken Sheridan's database.
In my approach, you'd have to use VBA code to add Qty to Product_Qty in the unbound form that you use to add a new record to tbl_Purchase_Details. Nothing happens to the Qty in that record - it provides a history of your purchases.
Ken's approach is probably safer. It doesn't store the quantity in stock, but calculates it as the total result of stocktaking, purchases (acquisition) and sales.
Best wishes,
Hans