Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Entries for same field type (2000/2002)

    I would like suggestions on the best method for handling this scenario - I am creating a database using 2002 that needs to be compatible with 2000, and I have several fields in the primary record which can potentially have multiple entries per record. If multiple entries occur I would like for them to be visible on the primary table in a tree branch type effect as far as the table view is concerned.
    Attempt at sample below:
    Record # | PO # ...| Job # ...| Material | Spec ...| .Location |
    -------------|-----------|-----------|------------|-----------|--------------|
    .................|...............|....654....| ...............|...............| Shed ......|
    .................|...............|-----------|................|.DMSxx..|-------------|
    ...... 1 ...... |.. 1234. |.....987...|..Alum.....|-----------|.....12........|
    .................|...............|-----------|................| AMSxxx |-------------|
    .................|...............|....321....| ...............|...............| .....A2......|
    ----------------------------------------------------------------------------
    In the above example Record #, PO #, and Material will only ever have one entry per record, but the Job #, Spec, and Location could have numerous entries per record. (Ignore the periods, blank spaces wouldn't work for achieving the alignment).
    Anyone who has suggestions for this please advise.
    Thanks,
    Danny

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Entries for same field type (2000/2002)

    It doesn't really make sense in a relational database to speak about fields having multiple entries per record. Create three additional tables, one each for Job#, Spec, and Location, and relate them many->one to the "primary" table, using the Record # to relate them (I assume your Record # is unique in your "primary" table).

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Entries for same field type (2000/2002)

    ok - but when I am entering the record on the form - how do I do the multiple entries, and will what you described give me a table view similar to what I illustrated?
    Danny

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Entries for same field type (2000/2002)

    What the tables look like and what the forms look like are two different things. You design the tables to correctly relate the data. You design the forms to present the data and allow data entry/edits in a format that is acceptable to the user. Generally what you do is base the parent form on the "one" side of the one-to-many relationship, in this case Record#. Then you could have a subform for POs. The PO record would have the Record# primary key as a foreign key, and you would link the form and subform on that field. You could either have another subform inside the PO subform listing all the Job#s for that PO or you could have side by side subforms related *through* the parent form.

    There are sample databases you can download from the Microsoft site that will give you all kinds of ideas on how to build forms and subforms to display your data.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •