Results 1 to 5 of 5
  1. #1
    Etan77 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    2

    Database Design Help

    I used this forum several years ago and it was a big help, I long forgot my user name.

    I'm attempting to build a database to record the profiles of medical devices. I've got most of the design down but I got stuck.

    The medical devices use computer ports to communicate with other devices on our network. I would like to keep a record of the ports the device uses, my problem is some of the devices use a couple of ports, while others can use 15 or even more. I don't think it makes sense to add ports as headers so I was thinking of linking another table that would record the ports in rows and use some other kind of data to keep the reference to the device that used the port. The problem I'm running into is I have to enter the part number again and again and again.


    Click image for larger version. 

Name:	Capture.PNG 
Views:	15 
Size:	6.6 KB 
ID:	33203

    I'm not sure if there is a better way to design the database, I'm not in love with the design, or is there a way to enter the part number once and keep repeating it for a number of rows.




    I'm not sure if I described it clearly enough but any advice would be helpful.

    Thanks

  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
    Am I right in assuming you have separate tables for parts and ports with PartNumber being the PK in the Parts table and FK in the Ports table

    If you enter the ports using a form/subform then you won't need to keep entering the P/N manually

    You COULD instead use a multivalue field BUT that would not be a good idea.
    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
    Join Date
    Apr 2017
    Posts
    1,673
    Don't enter data directly into tables - use forms instead.

    Create a single form based on parts/medical devices table:
    tblParts: PartID (autonumeric, PK), PartNumber, ...;
    With tblParts as source create a form fParts with text box txtPartID hidden, text box txtPartNumber visible, ...;

    Create a contionous form based on parts ports table:
    tblPartPorts: PartPortsID (autonumeric, PK), PartID (FK), Port, ...;
    With tblPartPorts as source, create a continous form fPartPorts with text boxes txtPartPortsID and PartID hidden, and text box/combo box txtPort/cbbPort visible, ...;

    Open fParts in edit mode, and drag the form fPartPorts into some free place on it - a subform is created. When all is done properly, the subform is automatically linked with main form through PartID in main form (fParts) and subform. By default the subform will have same name as form which is it's source (fPartPorts), but it will be better to rename it as p.e. sfPartPorts. NB! The objects are ordered as fParts > sfPartPorts > fPartPorts.

    Now whenever you select a part in main form, all ports attached to it are displayed in subform. And when you add a new port into subform, it is automatically linked to to part active in main form (PartID is filled automatically in tblPartPorts).

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with the earlier posts and would add:

    -make a description of what the business is; start at 30000ft and add details
    -when your happy with the description (and colleague(s) agree)
    -make a model based on the subject matter and the business facts involved
    -identify significant outputs -- make some mock ups
    -create test data and scenarios
    -test your model to ensure it supports the facts and can produce/supply the outputs
    -with that as blueprint build the database.

  5. #5
    Etan77 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    2
    I don't know why I didn't think of the subform, thanks guy!

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

Similar Threads

  1. Replies: 18
    Last Post: 12-04-2017, 05:25 PM
  2. Replies: 3
    Last Post: 01-13-2017, 03:52 PM
  3. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  4. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM
  5. How would you design this database?
    By LBERG in forum Database Design
    Replies: 2
    Last Post: 10-24-2011, 02:51 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