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:
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:
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!