• Table/Form design (2000SR1)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Table/Form design (2000SR1)

    Author
    Topic
    WSPeterN
    AskWoody Lounger
    February 10, 2003 at 9:52 pm #383148

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

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WBell
      AskWoody_MVP
      February 11, 2003 at 1:04 pm #652557

      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.

      Reply | Quote
      • WSPeterN
        AskWoody Lounger
        February 12, 2003 at 1:14 am #652787

        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

        Reply | Quote
        • WBell
          AskWoody_MVP
          March 7, 2003 at 6:01 pm #659554

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

          Reply | Quote
          • WSPeterN
            AskWoody Lounger
            March 7, 2003 at 8:09 pm #659638

            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

            Reply | Quote
    Viewing 0 reply threads
    Reply To: Table/Form design (2000SR1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.