Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Basic Form/Subform question

Discussion in 'Information Technology' started by Rocky, Oct 27, 2005.

  1. Rocky

    Rocky
    Expand Collapse
    Guest

    My problem is a basic one of trying to understand how to construct drop-down
    lists. I am very confused about what Access does with drop-down lists. Let
    me state my understanding, and somebody please tell me if I am right or
    correct me if I am wrong.
    In Access Tables, the use of the Lookup Wizard causes problems because an ID
    number is referenced instead of the actual data value, and at some point this
    might cause a problem if queries are dependent upon the data in a Lookup
    field (this is regardless of whether it is a Lookup Field or a Value List.)
    On the other hand, Subforms within a Main Form can be used to enter data
    from a drop down list but require an underlying Relationship between two
    tables.
    However, relationships need to be created with a junction table if the
    tables exist in a many-to-many relationship.
    An example may help. In a patient database, the gender is asked for. How
    to create a drop down list in a subform with “Male†or “Female†as the
    choices? There are two underlying tables, “tblDemographics†and “tblGenderâ€.
    The two tables will obviously have a many-to-many relationship, and
    according to what I have read, will need a junction table to establish this
    relationship.
    However, in my reading it seems that Forms and Subforms relate on a ‘one’
    (the MainForm†to ‘many’ (the SubForm) relationship.
    If everything I have stated is true, how can a Form/Subform be constructed
    for the example given?
    The same question holds for other similar drop down lists. Another example,
    a drop down list for a clinical score consisting of 6 choices of clinical
    condition. Again obviously a many-to-many relationship.
    Also, please let me know if there is some easier way to perform this task
    that I am overlooking.
    Tx
     
  2. Loading...

    Similar Threads Forum Date
    Basics Of Sikhi Jagraj Singh Diagnosed With Stage 4 Cancer Sikh Organisations Thursday at 4:12 AM
    Basics Of Sikhi Sikh Sikhi Sikhism Jul 3, 2016
    Sikhism Guru Gobind Singh’s Ideal of Khalsa Commonwealth: A Continuum of Basic Philosophy Sikh Sikhi Sikhism Jan 16, 2016
    Islam Dawah Man and Basics of Sikhi [Video] Interfaith Dialogues Dec 5, 2013
    Basic Questions about Converting to Sikhism Questions and Answers Nov 15, 2013

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 26 Oct 2005 14:49:03 -0700, "Rocky"
    <Rocky@discussions.microsoft.com> wrote:

    >My problem is a basic one of trying to understand how to construct drop-down
    >lists. I am very confused about what Access does with drop-down lists. Let
    >me state my understanding, and somebody please tell me if I am right or
    >correct me if I am wrong.


    Ok... will try...

    >In Access Tables, the use of the Lookup Wizard causes problems because an ID
    >number is referenced instead of the actual data value,


    No. It's the opposite. The actual value stored in your table *IS* the
    ID number; that basic fact is concealed from your view by the combo
    box, which is displaying a DIFFERENT value (usually text) from a
    DIFFERENT table. What you *see* is not what is *there*.

    >and at some point this
    >might cause a problem if queries are dependent upon the data in a Lookup
    >field (this is regardless of whether it is a Lookup Field or a Value List.)


    Not if the query is done correctly. Whether or not there is a Lookup
    Field in the table, you can choose to include the lookup table in a
    Query. If you need to see the looked-up text in a Report, say, simply
    join your main table to the lookup table (or tables), and include the
    field from there.

    >On the other hand, Subforms within a Main Form can be used to enter data
    >from a drop down list but require an underlying Relationship between two
    >tables.


    Well... yes, but Combo Boxes ("drop down lists" proper name) can be
    used on a mainform, on a subform, or both. There really is no
    connection between using a subform and using a combo box; they're just
    two different tools.

    >However, relationships need to be created with a junction table if the
    >tables exist in a many-to-many relationship.


    Relationships need to be created if you have a ONE to many
    relationship, too! Creating a Lookup Field actually DOES create a
    relationship, and then conceals it from view; one of the criticisms of
    the feature is that it creates such a relationship, and the
    accompanying indexes, even if a relationship already exists!

    I would recommend creating all your Tables, *WITHOUT* lookup fields;
    creating all your Relationships, in the relationships window; and
    using Combo Boxes liberally as needed on forms. I will confess that
    the one advantage of having a Lookup Field in a table is that it's a
    mite easier to put a combo box on a form - but it's dead easy even
    without.

    >An example may help. In a patient database, the gender is asked for. How
    >to create a drop down list in a subform with “Male” or “Female” as the
    >choices? There are two underlying tables, “tblDemographics” and “tblGender”.
    > The two tables will obviously have a many-to-many relationship, and
    >according to what I have read, will need a junction table to establish this
    >relationship.


    Ummm...

    No.

    One person will not have many genders (unless you're in a very
    particular medical specialty). You have a very simple one to many
    relationship here: each record in tblGender will be related to many
    recrods in tblDemographics, but each person in tblDemographics will
    have only one gender. In fact, unless you're dealing with multiple
    gender identities, I wouldn't bother having a tblGender at all; just
    use a Combo Box on the form with a List of Values "Male";"Female".

    >However, in my reading it seems that Forms and Subforms relate on a ‘one’
    >(the MainForm” to ‘many’ (the SubForm) relationship.


    That is correct. In a real many to many relationship (for instance the
    Northwind Sample databases OrderDetails table) there is are three
    tables involved: Orders (an Order can consist of many Products),
    Products (each Product can be part of many Orders), and OrderDetails.
    There is a one to many relationship from Orders to OrderDetails (used
    on the form, using OrderID as the linking field), and another one to
    many relationship from Products to OrderDetails.

    >If everything I have stated is true, how can a Form/Subform be constructed
    >for the example given?
    >The same question holds for other similar drop down lists. Another example,
    >a drop down list for a clinical score consisting of 6 choices of clinical
    >condition. Again obviously a many-to-many relationship.


    This is a very different issue from Gender. Each patient has only one
    gender, but (I presume) each patient might have zero, one, two or more
    conditions.

    There you need a THIRD TABLE. If each Patient may have multiple
    Conditions, and each Condition may affect many Patients, then you need
    a PatientConditions table with fields for the PatientID and the
    ConditionID (linked to the respective tables' Primary Keys). The
    Subform would be based - not on Conditions - but on PatientConditions;
    you'ld have a Combo Box on the subform based on Conditions allowing a
    different condition to be selected for each record. You might have
    another field in PatientConditions, say a Severity value (which again
    might use a lookup table - though not a Lookup Field - listing the
    valid severities), perhaps a Memo or Text field for comments, date of
    onset, etc. as needed for your application.

    >Also, please let me know if there is some easier way to perform this task
    >that I am overlooking.
    >Tx


    See if this description helps...

    John W. Vinson[MVP]
     
  4. Rocky

    Rocky
    Expand Collapse
    Guest

    John,
    I'm reading your response late at night, and it seems to make sense, but
    I'll let you know tomorrow if it really did!
    thank you for your time and effort
    Rocky

    "John Vinson" wrote:

    > On Wed, 26 Oct 2005 14:49:03 -0700, "Rocky"
    > <Rocky@discussions.microsoft.com> wrote:
    >
    > >My problem is a basic one of trying to understand how to construct drop-down
    > >lists. I am very confused about what Access does with drop-down lists. Let
    > >me state my understanding, and somebody please tell me if I am right or
    > >correct me if I am wrong.

    >
    > Ok... will try...
    >
    > >In Access Tables, the use of the Lookup Wizard causes problems because an ID
    > >number is referenced instead of the actual data value,

    >
    > No. It's the opposite. The actual value stored in your table *IS* the
    > ID number; that basic fact is concealed from your view by the combo
    > box, which is displaying a DIFFERENT value (usually text) from a
    > DIFFERENT table. What you *see* is not what is *there*.
    >
    > >and at some point this
    > >might cause a problem if queries are dependent upon the data in a Lookup
    > >field (this is regardless of whether it is a Lookup Field or a Value List.)

    >
    > Not if the query is done correctly. Whether or not there is a Lookup
    > Field in the table, you can choose to include the lookup table in a
    > Query. If you need to see the looked-up text in a Report, say, simply
    > join your main table to the lookup table (or tables), and include the
    > field from there.
    >
    > >On the other hand, Subforms within a Main Form can be used to enter data
    > >from a drop down list but require an underlying Relationship between two
    > >tables.

    >
    > Well... yes, but Combo Boxes ("drop down lists" proper name) can be
    > used on a mainform, on a subform, or both. There really is no
    > connection between using a subform and using a combo box; they're just
    > two different tools.
    >
    > >However, relationships need to be created with a junction table if the
    > >tables exist in a many-to-many relationship.

    >
    > Relationships need to be created if you have a ONE to many
    > relationship, too! Creating a Lookup Field actually DOES create a
    > relationship, and then conceals it from view; one of the criticisms of
    > the feature is that it creates such a relationship, and the
    > accompanying indexes, even if a relationship already exists!
    >
    > I would recommend creating all your Tables, *WITHOUT* lookup fields;
    > creating all your Relationships, in the relationships window; and
    > using Combo Boxes liberally as needed on forms. I will confess that
    > the one advantage of having a Lookup Field in a table is that it's a
    > mite easier to put a combo box on a form - but it's dead easy even
    > without.
    >
    > >An example may help. In a patient database, the gender is asked for. How
    > >to create a drop down list in a subform with “Male†or “Female†as the
    > >choices? There are two underlying tables, “tblDemographics†and “tblGenderâ€.
    > > The two tables will obviously have a many-to-many relationship, and
    > >according to what I have read, will need a junction table to establish this
    > >relationship.

    >
    > Ummm...
    >
    > No.
    >
    > One person will not have many genders (unless you're in a very
    > particular medical specialty). You have a very simple one to many
    > relationship here: each record in tblGender will be related to many
    > recrods in tblDemographics, but each person in tblDemographics will
    > have only one gender. In fact, unless you're dealing with multiple
    > gender identities, I wouldn't bother having a tblGender at all; just
    > use a Combo Box on the form with a List of Values "Male";"Female".
    >
    > >However, in my reading it seems that Forms and Subforms relate on a ‘one’
    > >(the MainForm†to ‘many’ (the SubForm) relationship.

    >
    > That is correct. In a real many to many relationship (for instance the
    > Northwind Sample databases OrderDetails table) there is are three
    > tables involved: Orders (an Order can consist of many Products),
    > Products (each Product can be part of many Orders), and OrderDetails.
    > There is a one to many relationship from Orders to OrderDetails (used
    > on the form, using OrderID as the linking field), and another one to
    > many relationship from Products to OrderDetails.
    >
    > >If everything I have stated is true, how can a Form/Subform be constructed
    > >for the example given?
    > >The same question holds for other similar drop down lists. Another example,
    > >a drop down list for a clinical score consisting of 6 choices of clinical
    > >condition. Again obviously a many-to-many relationship.

    >
    > This is a very different issue from Gender. Each patient has only one
    > gender, but (I presume) each patient might have zero, one, two or more
    > conditions.
    >
    > There you need a THIRD TABLE. If each Patient may have multiple
    > Conditions, and each Condition may affect many Patients, then you need
    > a PatientConditions table with fields for the PatientID and the
    > ConditionID (linked to the respective tables' Primary Keys). The
    > Subform would be based - not on Conditions - but on PatientConditions;
    > you'ld have a Combo Box on the subform based on Conditions allowing a
    > different condition to be selected for each record. You might have
    > another field in PatientConditions, say a Severity value (which again
    > might use a lookup table - though not a Lookup Field - listing the
    > valid severities), perhaps a Memo or Text field for comments, date of
    > onset, etc. as needed for your application.
    >
    > >Also, please let me know if there is some easier way to perform this task
    > >that I am overlooking.
    > >Tx

    >
    > See if this description helps...
    >
    > John W. Vinson[MVP]
    >
     

Share This Page