Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31
  1. #16
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Hi again,

    so I spent a great part of the morning trying to work with the data model.

    Here is what I came up with so far:

    My workflow diagram:

    Click image for larger version. 

Name:	flow_diagram.PNG 
Views:	54 
Size:	24.6 KB 
ID:	46446

    Additional description of each step:
    Click image for larger version. 

Name:	description.PNG 
Views:	51 
Size:	80.1 KB 
ID:	46447

    Data model (incomplete, tables and relationship missing, just for reference):
    Click image for larger version. 

Name:	datamodel.PNG 
Views:	51 
Size:	70.4 KB 
ID:	46448



    Can you please comment on if my approach so far is correct? Does this model make sense?

    What I don't get so far is how to get this functionality:

    UNIT HISTORY
    Date / Action / Details
    1.1.2021 / Purchased / PO123
    1.2.2021 / Sold / SO456
    1.5.2021 / Received for Repair / RMA789
    1.6.2021 / Shipped back after Repair / RMA789

    Specifically, that I can assign the reference number based on the type of the action.
    Is that possible?

    Thanks again everyone.

    Tomas

  2. #17
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Don't underestimate the importance of Minty's comment in post #5. Inventory is not trivial.
    Here are a few other things for consideration:

    26 minute youtube video on Stock Management for reference

    stump the model analysis and design approach

    Inventory management flowchart video

    you might get more contextual info by google-ing "Inventory Management"

    Do you have to deal with:
    Backorders, partial deliveries and/or substitution products?

    Have you considered a purchased/off-the-shelf inventory management product/software?

  3. #18
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by orange View Post
    Don't underestimate the importance of Minty's comment in post #5. Inventory is not trivial.
    Here are a few other things for consideration:

    26 minute youtube video on Stock Management for reference

    stump the model analysis and design approach

    Inventory management flowchart video

    you might get more contextual info by google-ing "Inventory Management"

    Do you have to deal with:
    Backorders, partial deliveries and/or substitution products?

    Have you considered a purchased/off-the-shelf inventory management product/software?

    Thanks, I'm in no rush. I plan on refining the design many times. This above is just a first working version. Far from the production model.

    Thank you for the resources, I will definitely check those out.

    T.

  4. #19
    Join Date
    Apr 2017
    Posts
    1,673
    I look at your post #16, and it looks like when you implement all this you'll get some ERP DB for small firm

    When you want to include producing part too, then you need:
    Producing orders, i.e. an order to produce certain quantity of some article on certain date/date period, which is marked in your database as your product - like
    tblProdOrder: ProdOrderID, ProdOrderDate, ProdArtID, Qty, ..., ClientOrderID (ProdOrder will be linked with client order. When client order has several products ordered, for every of them you need separate ProdOrder's. Also when the producing is done stepwise in different workplaces, then you need separate ProdOrders for every such step, where only the final one is producing ordered product - all other are to produce some medium part of it, and you need to register such medium parts a s articles.).
    Then you need a BOM list for this product, where all components and their quantities for one unit of product are listed. This may be a simple list, or a structured one where every step of producing is described, with additional table where all operations for every step are described. As example let's it be a simple one:
    tblBOM: BomID: ProdArtID, BomComment;
    tblBomDetails: BomDetailID, BomID, RowNo, ComponentArtID, Unit, Qty.
    Now, based on ProdOrder and BOM list + BOM Details for given product, you get a list of all components, and their quantities, you'll need to produce product(s)/medium part(s) in workplace. In case you have a Storage as unit in your firm, this will be a document for them to assemble all needed components and send them to production unit(s) at wanted date. When yo haven't, then production must simply collect them, and somehow register this.
    When the product is produced, then it must be send into storage, or at least registered. Also, when some component is produced, and not used immediately in production, this must be registered as incoming transaction. And when such stored produced component is used later, then it must be included into BOM list.

    As you see, it will get very complicated very fast. In case you want to do this anyway, my advice is to create 2 different apps - one for production, and another one for article management. Article management has to take into account all purchases and incoming purchased articles and all sales and shipping of sold articles as external movements, and all component complects sent to production for certain ProdOrder as outgoing articles, and all produced or partially produced articles, and components returned from production for some reason, as incoming articles as internal movements (you'll need a lot of various transaction types for it!). Of course those apps must be linked - storage must know and register as transaction, when, what, and how much to send/register for production, and what and how much produced/returned goods was coming from production.

  5. #20
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by ArviLaanemets View Post
    I look at your post #16, and it looks like when you implement all this you'll get some ERP DB for small firm

    When you want to include producing part too, then you need:
    Producing orders, i.e. an order to produce certain quantity of some article on certain date/date period, which is marked in your database as your product - like
    tblProdOrder: ProdOrderID, ProdOrderDate, ProdArtID, Qty, ..., ClientOrderID (ProdOrder will be linked with client order. When client order has several products ordered, for every of them you need separate ProdOrder's. Also when the producing is done stepwise in different workplaces, then you need separate ProdOrders for every such step, where only the final one is producing ordered product - all other are to produce some medium part of it, and you need to register such medium parts a s articles.).
    Then you need a BOM list for this product, where all components and their quantities for one unit of product are listed. This may be a simple list, or a structured one where every step of producing is described, with additional table where all operations for every step are described. As example let's it be a simple one:
    tblBOM: BomID: ProdArtID, BomComment;
    tblBomDetails: BomDetailID, BomID, RowNo, ComponentArtID, Unit, Qty.
    Now, based on ProdOrder and BOM list + BOM Details for given product, you get a list of all components, and their quantities, you'll need to produce product(s)/medium part(s) in workplace. In case you have a Storage as unit in your firm, this will be a document for them to assemble all needed components and send them to production unit(s) at wanted date. When yo haven't, then production must simply collect them, and somehow register this.
    When the product is produced, then it must be send into storage, or at least registered. Also, when some component is produced, and not used immediately in production, this must be registered as incoming transaction. And when such stored produced component is used later, then it must be included into BOM list.

    As you see, it will get very complicated very fast. In case you want to do this anyway, my advice is to create 2 different apps - one for production, and another one for article management. Article management has to take into account all purchases and incoming purchased articles and all sales and shipping of sold articles as external movements, and all component complects sent to production for certain ProdOrder as outgoing articles, and all produced or partially produced articles, and components returned from production for some reason, as incoming articles as internal movements (you'll need a lot of various transaction types for it!).

    Haha, yes, building a small ERP is kind of my goal .

    Fortunately, we do not produce stuff. We only buy and resell with a margin. I wouldn't get into production, that's way too complicated. But I think I can handle reselling.

    So basically I only need the article management app like you mentioned.

  6. #21
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Hi guys,

    I created the core model within Access / SQL Server, here it is:

    Click image for larger version. 

Name:	datamodel2.PNG 
Views:	47 
Size:	76.0 KB 
ID:	46454


    Here is some sample data in the Transactions table:

    Click image for larger version. 

Name:	table_transactions.PNG 
Views:	47 
Size:	6.6 KB 
ID:	46456



    Then I created 3 queries (for simplicity in Access, not SQL server):

    1st query: Last Transaction ID
    • Purpose: Find the last Transaction ID for each Unit
    • Source: tbl1Transactions
    • Fields: MAX(TransactionID), Group by: UnitID


    Click image for larger version. 

Name:	qry1.PNG 
Views:	46 
Size:	7.3 KB 
ID:	46457

    2nd query: Last Transaction Details
    • Purpose: For each Unit's Last Transaction, display all the details
    • Source: 1st query, tbl1Transactions, tbl1Units, tbl1Products, tbl2TransactionTypes
    • Fields: LastTransactionID, UnitID, TransactionDateTime, SerialNumber, ProductName, TransactionTypeName, TransactionTypeID, ListPrice


    Click image for larger version. 

Name:	qry2.PNG 
Views:	46 
Size:	23.0 KB 
ID:	46458

    3rd query: On Stock
    • Purpose: Display all units that are currently on stock (for simplicity I only work with 2 transaction types now - Purchased and Sold)
    • Source: 2nd query
    • Fields: UnitID, ProductName, SerialNumber, ListPrice, TransactionTypeID (WHERE = 1) - purchased




    That currently correctly displays the units that were purchased but not sold yet. What do you think about this solution? Could it be a good base for my project?

    Thanks.
    Attached Thumbnails Attached Thumbnails tables.PNG  

  7. #22
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Thomasso View Post
    What do you think about this solution? Could it be a good base for my project?
    It looks like it must work OK!

    Only in SalesOrderDetails table the field ProductID isn't marked as FK (probably unintentionally).
    And you have to ensure, that whenever shipment or delivery is registered, the linked rows from tables DeliveryDetails and ShipmentDetails are added into table Transactions.

    And as you never can't be sure all deliveries/shipments are always registered correctly, you have to foresee a possibility to correct current stock of any product, adding an positive/negative entry (you probably want specific transaction type/types for this) into Transaction table whenever a need, or whenever such product is found when scheduled inventory check is made.

  8. #23
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Yep, that diagram is for illustration only, I made the actual database from scratch. More keys missing

    My plan is that I will validace the whole process inside the front end (VBA).

    Within every relevant operation, there will be something like INSERT INTO tbl1Transactions (UnitID, TransactionDateTime, TransactionType) VALUES (Me.UnitID, Now(), Me.Type)

    I suppose that INSERT INTO won't slow down the database like SELECT statements, correct?

    Thanks for the tip, I will definitely include some correction mechanism.

  9. #24
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Thomasso View Post
    Within every relevant operation, there will be something like INSERT INTO tbl1Transactions (UnitID, TransactionDateTime, TransactionType) VALUES (Me.UnitID, Now(), Me.Type).

    I suppose that INSERT INTO won't slow down the database like SELECT statements, correct?
    You can use AfterUpdate event of form. At moment when it runs, the source row was saved and new data are accessible for query. Such query will be run only once per entry, and the time it takes is really short.

    And probably you must consider making tables DeliveryTetails and ShipmentDetails like log tables - i.e. after the record is saved, user can't delete or change it (this makes the management of information transformation to Transactions table much easier). With such design, whenever there is a need to correct existing delivery/shipment, the user must enter a special 'red' entry with quantity of opposite sign for product quantity, and which marks a previous entry as 'corrected' (e.g. selecting the entry with wrong information and then clicking a button). And then user registers a new entry with correct info.
    Like there was made an entry for 10 units product xxx, but instead 10 there must be 20! To table will be added a 'red' entry of -10 units, and then a new entry of 20 units. As result there will be +10-10+20=20 units. And later, when e.g. invoice is generated for such delivery, the record marked 'corrected' and 'red' record are ignored, and the invoice is generated based on records with correct info only.

  10. #25
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Good idea, thanks. I thought of a possible problem.

    If I add a serialized item in a sales order and then decide to remove it (for whatever reason), I already have a record in tblTransactions of it. If I delete the record from the tblSalesOrderDetails, there will still be the Transaction record.

    But thinking of it - that's actually solved by your previous post, haha.

    If I delete a record from SalesOrderDetails, you suggest that I actually create a new record in Transactions, with negative amount and perhaps a comment "removed from order", action on stock now?

    That would work, right?

  11. #26
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I might consider making the sales lines part of your transaction records.
    Any changes to numbers or deletions are automatically accounted for, and any credited or returned items likewise.

    If you need to move stock around for non sales items simply categorise the transaction record accordingly.
    e.g. You return an item as wrongly supplied into you, would mean the transaction record wouldn't have an sales order header ID, as it wasn't sold.
    Or it has a ID of -99 to ensure it won't ever appear as part of a sales order.

    I'm very wary of a second table for transactions that is basically a copy of your product input or sales output details.
    It will take a lot of manoeuvring to keep them both in alignment.

    Maybe you can just query those tables to work out your transactions?
    Both have in and out transactions?

    Thinking out aloud...
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #27
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by Minty View Post
    I might consider making the sales lines part of your transaction records.
    Any changes to numbers or deletions are automatically accounted for, and any credited or returned items likewise.

    If you need to move stock around for non sales items simply categorise the transaction record accordingly.
    e.g. You return an item as wrongly supplied into you, would mean the transaction record wouldn't have an sales order header ID, as it wasn't sold.
    Or it has a ID of -99 to ensure it won't ever appear as part of a sales order.

    I'm very wary of a second table for transactions that is basically a copy of your product input or sales output details.
    It will take a lot of manoeuvring to keep them both in alignment.

    Maybe you can just query those tables to work out your transactions?
    Both have in and out transactions?

    Thinking out aloud...
    Hi, thanks for your suggestions. I actually have thought about this today. That it will be kind of a pain to make sure every scenario is accounted for within the code. It screams for mistakes, and the table is completely separated, which is not a very good relational design.

    But my "problem" is that I need many types of transactions, for example:
    • Purchased
    • Sold
    • Reserved for a sales order
    • Reservation cancelled
    • Returned against credit note
    • Received for repair
    • Sent to manufacturer for repair
    • Received for calibration
    • Sent to manufacturer for calibration
    • Sent back to customer after repair/calibration
    • Rented to a customer
    • Customer returned from rental
    • End of Life
    • Marked for our own use
    • Temporarily removed from inventory


    And so on. So as you can see, if I incorporate order lines here, things will get very messy. But I really would like to think of something yet better.

    I like the idea of the Transactions table because I can easily see very detailed history of each unit.

    If you think about it, it's really not the same thing as order lines.

    In order lines, I insert generic information (used for invoicing, financial analysis and so on).
    For example: This customer has purchased 5x Product A and 2x Product B. That's great to know because at the end of the year I can say, ok so I sold 200 units of Product A.

    But the Transactions table hold completely different information. It says that within this order, there are Products A with serial numbers 1, 2, 3, 4, 5. For me that's a different dimension of knowledge.

  13. #28
    Join Date
    Apr 2017
    Posts
    1,673
    My intent was also from start, you'll have a single Transaction table for purchases, for sales, and for whatever other types of transactions. And there must be data exchange between Transactions table, and between shipping and arrival registries (NB! Where real movements of services/goods are registered!).

    About many types of transaction records - you may consider adding an additional table, where those transaction types are registered, with a some number of additional fields which will determine the rules, how those transactions are used in any operations or calculations. So when in future you add a new transaction type, as long as it is covered with rules set you did set earlier, you don't have to redesign the database.

  14. #29
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    and between shipping and arrival registries (NB! Where real movements of services/goods are registered!).

    -- Sorry but what do you mean by this? What is NB? Thanks.

  15. #30
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Thomasso View Post
    and between shipping and arrival registries (NB! Where real movements of services/goods are registered!).-- Sorry but what do you mean by this?
    I mean that Transactions table registers real movements of real products in or out, or from one status to another. And arrival/shipping registers are similar and so best candidates for information exchange. At same time orders, invoices, etc. don't have any information, what happened to your products in reality - they are simply some bookkeeping info.

    What is NB? Thanks.
    Nota Bene!

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 03-11-2015, 08:13 PM
  2. Replies: 1
    Last Post: 11-05-2014, 11:08 AM
  3. Replies: 1
    Last Post: 02-21-2013, 12:18 AM
  4. Products Inventory Dilema,Add To Inventory
    By burrina in forum Forms
    Replies: 3
    Last Post: 12-02-2012, 12:10 PM
  5. What is the best way to handle photos?
    By TundraMonkey in forum Database Design
    Replies: 2
    Last Post: 08-12-2009, 10:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums