Results 1 to 7 of 7
  1. #1
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Posts
    528

    Relations tables


    Hi All
    Is it possible to explain this relationship is and how it works, I really do not know who is the table in which the quantity of basic .
    Click image for larger version. 

Name:	gg.PNG 
Views:	16 
Size:	21.7 KB 
ID:	17634

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I do not understand what you mean by "quantity of basic".

    Is this someone else's database that you are trying to use as a template? I will go out on a limb and say this is not a very good design for Inventory entities. Maybe if it is intended for use over the internet. For desktop databases, it is usually a bad idea to store calculated data in the tables. I see a lot of quantity fields in various tables. This will create issues when designing your app and trying to maintain constraints/data integrity.

    Having said that, newer versions of Access have many constraints that can be enforced using intrinsic features at the table layer.

  3. #3
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Posts
    528
    Thank you, Professor
    How can I manage and control inventory
    Quantities that enter and quantities that come out

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I use Yes/No fields at the Child table. The Parent table would be something like a customer request or an Order to bring inventory IN. A child table would describe what is in the Order/Request. There may be other child tables that break down the inventory further. Perhaps the Order/Request describes multiple trucks and or multiple pallets. Child tables will describe what is inside a given truck/pallet. Use the yes/no fields at the lowest Child to indicate if something is IN inventory or not.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The basic structure looks good to me. I can't identify any calculated fields.

    To determine products on hand, do aggregate queries that summarize the tblAcqDetail and tblInvoiceDetails and tblStockTake records then join those queries to tblProducts. Do calc in query that subtracts sums of invoice details and stock take from sum of acquisitions.

    Take a look at the MS Desktop Inventory database template. http://office.microsoft.com/en-us/te...010206882.aspx

    Also, http://allenbrowne.com/AppInventory.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If you take a look at June's link to Allen Browne's site, you will see that he explains the peril of storing a "quantity on hand" in your DB (towards the bottom of the page).

    I did not look too closely at the link but, perhaps you and I are interpreting the Quantity field as a 'quantity on hand" when, in fact, it is a description of that row/record. In other words, a count of pieces in a case, where, according to the business rules, the case would never be divided. Unfortunately, I do not have the time to review what Allen has there.

    Any example that I have for an inventory system would be very complex and not worth much as a model. All of my inventory DB's are a part of a larger Warehouse Management System. The models are specific to types of products and complex business rules.

    The only other thing I can suggest, besides the links June offered, would be to look at the Cascading Combo example I uploaded. It is not a sample DB for an inventory system. It is a sample of various approaches to using VBA. If I recall correctly, there is some use of yes/no fields in tables. When using multiple child tables, I will violate rules of Normalization and include the Primary Key values of relative fields from ALL Parent tables in the Child tables.
    https://www.accessforums.net/sample-...tml#post200624

    I have not looked at that sample DB in quite a while...

  7. #7
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Posts
    528
    Thank you very much
    This is an example, where relations have been built on the light by Allen Browne
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 3
    Last Post: 03-06-2013, 02:33 PM
  2. Table with more relations...
    By mr3sn in forum Access
    Replies: 2
    Last Post: 02-06-2013, 02:06 AM
  3. Relations
    By Frasse in forum Database Design
    Replies: 3
    Last Post: 08-20-2012, 06:11 AM
  4. Combining tables and keeping relations
    By Bangsadrengur in forum Access
    Replies: 7
    Last Post: 07-28-2011, 08:12 AM
  5. one to one relations
    By crackpot in forum Database Design
    Replies: 2
    Last Post: 08-18-2010, 09:39 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