Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: Need query result = all fields across 9 tables from ONE record ONLY!! . . .

    . . . . I KNOW, I KNOW what you are probably saying. Why use MSAccess for this??!?!?

    Boss = amazing & smart woman, not a datahead.

    Me = emerging datahead guy, new to MSAccess 2007.

    $ = She who pays the piper calls the tune, right?!?!

    Good news == I am killing the content and appearance of 9 forms and associated (9) tables.

    PROBLEM == building query to search across 9 tables for all 125-150 fields in dB for only 1 record in query I'm building.

    P-ID and P-Name are standard across all tables. P-ID is primary key in 'main'table (for lack of better word.) Should I have same field be primary key for all 9 tables? Should I create a relationship between all 9 tables to the other 8? Or should I join properties in the query in some fashion?

    Confused and I know that the solution will probably seem obvious once I implement, but right now I am banging my head against the wall and I'm getting a headache!

    When I say "602" under Criteria line of field that is primary key in query (P-ID# of test record I want in report) I get a Data mismatch error (error #3464). 3 fields are date fields, could that be reason why?

    Hunch is its all about how I have assembled the data and I am open to suggestions on changes.

    Any help would be greatly appreciated!
    Last edited by HealtheMind402; 05-24-12 at 01:11.

  2. #2
    Join Date
    Dec 2010
    Location
    Manchester
    Posts
    24
    Making P-ID a Primary key across all other tables would be a bad idea, but keep the one you have at the moment, and establish a relationship with other tables using P-ID. things may be easier doing this in a query. try doing it this way first.
    Last edited by Batou; 05-24-12 at 09:32.

  3. #3
    Join Date
    May 2012
    Posts
    3

    thanks, follow up Q

    Thanks for the advice. I am getting the query to run now but if someone can explain to me how I can best use inner and outer joins to do this query, that would be great.

    (I also think a dedicated table for P-ID and P-Name to populate the other 8 tables is unavoidable and a + at this point.)

    And avoid Cartesian results too.

    Pls. keep in mind I am new to this.

    Thanks!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    short of knowing your design in full its hard to say
    however having
    P-ID and P-Name are standard across all tables
    suggests a flawed design and or flawed understanding of normalisation
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2012
    Posts
    3
    Ok so should I have a seperate table that is the home of P-ID and P-Name? The reason why I put these fields into the other tables is because the end users might get confused if they do not see what p-id or p name they are working on at the moment.

Posting Permissions

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