Results 1 to 10 of 10
  1. #1
    Robbo943 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5

    Setting up a complex database

    I need to plan a database for work, i am a novice and never used vba; however if i can describe the principles similar to first aid kits.....



    IE

    We have first aid kits numbered 1-10
    Each first aid kit has the same products inside.
    Each product needs to have a description, batch and expiry number.

    I need to be able to see the history of each box: last audit, alert expiry, batch search in case of recall.

    Example (including report of each kit)

    First Aid Kit #1
    Earliest Expiry
    Audit No.

    Plasters, Batch 123, exp 12/20
    Dressing 1, Batch 435, exp 8/20
    Dressing 2, Batch 777, exp 6/20

    If its possible to have stock control from purchase to issue to first aid kits, that would be fantastic.

    Thanks

    John

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In very basic terms (no pen and paper was harmed in the planning of this, so I may have missed a trick);

    Code:
    You have First Aid Kits.    Table1 = tblKits
    You have Products.         Table2 = tblProducts
    You put Product in Kits    Table3 = tblKitProducts - This stores the product per kit, with the expiry / check dates
    You might want to consider a table to record the when and where of the Kits issued , this would record the dates and locations of the KitID's from Table 1

    tblKitAllocation
    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 ↓↓

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    As there will be same product from different batches in different kits, and even maybe in same kit, the DB structure will be more complicated. Probably something like:

    tblKits: KitID, ...; (My advice is to create also a fictional kit, which is working as storage for products not distributed out);
    Probably those kits are given out to some persons/users, and you need to follow this too.
    tblUsers: UserID, LastName, FirstName, ... (Along with real users, create a fictional user to register kits not given out too.);
    tblKitUsers: KIID. KitID, UserID, MovementDate ;
    Now about products.
    tblProducts: ProductID, Producer, ProductName, ...;
    tblBatches: BatchID, ProductID, ExpireDate, ...;
    tblProductLog: LogID, BatchID, MovementDate, [MovementType,] KitID, ProductID, ProductQty, ....

  4. #4
    Robbo943 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    Quote Originally Posted by ArviLaanemets View Post
    As there will be same product from different batches in different kits, and even maybe in same kit, the DB structure will be more complicated. Probably something like:

    tblKits: KitID, ...; (My advice is to create also a fictional kit, which is working as storage for products not distributed out);
    Probably those kits are given out to some persons/users, and you need to follow this too.
    tblUsers: UserID, LastName, FirstName, ... (Along with real users, create a fictional user to register kits not given out too.);
    tblKitUsers: KIID. KitID, UserID, MovementDate ;
    Now about products.
    tblProducts: ProductID, Producer, ProductName, ...;
    tblBatches: BatchID, ProductID, ExpireDate, ...;
    tblProductLog: LogID, BatchID, MovementDate, [MovementType,] KitID, ProductID, ProductQty, ....

    Thank you. What is the best way to manage stock? How does access calculate this best: queries, form controls or field ?

    Regards

    John

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Robbo943 View Post
    Thank you. What is the best way to manage stock? How does access calculate this best: queries, form controls or field ?
    You use tblProductLog for this. You have e.g. MovementType's for purchase, scrapping, internal movement in, and internal movement out.
    When you purchase some products, you are accounting it into storage kit as incoming movement;
    When you are scraping used, broken, or expired products, you are accounting it from storage kit as outgoing movement {it will be better to do all scrapping from storage kit - then your overall saldo for all products will be SUM(purchased qty)-SUM(scrapped qty)};
    When you are adding products into some kit, you need 2 entries - one for outgoing from storage kit, another for incoming into target kit;
    When you are removing products from some kit, you also need 2 entries - one for outgoing from source kit, another for incoming into storage kit.

    You can calculate various product saldos from tblProductLog - for store (storage kit), for any specific kit, or general saldo. You can also calculate saldos for specific patches. And queries are best tools for this.

  6. #6
    Robbo943 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    Quote Originally Posted by ArviLaanemets View Post
    You use tblProductLog for this. You have e.g. MovementType's for purchase, scrapping, internal movement in, and internal movement out.
    When you purchase some products, you are accounting it into storage kit as incoming movement;
    When you are scraping used, broken, or expired products, you are accounting it from storage kit as outgoing movement {it will be better to do all scrapping from storage kit - then your overall saldo for all products will be SUM(purchased qty)-SUM(scrapped qty)};
    When you are adding products into some kit, you need 2 entries - one for outgoing from storage kit, another for incoming into target kit;
    When you are removing products from some kit, you also need 2 entries - one for outgoing from source kit, another for incoming into storage kit.

    You can calculate various product saldos from tblProductLog - for store (storage kit), for any specific kit, or general saldo. You can also calculate saldos for specific patches. And queries are best tools for this.
    Sorry to be a pain:

    I am having trouble calculating a query. I need the result of two columns SumOfProduct QTY In - SUMOfProduct QTY out and then can I use this in forms?

    Regards

    John
    Click image for larger version. 

Name:	example.PNG 
Views:	23 
Size:	67.7 KB 
ID:	35699

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    With such structure, what you mean with 2. row? are you moving the product from store to BAG2, or out from BAG2?

    No need for 2 quantity fields. movement between 2 locations (stores and BAG2) must always have 2 records (or a single record with one quantity field, and 2 location fields - one incoming, another outgoing). The sign of quantity is entered with quantity, or is determined by MovementType.

    Like (MovementType values are 1 for purchase, -1 for scraping, 2 for internal incoming and -2 for internal outgoing)
    Code:
    BatchID     MovementDate,  MovementType, KitID,      ProductID,      Qty
    1233         04/10/2018        1         Stores      CSKU1980        20
    1233         04/10/2018       -2         Stores      CSKU1980        3
    1233         04/10/2018        2         BAG2        CSKU1980        3
    1233         04/10/2018        1         Stores      CSKU2020        20
    Now the query to calculate saldo of all products in stores will be
    Code:
    SELECT ProductID, SUM(MovementType*Qty) As StockQty FROM tblProductLog WHERE KitID = "Stores" Group by ProductID
    Calculating the total number of all products together doesn't give you anything useful! Who cares that total number of cars and pears you have is 10 Having 10 pears is not too much

    The query to calculate the current number of every product in every kit will be
    Code:
    SELECT KitID, ProductID, SUM(MovementType*Qty) As StockQty FROM tblProductLog WHERE KitID <> "Stores" Group by KitID, ProductID

  8. #8
    Robbo943 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    But once I have an answer, how can I export that to a form?

    IE the query has found that I have 20 plasters in stock. Lets say I want a template stock information (product details) as a table and would like the current stock placed in that form.

    For example:


    Click image for larger version. 

Name:	example2.PNG 
Views:	25 
Size:	172.4 KB 
ID:	35701

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    Robbo943 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    Thanks, i've seen this a few times and working through pitman training work book and also another book. The problem I have is connecting and calculating the data.

    I can calculate in tables and forms (basic) however when it comes to calculating stock or using results of queries, I struggle. I cant find much on you tube to help! I'm better at fixing people than I am learning formulas

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-16-2013, 12:40 AM
  2. Very complex database
    By adryan_g78 in forum Database Design
    Replies: 20
    Last Post: 12-16-2011, 10:30 AM
  3. Setting Up Complex Relationships
    By seanbhola in forum Access
    Replies: 3
    Last Post: 05-15-2011, 11:47 PM
  4. Replies: 1
    Last Post: 03-06-2011, 06:21 PM
  5. Complex query system for map database
    By brian.tunks in forum Queries
    Replies: 2
    Last Post: 07-28-2010, 07:07 AM

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