Results 1 to 4 of 4
  1. #1
    Sleepy81 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2016
    Posts
    2

    Question Newbie, specific tables and form design question.

    Hi!




    I am new to Access, but have a very specific problem for a database I am trying to create for work.

    I was hoping maybe somebody here could spare a few minutes to look at it and come up with some ideas or feedback for me to solve these problems...


    Since the data itself is confidential, I have elected to substitute the data with dummy data.

    I have two problems that I cannot figure out:


    First, the Database.
    I have some problems on how to design this database, and I am not sure if I have done it correctly or if it is a better way to do it.
    See attached picture, and i will explain each field below:


    Click image for larger version. 

Name:	Skjermbilde 2016-02-13 21.43.02.png 
Views:	14 
Size:	36.1 KB 
ID:	23665


    System ID: Autonumber


    Computer System: Name of Computer System in Short Text


    Program Name: Each Computer System can have one or more programs, called Program Name.


    Sub Prog A/B/C:
    Each Program Name has one or more Sub Programs.
    A Program Name will always have at least one Sub Prog, but it might not always be Sub Prog A. I.E: A Program Name might have a blank Sub Prog A, and a value in Sub Prog B and C, or blank in A and C, and a value for B, etc. etc. It can also have a value in all Sub Programs. (A, B and C)


    Now here is the root cause of the main problem I have:
    The value of the Sub Programs are not unique to the Program name. It is however unique to the combination of Program Name and Computer System.


    So as shown, Space Invaders has Sub Prog A: Ver A, Sub Prog B: Ver B when it is used on the Atari system.
    But, Space Invaders has Sub Prog A: Ver A, Sub Prog B: Ver A if it is on the C64 system.


    Ideally, I would like to have a separate table for Computer System, and another table for Program Name and Sub Programs.
    However I have not found a way to connect these tables properly, hence I have ended up with a single table for now.


    The problem with that, is that I end up with several records with the same value for Computer System... I would like it to be only one record per system, but that would require me to find a way to split the data into two tables with the propper relationship between them.




    The Forms Problem.
    After I sort out the tables problem, I am still left with a Forms problem.
    See attached picture:


    Click image for larger version. 

Name:	Skjermbilde 2016-02-13 21.43.27.png 
Views:	13 
Size:	34.1 KB 
ID:	23666



    I want to be able to have a Combo Box for Computer system. This Combo Box must only hold unique systems, i.e. no duplicate names.


    Then, after the user has selected the Computer System in the top combo box, the Program Name combo box should be populated with the Program Names that are applicable to the selected Computer System.


    Lastly, when the user selects the appropriate Program Name in the combo box, the Sub Programs should be displayed below, as shown.


    Hope somebody can have a look at this and come up with some smart solutions, as I apparently am not smart enough to do it myself!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    As soon as I see repeating Field Names (prog1, prog2, prog3, etc.) I suspect a normalization issue. You should strive to get your db into 3NF if you can. Google Normalization and if you need additional assistance I have a few links I could share. This may seem like a put off but if the db is not at least 3NF then you will have difficulties all along the way.

  3. #3
    Sleepy81 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2016
    Posts
    2
    Thanks! I have watched a few YouTube videos and reed a few articles about 3NF now, so I think I have understood it. Will give it a stab later this evening.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Good luck and have fun.

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

Similar Threads

  1. Newbie Pre-Design Question
    By JamieB in forum Database Design
    Replies: 13
    Last Post: 10-18-2013, 05:20 PM
  2. Newbie with database design question one to many
    By djmcats in forum Database Design
    Replies: 9
    Last Post: 10-02-2013, 01:40 PM
  3. Replies: 14
    Last Post: 03-22-2013, 06:35 PM
  4. Replies: 4
    Last Post: 08-13-2012, 04:39 PM
  5. Form Design Question with DLookup (newbie)
    By wongc61 in forum Access
    Replies: 4
    Last Post: 07-08-2011, 03:22 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