Results 1 to 11 of 11
  1. #1
    carduel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    7

    School assignment

    Hello friends,



    can you help me solve a task, please.

    I have major companies and subsidiaries which belong to them. Each major company has its own unique number. Each subsidiary has its own unique number. All buy same products that has its own unique number and can only belong to one major company or subsidiary.

    I have 3 tables (tbl_major_companies, tbl_products and tbl_subsidiaries).

    In tbl_major_companies are:

    1. precision company
    2. dell company
    3. hp company

    In tbl_subsidiaries are:

    1. precision company London (belong to
    1. precision company in tbl_major_companies)
    2. dell company Milano
    (belong to 2. dell company in tbl_major_companies)
    3. hp Germany
    (belong to 3. hp company in tbl_major_companies)
    4. dell company Paris
    (belong to 2. dell company in tbl_major_companies)
    5. ...
    6. ...
    etc...

    In tbl_produsts
    1. hard disk (only hard drives wich has different serials number in tbl_products (productID - primary key)

    One major company has its own unique number (13 numbers) and have more than one subsidiaries.

    How to link these tables? Whether two or three tables are to be used?

    This makes me confused and would ask for help.

    Thank you very much.

    Attacachment:
    http://carduel.com/wp-content/uploads/2018/01/test-1.rar


    Last edited by carduel; 01-16-2018 at 07:54 PM.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    CarDuel-test-v1.zip
    Try this as a starter to link your major and subsidiary companies.
    There's no commonality established for Products. How do they relate?
    If your companyID and subsidiaryID and PRoductID are made up (just for keys), you can delete them and use the autonumbers I added for the primary keys.

  3. #3
    carduel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    7
    Thanks for help!

    The form you have made connects the major and their subsidiaries companies in subform.

    The Major can buy a product that has a unique number and this product can not be bought by any other company (majors or subsidiaries).

    It should be a form where it is possible for each major company and their subsidiaries to enter this product.

    Also I need an individual report for all companies (all major and subsidiaries) and group report for each major and their subsidiaries.

    All major company may have more products and subsidiaries too.

    This is group report for major and their subsidiaries:

    Major 1
    1. product no. 344
    2. product no. 345
    3. product no. 346

    Subsidiaries 1
    1. product no. 347
    2. product no. 348

    Subsidiaries 2

    1. product no. 349
    2. product no. 350

    etc....

    This is individual report

    Major 1
    1. product no. 344
    2. product no. 345
    3. product no. 346

    or

    Subsidiaries 1
    1. product no. 347
    2. product no. 348

    etc...

    If You could solve this, I would be vary grateful to You.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Too many questions about specs:
    Is the product number pre-assigned to a company or is it reserved by first company to buy?
    Can a company or subsidiary buy more than one of the same product?
    Can 2 subsidiaries of the same major buy the same product?
    Is a major or a subsidiary required to buy at least one product?

    These are all questions (and others that will crop up when you block design and test on paper) that have to be considered in coding the app.
    It appears to me that you still have work to do in finalizing specifications. That's your school project.

  5. #5
    carduel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    7
    Quote Originally Posted by davegri View Post
    Too many questions about specs:
    Is the product number pre-assigned to a company or is it reserved by first company to buy? - No, the first company to buy a product is her.

    Can a company or subsidiary buy more than one of the same product? Yes but with a different serial number of product.
    Can 2 subsidiaries of the same major buy the same product? Yes but with a different serial number of product.
    Is a major or a subsidiary required to buy at least one product? No. First You enter the product information (hard disk s/n: 1234, hard disk s/n: 5216, hard disk s/n: 7485) in product table than give it to a company (one of the major or subsidiary). If the major or subsidiary does not exist in the system, it must be entered and then assign it a product. The product is always the same (hard disk) but oech has a different serial number.

    These are all questions (and others that will crop up when you block design and test on paper) that have to be considered in coding the app.
    It appears to me that you still have work to do in finalizing specifications. That's your school project.
    I've put the answers in quotation marks.

  6. #6
    carduel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    7
    Table products:

    -productID
    -productUniqueLabel (wdcs020301 = wester digital computer store 020301, sgpcs156346 = seagate 156346 personal computer store,...)
    -productName
    -SellerID

    Table Sellers:
    - sellerID
    - sallerName
    - sallerUniqueLabel (cs = computer store, pcs = personal computer store, ...)

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Refer back to post #4. You have given thought to your design and it is evolving.
    It appears to me that you still have work to do in finalizing specifications.

  8. #8
    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
    You might consider the attached overview draft model for tables/relationships. I created this based on the comments in the thread. (You can ignore the integer NN stuff in the boxes--it's just part of the tool I used and the layout)
    Click image for larger version. 

Name:	CompSubPurchaseProductsFromSellers.png 
Views:	22 
Size:	20.4 KB 
ID:	32221

  9. #9
    carduel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    7
    It is difficult to explain the problem since English is not my language. I've put the report out, so they can help You more than my text.

    Reports

  10. #10
    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
    If English is not your native language, you can write your post with details in your native language, then use Google translate to convert from your language to English, then post your message. Google translate is free and is certainly more than adequate for communication on the forum.

    The draft model was just meant as a starting point for consideration. I did not deal directly with the Company/Subsidiary hierarchy. You could consider a large box that includes Company and Subsidiary and call it Purchaser from a concepts perspective.

    Good luck with your project.

  11. #11
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Based on your original post this is how I would organize / normalize:


    tbl_major_companies
    CompanyName (PRIMARY KEY - Cascade on update)
    Precision
    Dell
    HP
    CompanyID
    13 digit number
    13 digit number
    13 digit number

    tbl_subsidiaries
    SubsidiaryID (PRIMARY KEY - Cascade on update)

    CompanyName (FOREIGN KEY)

    Country
    Britain
    Italy
    France
    Germany

    City
    London
    Milan
    Paris
    German City?

    tbl_products
    ProductSerial (PRIMARY KEY - Cascade on update) changed ProductID to ProductSerial as more decriptive and less redundant (i.e. it is already unique)

    ProductName

    CompanyName (FOREIGN KEY) or SubsidiaryID (FOREIGN KEY) depending on how you track your data


    The following is based on additional information you provided later on:

    tbl_sellers
    SellerID (PRIMARY KEY - Cascade on update)

    SellerName

    SellerType I changed this from SellerUniqueLabel because it is not a unique label

    SellerCountry I added this

    SellerCity I added this


    tbl_sales I added this table to join tbl_products and tbl_sellers
    SalesID (PRIMARY KEY - Cascade on update)

    SellerID (FOREIGN KEY)

    ProductSerial (FOREIGN KEY)

    UnitCost

    The tables are linked by the attributes with primary and foreign keys. Just be sure to set primary keys to cascade on update.

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

Similar Threads

  1. Assignment
    By Michael_25 in forum Programming
    Replies: 13
    Last Post: 02-17-2015, 06:47 PM
  2. Access Assignment
    By IsaacA95 in forum Access
    Replies: 1
    Last Post: 08-28-2013, 01:09 PM
  3. Help Designing a database for a school assignment
    By nl49 in forum Database Design
    Replies: 1
    Last Post: 02-10-2013, 07:18 AM
  4. Vehicle inventory/assignment
    By jzacharias in forum Database Design
    Replies: 2
    Last Post: 10-11-2012, 02:39 PM
  5. Not lookup, but assignment
    By ArmyLT in forum Database Design
    Replies: 3
    Last Post: 11-17-2011, 02:35 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