Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2012
    Posts
    4

    Unanswered: One to One Relationship and a Subform

    Hello

    I am building a database that stores project information. There are several types of projects, and each of these projects has a different set of project information based on what type it is.

    I have built a table (tblProject) that houses all of the Project info that is the same for all projects (project number, address, basic info, etc), another table (tblProject_Type) that houses project type (there are 4 or 5 types), and then I will build tables that house information specific to the project type (tblProject_[type of project]).

    I have a lookup on tblProject that chooses a Project Type from tblProject_Type. I want this to feed into the project type table. I would like the tblProject_[type of project])

    For example, let's say I have a project that is Capital. I would like to choose "capital" from the drop down, and then have a table called tblProject_Capital that houses information that is different than other projects.

    Finally, I would like to be able to enter all of this information on one form. I already have a project form that I like, and I have a tab for "Capital" I want all of the info from tblProject_Capital to be on this tab.

    When I put everything from this table onto that tab, and I go into Form View, it only shows one record and I can't pull up any information. I can't figure out what I've done wrong! If someone can help, that would be awesome.

    Thanks!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The first indication of a problem was the thread heading. A one-to-one relationship is usually an indication of unnecessary structure.

    You only need two tables: tblProjectInfo and tblProjectType. tblProjectInfo should have a field called PType (or similar) which should be a lookup to tblProjectType. TblProjectType only needs two fields: PType and PDescription. These are both self-explanatory.

    PType should be the PK for tblProjectType, and FK in tblProjectInfo. You then have a many-to-one relationship from tblProjectInfo to tblProjectType.

    Sam

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry I disagree
    This model seems fine perhaps the language don't help but right keeping common project stuff in a parent table and the rest in discrete sub tables seems fine to me assuming that the 4 sub tables contain different types of data. If they contain the same (or largely) the same type of data then that goes out of the Window.

    If you are still struggling with this and I have time I look it again this week
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I bow to healdem. He has more and better experience than I. However,
    keeping common project stuff in a parent table and the rest in discrete sub tables seems fine to me assuming that the 4 sub tables contain different types of data
    even in this case, it still wouldn't be a one-to-one relationship; it would still be many-to-one.

    Sam

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sometimes its just terminology
    If you have a single entry in the project header and a single entry in the project sub type, then its still a one to one relationship. if you have multiple entries int he project sub type then its a one to many relationship

    you can define it as a one to one realtionship.. that gets round the issue of potentially having two or more rows for a single project in a single (sub) table. however that doens't get round the problem of having the same project header being linked to different sdub tables... you'd need to do that either in the form doing the data capture and/or using a trigger to enforce a check constraint to be hionest I've not done much Access stuff recently so have not had the need to use Triggers so I don't know if they have been introduced... web seems to say so on more recent versions but Ive never used 'em so don't know if they exist or work.

    As to how you design the user interface for this is up to you. The classic way of doing this in Access is to use sub forms for the child table, embedded in the parent form. if the sub forms are mutually exclusive you dont' have to use Tabs, 'just' display the correct for as required.. to do that alter the subforms visibility.

    there isn't enough information here to diagnose why you are only seeing one row and cant add rows... thats almost certainly down to the query
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I dunno. To my mind it's simple. Since a project can only be one type, and there can be many different projects that are the same type, the relationship is defined as many-to-one.

    Sam

  7. #7
    Join Date
    Dec 2012
    Posts
    4
    Hello

    Thanks for your help. Yes that does seem right. I would have one project#. All of the basic info would be in tblProject and info specific to the project type is in the second set of tables.

    I think at this point I'm struggling with the relationships portion of everything. I think that I'm overcomplicating this! As it is I have the following links:

    tblProject.ProjectType is in a many-to-one relationship with tblProject_Type.ID (which is the primary key). This resulted from creating the project type as a lookup.

    tblProject_Type.ID is in turn in an undefined relationship with tblProject_Capital.ProjectType (Which is not the primary key for that table). Would it be better to have a one-to-many relationship with tblProject_Capital.ID which is the primary key?

    The big problems start when I try to link tblProject with tblProject_Capital. I wanted to do a one-to-one relationship between tblProject.ID and tblProject_Capital.ID which are both the primary keys but when I do that the forms stop working!

    Thanks again for your help!

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    tblProject_Type.ID is in turn in an undefined relationship with tblProject_Capital.ProjectType (Which is not the primary key for that table). Would it be better to have a one-to-many relationship with tblProject_Capital.ID which is the primary key?

    The big problems start when I try to link tblProject with tblProject_Capital. I wanted to do a one-to-one relationship between tblProject.ID and tblProject_Capital.ID which are both the primary keys but when I do that the forms stop working!
    What info do you have in tblProject_Capital that you can't have in tblProject_Type, since you anyway have a one-to-one relationship? Any project detail is already in tblProject, so there's no changing info in tblProject_Type from project to project for the same project type.

    One other thing. You can't force Access into giving you the kind of relationship you want. At least not in XP; I'm not so sure about later versions, but I doubt it. Access gives you the relationship it knows best, based on the structures of the tables involved.

    Sam

  9. #9
    Join Date
    Dec 2012
    Posts
    4
    tblProject_Type is a dropdown that you can choose the project type from. The table I am working on first deals with Capital projects, and another table will deal with Research, and they are quite different. However, they will have some of the same data, such as Project number, contact info, mailing address, etc.

    I guess the alternative, which you are suggesting, would be to have one GIANT table with all of the information and leave information that is not relevant to a project blank? This seems like it isn't the best way to organize data, but I could easily make it work.

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I guess the alternative, which you are suggesting, would be to have one GIANT table with all of the information and leave information that is not relevant to a project blank? This seems like it isn't the best way to organize data, but I could easily make it work.
    That is what I'm suggesting. The second table, as I posted earlier, can contain a basic description of a Capital or Research project, and the ptype PK.

    If you feel the text fields for the project details are too long, you can always go to a Memo field instead, which won't get in your face so much.

    Sam

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    effectively this is a sub/supertype model.
    its perfectly legit, if the model requires it to have a common project master, and sub tables holding what is different for the different project types. in that role there is one row for each project in the top table, and one row in one of the the other child tables.. its a one to one relationship

    but it only makes sense if there is substantial difference between each of the (in this case) 4 project types. if most of the data is similar then it probably should be in one table.

    what we don't know is the reasons for splitting the project types into 4 separate entities. it may be not neccessary at all

    you could have project details in tables below the sub tables if required (where it would almost certainly be a one to many relationship

    as a ferinstance:-
    the project header contains all that is common to all projects (say title, date, completion date, project manager etc...
    a capital project may requitre a series of columns which would be irrelevant to another type of project
    so you'd have a single row in the capital projects table. a one to one relationship, AND you'd exclude rows in other tables for that same project. there are not multiple rows in for that same project in the capital projects table as only one row covers that need.

    If you stuffed all the columns in the project table you'd have the potential of lots an lots of null columns for data that isn't relevant to the other 3 project types.

    again its all theoretical. if the OP is creating 4 sub tables for the heck of it as say most of the data is the same between the 4 project types then its a pointless exercise. it depends on the data, it depends on the requirements.

    gong down this route can complicate matters as if you need sub sub tables then you need a sub table of each type for each project sub type, and so on.

    in the real world I find it relatively rare to come accross one to one relationships unless there is a performance or security issue. eg to get round Access limitations of around 250 columns, or to pass some obscure audit requirements where certain data cannot be be seen by certain types of users (which always begs the question why the feck are you doing this in Access/JET in the first place )
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Dec 2012
    Posts
    4
    So as I see it I have two options:
    1. figure out subtables. This would help me separate my data, which is distinct enough to warrant separting into the different project types, but I could run into problems down the road (And I already am since it is causing problems now!)
    2. put all of the data into one giant table. This would mean that there are a LOT of null fields for project info that isn't relevant to a project.

    I don't have a problem with the second option and it would actually help me move along immediately. Will I run into any problems down the road with having so many null fields?

    Thanks again for your feedback!

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    will you run into problems down the road... who knows
    there are several sources of problems
    if you get close to an internal Access limit (the most likely one would be around 250 columns in a table or query)
    if someone decides to that there should be a 5th, 6th or nth project type, becuase you will hot problems at a later stage

    nulls aren't a problem in a table (or query), they can be a source of problems in a form or report, especially if you intend to do some maffs with NULL columns, but that can be amelorated using the NZ function and or COALESCE function

    effectively you already have sub tables. personally I don't see a problem with what you are proposing.. its looks 'right' to me. but thats based on very limited data


    ferinstance I don't know what your 4 project types are, and I don't know what differentiates a Captial project form a non capital project so I don't know if your design is actually warranted / neccessary / proven.

    Bear in mind that there is an awful lot of theory and idealism in the systems world, when in reality all the user wants is something that works. unless your users need the 4 sub table per project type then don't do it (unless you have all the time int he world to implement it)

    read up on the sub / supertype model a nd decide if you actually need it.

    if you implement a one to one relationship then the primary key would have to be the same in BOTH tables. to implement that you'd have say an autonumber PK in PROJECT and a LONG number in the sub-tables (which will ne the same as the Project PK. you cannot have an autonumber PK in both, and in reality you'd loose the db enforcing only one project number per project type (unless you declared an index on project ID in each of the subtables.

    Bear in mind you don't need to use an autonumber column to use as a primary key. if there is a so called 'natural' key (ie something that exists already in the data that uniquely identifies a specific row then you can (and should) use that). that can be a single column or a composite of more than one column). therre are times when you might want to use a so called surrogate key (an autonumber column) even when there is a suitable 'natural' key (usually thats becuase of performance issues .... if you get to many columns to make a PK sometimes its just easier to use an autonumber column)

    in this case you have potentially
    an autonumber PROJECTID, a 'surrogate' key
    the same PROJECTID in each of the 4 project categories, but in this case its a 'natural' key

    If you can I'd suggest persevere with your current model, rather than have one big table. but be aware it could well increase the time required to complete the system
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are getting stuck then an option would be to strip out any sensitive data in your db, compress then post the zip file here as an attachment, and outline what the problem you are struggling with is
    I'd rather be riding on the Tiger 800 or the Norton

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
  •