Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    12

    Unanswered: Database Initial Design - Relationships

    I've forgotten a lot about access (haven't used it in a while) and have a bsic design question. I have a db for the items in my warehouse. Each item can have a client and a subclient. I'd like to have only 1 list of clients that can be used for both fields. In the ItemsTbl I'd like to store the ClientID and SubClientID, but I will obviously need to see their names on forms and reports and thus will need them in a query. How do I set up the relationship so that the ClientTbl field of ClientID can populate two places, both the ClientID and SubClientID fields on the ItemsTbl? Is this something I just do with a form or subform? Thanks so much.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I't depends on how your client / sub client is defiend

    whether a sub client can also be a client
    whether a sub client can be a sub client for more than one client
    whether you need to know upfront that client A has subclients 1,5,90
    or whether you just 'bang in' th clients and sub clients on an ad hoc basis


    you could have a table for clients
    it could have a tag / column identifying if theyr are a client, sub client or both

    if there is a one to one realtionship between clinet and sub client then you could have the subclient as part of the client. if you have more than one sub clinet or one sub client appearing in more than one client then you need an intersection table that identifies a client with one or more sub clients on this specific project
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2010
    Posts
    12
    Yes, subclients can be clients and vice versa. Essentially they need to store 2 client names with each item in the warehouse, and we'll need to query items based on both client and subclient. Clients can have more than one subclient, subclients can go with more than one client. Essentially the clienttbl is just a list of possible data to populate either field.

Posting Permissions

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