Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Posts
    25

    Unanswered: Need Results from Query.

    Hi All,

    This is my first post.

    I'm trying to create a query in Access 2000, that will display the header and detail records only that don't have a transaction type of 14, without displaying the other transaction types. I just need to know if the transactype of 14 was created, if not I want to see the results.

    This is a one to many relationship.

    TABLE1 - Header
    f_number int primary index
    f_offset int primary index
    f_trans_type int value(0,3,9,11,13,14)


    TABLE2 - Detail Info
    f_number int primary index
    f_offset int primary index
    f_time
    f_patient

    I tried the following first join.

    SELECT TABLE2.f_number,TABLE2.f_offset,TABLE2.f_time,TABL E2.f_patient ,TABLE1.f_trans_type
    FROM TABLE1, TABLE2
    WHERE (TABLE2.f_number = TABLE1.f_number) AND (TABLE2.f_offset = TABLE1.f_offset)
    ORDER BY TABLE2.f_number,TABLE2.f_offset,TABLE1.f_trans_typ e

    This query will result with my detail info, and transaction type with each detail record. But still doesn't tell me which header record is missing a transaction type of 14. Could I maybe query the results of this query then link the results back to TABLE1, with a key on f_offset, f_number, and trans_type?

    Getting confused


    Thanks.

    Matt
    Last edited by mlong30; 10-30-07 at 23:49.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by mlong30
    This query will result with my detail info, and transaction type with each detail record. But still doesn't tell me which header record is missing a transaction type of 14. Could I maybe query the results of this query then link the results back to TABLE1, with a key on f_offset, f_number, and trans_type?
    Tell me if this gets you closer:

    Code:
     SELECT TABLE2.f_number,TABLE2.f_offset,TABLE2.f_time,TABLE2.f_patient ,TABLE1.f_trans_type
      FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.f_number = TABLE2.f_number AND TABLE1.f_offset = TABLE2.f_offset
    WHERE TABLE1.f_trans_type <> 14
      ORDER BY TABLE2.f_number,TABLE2.f_offset,TABLE1.f_trans_type
    I'm not sure if it's right because your schema doesn't make sense. number and offset are the primary key for each header *and* each detail? In that case, why split it into two tables at all? If you have multiple details per header (which you can't with the current layout) you'd want an extra column in the detail primary key.

    Some notes on readability: Why name the tables "TABLE1" and "TABLE2" instead of Headers and Details? And don't prefix every field with f_. It's just a lot of annoying typing for no added clarity. Personally, I just make field names lowercase and table names start with a capital letter. All caps denotes SQL keywords.

  3. #3
    Join Date
    Oct 2007
    Posts
    25
    [QUOTE=sco08y]Tell me if this gets you closer:

    << Tell me if this gets you closer>>

    No, it doesn't.


    << I'm not sure if it's right because your schema doesn't make sense.>>

    You are absolutely correct! This is an off the shelf database my company purchased, so I can't make any changes to it since we don’t' support it.

    Matt

Posting Permissions

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