Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    28

    Unanswered: table design problem

    Hello



    Could someone tell me what I wrong with my table design ( I have attached a snapshot of the relationship window - not all tables are shown, just ones that are in problematic query)


    my problem:


    When i add table main, habitat info, stream stream site, and reach data to a query it is blank

    (when i add table main, habitat info, stream stream site or table main, habitat info, reach data - no problem)


    I want to be able to select certain "gazetted name" from my main table and be able to view related records from both the stream site table and the reach table.

    I have been looking at this for way to long and need fresh eyes to approach the problem with

    Thanks for your help
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    Have you tried making two seperate queries and joining the two in a third?

  3. #3
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37
    Too many one to manys. Can't do that at multiple levels.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sue, you need a union

    select stuff
    from main, habitat, streamsite
    where join conditions
    union all
    select stuff
    from main, habitat, reach
    where join conditions

    the ability to combine results from the two queries in a union will depend on having datatype-compatible columns in the select lists

    rudy
    http://rudy.ca/

  5. #5
    Join Date
    May 2002
    Posts
    28
    Originally posted by Balr14
    Too many one to manys. Can't do that at multiple levels.

    Can you explain your response - "too many one to many" - how is this problematic?

    Thanks

  6. #6
    Join Date
    May 2002
    Posts
    28
    Originally posted by r937
    sue, you need a union

    select stuff
    from main, habitat, streamsite
    where join conditions
    union all
    select stuff
    from main, habitat, reach
    where join conditions

    the ability to combine results from the two queries in a union will depend on having datatype-compatible columns in the select lists

    rudy
    http://rudy.ca/

    Thanks to everyone you took the time to answer my question - I got i working

  7. #7
    Join Date
    May 2002
    Posts
    28

    Rudy can you take a look at my relationships when you get the chance?

    In this screen shot I have included more tables that relate to the tables in the first screen shot - By taking a quick look can you tell me if are problematic?


    I dont really know how else to set the relationship between the tables other than the way they are set up.


    Stream site data table contains data collected at a specific point on a stream - at this point riparian data and physical data are recorded

    The Reach (a "reach" is defined as a length stream that has homogeneous, or regularly repeating habitat characteristics) data table contains data about a particular segment of a stream - from this segment riparian data and physical data is recorded.

    Any suggestions?
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i've looked at the extended diagram, but it's kinda hard to figure out, so i'll guess

    main to habitat is one-to-many
    habitat to stream is one-to-many
    habitat to reach is one-to-many

    physical wtr to stream is one-to-many?
    physical wtr to reach is one-to-many?
    stream to riparian is one-to-many?
    reach to riparian is one-to-many?

    if that's accurate, then i don't see any problem

    queries will be tricky, though, depending on how many of these tables have to participate in producing a desired report

    rudy

  9. #9
    Join Date
    May 2002
    Posts
    28

    Thanks

    Thanks Rudy,

    I totally appreciate it

    I've come a long way from knowing absolutely nothing about Access in the last few months thanks to people like you responsing to my questions - still need reasuring now and again though

  10. #10
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37
    Originally posted by sueviolet
    Can you explain your response - "too many one to many" - how is this problematic?

    Thanks
    What Rudy said. Coding a single query with this structure could result in virtually infinite results. You need a union of 2 queries. Sorry, I didn't know you were pretty new or I would have explained it better. It's my first visit and I don't know the general experience level of people that come 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
  •