Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table/Form design (2000SR1)

    I am setting up an input form for a new segment of an existing database and can

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Table/Form design (2000SR1)

    Hi Peter,
    After chewing on this for a while, I would create a separate table where you listed the Category and the Specialties for each person. It would look something like
    intClientFK Number The pointer to a Clients table record
    intCategFK Number The pointer to a Category table record
    intSpeclFK Number The pointer to a Specialties table record
    with a primary key based on all three fields so you prevent duplicate entries. Each of the fields would be a long integer. This structure would allow you to create a simple subform that the data could be entered with, and the existing list of Clients would be available to start the process. The creation of the paper directory may be a bit more of a challenge, but you would only pick people who had one or more entries in the table. You might however need to resort to creating a temporary table to combine information, depending on the format of the report.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table/Form design (2000SR1)

    For the part of the database you refer to I actually have a table and a sub-table With category the main table and specialties the detail table rather like the typical Orders/Order Details a la Northwinds.

    I wasn't completely clear on my dilemma, though. This has to do with unique info at theClient level that will only be recorded for the directory. For example, Organizations give Founding dates, contact names, charitable status, etc and Individuals can record degrees and diplomas and Both types have the opportunity for Additional Information that will be recorded in a memo field - up to 40 words for free and up to an additional 100 words for a fee. All of the above are only recorded in the directory and are not part of the regular record keeping of the Arts Council.

    With that additional information, then, should I just dump this stuff into the main clients table or is there a reasonable case for a sub table that has a one-to-one relationship.

    In addition, if I use the main table, what is the best way to filter these directory listings? Just use a check box called "directory"? Certainly both safe and explicit as opposed to doing a query on one of the other fields that uses "not null" as a parameter.

    If you think I should go the one-to-one route, how do I get it to hook in easily to the main table, which I have always found a bit of a hassle when adding this type of table to existing records.

    Peter

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Table/Form design (2000SR1)

    Did you get this issue resolved? Somehow it slipped under my "unanswered questions" radar.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table/Form design (2000SR1)

    I decided to put the unique information in the client Main table. It was simpler than dealing with the one-to-one situation. The database isn't so large that all the null fields are a problem. For data entry, I made a form that has the relevant client information, a subform that selects categories and a sub-subform where the details can be recorded. It seems to be working fine, so i am letting sleeping dogs lie as this is a volunteer activity!

    Thanks for the inquiry.
    Peter

Posting Permissions

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