Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002

    Question Unanswered: Linking 3 tables to one on the same field

    I'm creating a Database that stores information about water supplies.

    There are three key tables; Distribution Zone, Treatment Plant and Source.

    Each of these is different, but they all link to a single table containing info on tests carried out on them.

    So far what I've done, in the Tests table there is a field each for Zone, Plant and Source ID number- with a validation Rule that allows a value in only one of the fields. eg.
    - TestID (PK, autonumber)
    - ZoneID (FK)
    - PlantID (FK)
    - SourceID (FK)
    - {test specific data}

    However, I'm having problems using this, especially creating a form that works well to enter and display the tests.

    Does anyone know of any better way of doing this. I want to keep a single Tests table that can be for any of the Zone,Plant or Source Tables.

    (On another topic, this data is linked to a GIS in a Personal GeoDatabase. Is there anyone out there who has experience with these?)

  2. #2
    Join Date
    Jul 2003
    London UK
    The answer depends upon the way your tests operate. Are they separate tests on each of the three options or single tests on all three at once?

    If each test is run separately on each of Distribution Zone, Treatment Plant and Source, I'd look at your database design. It may be easier to have a single field in TESTS called eg EXTERNAL ID that could be any of ZoneID PlantID or SourceID, with a second filterable field of TYPE that identifies which one it is. So then you'd be able to go into the form, filter on type, then filter on ID to display your options. The source data for the table could then be TESTS plus lookups.

    If a single test is run against all three at once then you'll need to have your relationships set up correctly so that you can run a single query based upon all four tables. You'll then be able to pick and choose what you want from a form based on the results of this query. If you do this then you might try using option buttons to drive the selections in your search combobox to prevent your filters getting muddled with each other.
    If you want a different display for each of your three types of search, try playing with the visible property to achieve the look you're after - or build three separate forms with the same subform based on TESTS.

    Hope this helps & I haven't misunderstood - it's 3 am here....

Posting Permissions

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