1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2000 - relating tables

Discussion in 'Business Applications' started by nwinchel, Jan 16, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. nwinchel

    nwinchel Thread Starter

    Joined:
    Mar 19, 2001
    Messages:
    59
    I’m back with yet another of my loony database questions. Again, I’m setting one up and need a connecting thought , if you will. Basically I’m working with a couple of tables for a contact management-type database. It’s more complex, however, because I need to relate the tables by using an auto number field, I believe. Here’s the scenario:

    A parent/client comes to us for service. We collect contact information such as address, phone number, etc. and the parent/client is automatically assigned a number in a specific format. (Actually, the “number” could be a combination of numbers and characters that mean something to us – more complex.) Now, this parent/client may have children that might (or might not) require service, too, and I want them to have a unique number but still need to relate them to the parent. These children may have different last names (which could change) so they can’t be related that way. I’m looking at a main form/sub-form for entry but need that magic connection so I can query statistical information based on unique numbers as well as information based on related numbers. Does this make sense? I’ll need to know, for instance, information based on an individual client but also the client’s children. I want the unique number automatically assigned but in a specific format that would distinguish a parent from a child yet relate the parent to the child (or children). Maybe something based on the Dewey Decimal system: Parent: 4.0.0 Child: 4.1.0. or some such, I don’t know.

    Any ideas? If you need more info, let me know. TIA.
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Perhaps I'm simplifying too much, but can't you just select parent and child and make them both the primary field? That way, you don't duplicate the primary field, but you can duplicate the parent.

    In your queries/forms and such, you just concatenate the two fields.

    [parent]&"-"&[child]
     
  3. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Hi. Sounds to me like a one-to-many thing, if for no other reason than that a parent can have more than one child. You're right, the Primary Key should be something like an ID number, I wouldn't necessarily try to make it meaningful, not only because it's complicated but because in an ideal world that ID number only has significance to the db, so that changing it becomes purely an administrator's task. If that makes sense.

    The child(ren) could then be added through a subform, which would be related to the main form through that ID number. In the child table, the ID number linked to the parent is not autonumber; it is the parent's number. That number can be auto-filled each time you add a new record. If you set up the linking fields correctly between subform and master, Access will do it for you. The user never even has to see that ID number; you just use it to build queries, etc.

    Now, it may be that you need parents and children in the same table; I can't tell from your post. If that's the case, you just add a field for a reference ID, and when a child is added to the table, you use a drop-down list (based on the reference ID field, not the PK) to select the parent of the child. The table then becomes "self-referential"; you add it twice to your relationships form, and make a link between the PK in MyTable and the reference ID field in MyTable_1. (Add the table twice to relationships, and I think you'll see what I mean). But that's not the most "correct" solution, for whatever that's worth. And you can always use union queries to put the two tables together. Depends mostly on whether you're trapping different data for each...

    You'll probably want some more clarification... :)
     
  4. nwinchel

    nwinchel Thread Starter

    Joined:
    Mar 19, 2001
    Messages:
    59
    Ya think?! ;)

    I don't know if I need parent and child in the same table or not. (They are currently but I thought we'd be able to more easily do some of the statistical stuff if they were in a separate table.) To complicate things even further we want to know about all the children that reside with the parent as well as others in the household (grandparents, boyfriends, etc.) Some of these may or may not be clients at any given time.

    The database was initially set up by a "consultant" who put EVERYTHING in one huge table. There isn't any type of numbering scheme except for a case number that is assigned to the parent or child if they see a counselor. However, this gets complicated if another child needs to be seen at a later date or other members of the household are added (mom gets a new boyfriend and he's seen for services, etc.) We want to keep track of each client separately in their own right as well as relate those who are part of a family group. For instance, we might not see a parent, only the child initially, but let's say we see the parent 6 months later.

    Am I making sense? Am I making it harder than it needs to be? Because the initial database was set up inappropriately I'm afraid that they'll need a new one to get the results they want.

    I also think I'm over my head. :) I'm willing to learn new things but I'm afraid this type of thing might require additional training (and time) that I don't think my employer intends to support. :( It's my contention that we might benefit from a commercial database designed for, say, a physician's office.

    Until we can get this behemoth to work it takes our best people several hours to enter information manually into an Excel spreadsheet. (And don't get me started on this!)
     
  5. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Yeah, well don't get me started on "consultants" either (I am one, and I've spent the better part of my "career" as such cleaning up the garbage that other ones created :mad::mad: )

    I don't think you need to scrap the whole thing; most normalization work (you can read about that here or in your developer's handbook--it's good stuff to understand) can be done by importing stuff into new, well-built tables using append queries that regroup and subdivide stuff in old, badly-built tables.

    It sounds to me like you're treating parents, children, boyfriends, ex-boyfriends, aunts, uncles, and family pets more or less the same. That is to say, the data captured for each is similar (tho you may have less in some cases, no problem there, as long as you don't have more), and then what you do with each of these master records is the same: they have appointments, case numbers, etc.

    So what I would do is keep all those people in the same table. But it sounds like you need to add a couple of things: one, a "family group" table, which is actually above the individuals table, and would then allow you to regroup family members via a "family" ID. And two, in addition to some sort of foreign key tying each family member back to the family table, some sort of Yes/No field that allows you to designate who is the "main" or "first" or "central" member of the family, and perhaps some sort of drop-down list type field which would allow you to designate relationship to that person. If you get my drift...
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/65145

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice