Hello All,
I want to create a database for my store to track the inventory, i have create three tables as below:
using this design is overwriting the quantity on hand in table product, so i decided to add another table to keep the original qty on hand
so i decided to add this table to my design as below this way i can keep purchased and sold item numbers:
now how to connect this table to other tables, or is their a better design ?
Regards
Need your suggestion in designing my database
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
Need your suggestion in designing my database
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need your suggestion in designing my database
How do you want to use the TransactionT table?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need your suggestion in designing my database
You may find Ken Sheridan's example of an inventory database useful. You can download Inventory.zip from Ken Sheridan's public database examples.
And a series of tutorials: How to create a stock control database.
And a series of tutorials: How to create a stock control database.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Need your suggestion in designing my database
You would normally calculate the QtyOnHand, not store it.
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
Re: Need your suggestion in designing my database
Thank you very much for replies
based on your suggestion i came up with this design, but I'm not sure about its bugs and problems if you could you help me on this please
I have two junction tables, one OrderDetailT and another PurchaseDetailT
based on your suggestion i came up with this design, but I'm not sure about its bugs and problems if you could you help me on this please
I have two junction tables, one OrderDetailT and another PurchaseDetailT
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need your suggestion in designing my database
In the PurchaseDetailT table, you do not need a SupplierID field - the supplier is implicated by the PurchaseID field.
The table should have a ProductID field, and the link to ProductT should be from that field instead if from PurchaseID.
The table should have a ProductID field, and the link to ProductT should be from that field instead if from PurchaseID.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
Re: Need your suggestion in designing my database
Thank you very much for the reply, now the design became like this, does this design allow me to track the quantity on hand without overwriting the original quantity purchased and entered the stock?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need your suggestion in designing my database
That looks fine. In this version, the stock on hand is not stored in any table. It can be calculated on the fly in a query as the sum of all Quantity values in PurchaseDetailT where the related PurchaseDate in PurchaseT is less than or equal to the current date, minus the sum of all Quantity values in OrderDetailT where the related OrderDate in OrderT is less than or equal to the current date
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
Re: Need your suggestion in designing my database
This sounds good, so we can calculate it at any time on the fly :)
Thanks again for your continues support.
regards
Thanks again for your continues support.
regards