Results 1 to 5 of 5
  1. #1
    Cyber Syster
    Guest

    Data verification-1 field dependent upon another

    I'm trying to verify that data is correct in an data entry form. One field is called widgets and another called locations. A widget may be in more than one location and each location may hold more than one type of widget. I can verify that the data in widgets and locations are both valid but not that the combination of widget and location is vaild. How can I do this? I'm using Access 97.

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

    Re: Data verification-1 field dependent upon another

    Start with telling us how *you* determine whether the combination is valid, never mind how the program does. You're describing a many-to-many join, so I assume your form is based on the join table, but what rules are there that say which widgets can occur in which locations?

    If you just want to make sure that you don't enter the same thing twice, just create a multi-field unique key on your table using the widget key field and the location key field. That way, you won't be able to enter the same widget-location combination twice.
    Charlotte

  3. #3
    Cyber Syster
    Guest

    Re: Data verification-1 field dependent upon another

    Thanks, Charlotte, but I suppose I was't clear. My main table consists of the widgets and their locations like this:

    widgeta loc1
    widgeta loc2
    widgetx loc1
    widgetx loc2
    widgetx loc2

    What I need is a way for a form to update a different table with other information (quantity) on a particular widget (ex. widgetx) and location combination (widgetx is in locx). The form requires entry of both the widget, the location, and some other information and I can't put it in the table with the widget/location information.

    I just need a way to verify that a widget may be in a location and then I update a different table.

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

    Re: Data verification-1 field dependent upon another

    Then your design is faulty and will bite you sooner or later. Your 'main' table is actually a join table relating a particular widget to a particular location. It would be better designed if you also had a table of widgets (unique) and a table of locations (unique) and used the primary keys from those in your join table. Then you could enter additional information (like a description) about a particular widget into the widgets table and about a particular location into the locations table. In fact, there's no reason not to add a field to the join table to hold information about that particular combination of widget and location, and things like quantity on hand at that location is a good candidate for inclusion in the join table.

    The essential rule of thumb in relational databases is to enter actual data only once. There should be a primary key (i.e., WidgetID) that uniquely identifies that data. Then you use the primary key in other tables to point to the information in the table for that thing (i.e., the Widgets table). That keeps you from having to wander through multiple tables changing something like the spelling of the widget name--you change it once in the only table where it lives and you simply display it elsewhere in the user interface when appropriate.
    Charlotte

  5. #5
    Cyber Syster
    Guest

    Re: Data verification-1 field dependent upon another

    Actually there is a lot more information about the widgets and locations that make the records unique. I don't need info about descriptions, etc. My main table has another field, let's call it Orders, and each Order is for one or more widgets which may be in one or more locations. One widget will be on an order only once but I still need to know if the location chosen for the widget is valid for the widget.

    I know that the order is correct, the widget is a valid one, and that the location is correct. I just need to look at the table of widget and locations and see if the location entered is valid for the widget entered.

    I'm just not getting anywhere!

Posting Permissions

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