Results 1 to 3 of 3
  1. #1
    Ruy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    2

    help with database design

    Hi, I take this opportunity to greet you all, for it is my first participation in this forum





    I am an inexperienced Access user and have recently started designing a database in my company. I'm making a database in which I have 5 tables:



    - A table with customer data (key field "customer code")
    - A table with order data (key field "order", is an order number) where customer and order data appear (there is no product data here)
    - A table with order and product data (2 key fields: "order" and "product code"), where product data for each order appears.
    - A table with product data (key field "product code") with all existing products and their associated logistics.
    - A table with data of the packaging area and incidents. (key "order" or/and "product code" field), where product data for each order (product code only) and associated incidents appear again.
    My intention is to make an application with a form consisting of 3 tabs (tabbed form, following a video that I have seen on youtube): one for general order data, another tab with products and quantities, and another tab with data and incidents arising in the area of Packaging.



    To do this, I will make queries with several tables for each form (one query for the order data, another for and insert 1 main form (with general order data), another subform (with product and quantity data) and a third subform (dependent on the other two) with data for the packaging area. For this application, I need to set passwords to form 1 (orders) and 2 (product relationship of the order), and leave free access to form No.3 (packaging data). I'm interested in making a separate table with the packaging area data because I think to give permissions to the users of each form, I have to do the 3 forms separately.
    The main difficulty is that I don't quite know how to join the table with data from the packaging area to the other tables. When doing the different combinations and testing queries from multiple tables to make the subforms (for possible incidents, in the query I use the "order" field of order table, field "product code" of table products orders, field "incident" packaged data) , I can not retrieve data, because I get the fields from the table result of the blank query and the form also appears blank.



    In my (inexperienced) opinion, the sequence of relationships should be:
    - Customer table customer code (relationship 1 to many) with order table order
    - Order table order order (relationship 1 to many) with item code of table products-orders
    - Product code of ordered product table (ratio 1 to 1 with) with table product code packaging data
    - Packaging data table product code (many to 1) with product code of product table.



    Notes: I think the product code is repeated too much as it serves as a join in 2 relationships and I have seen that the key field is not usually repeated as a join. I have doubts whether the relationship between product code of product ordering product and product code of packaging data table should be 1 to 1.

    Thanks in advance

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Ruy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    2
    Yes, sorry, I don`t usually discuss in forums

    I read your link in the other post about crossposting and I agree with it, thanks, so I prefer mark this post as solved to discuss in the other.

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

Similar Threads

  1. Replies: 9
    Last Post: 06-23-2018, 10:25 PM
  2. Replies: 18
    Last Post: 12-04-2017, 05:25 PM
  3. Replies: 3
    Last Post: 01-13-2017, 03:52 PM
  4. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  5. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM

Tags for this Thread

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