Results 1 to 11 of 11

Thread: Query Question

  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: Query Question

    Ok here's my question, say the following table existed in an Access Database:
    Code:
    Name       PT1         PT2         PT3         PT4         PT5         PT6
    Derek      1/2/10     2/28/10    3/15/10   4/1/10     5/10/10
    Kayla      1/17/10    3/29/10    4/5/10
    Audrey    3/29/11   4/2/11      5/22/11   6/8/11     6/20/11  7/5/11
    Ok, now my question is can you create a query that searches for the farthest completed field, and only displays that data. For example, I want to see each of my testers on the query, but only display the data from the farthest stage of PT they have completed. So if this query works the way I'd like, when I run it for the data above, I would see something similar to this (But not necessarily in this layout):
    Code:
    Name      PT1         PT2         PT3         PT4         PT5         PT6
    Derek                                                    5/10/10
    Kayla                             4/5/10
    Audrey                                                              7/5/11
    Is this possible, or is there a better way to do this?

    Thanks!
    Last edited by dlester; 07-18-11 at 00:26.

  2. #2
    Join Date
    Jul 2011
    Posts
    14
    Well it's possible to get that output with something like this:

    Code:
    select 
    	FirstName,
    	iif(isnull(PT2), PT1, null) as PT1,
    	iif(isnull(PT3), PT2, null) as PT2,
    	iif(isnull(PT4), PT3, null) as PT3,
    	iif(isnull(PT5), PT4, null) as PT4,
    	iif(isnull(PT6), PT5, null) as PT5,
    	PT6
    from Table1;
    You're basically saying for each of the PTx fields, only display it if the next PTx field is null. So as long as (example) PT1, PT2, and PT3 all have data and PT4, PT5, and PT6 don't it will work.

    But for most uses you'd want to store this data differently. For example, you could have one table to store all of the people:

    People
    ID AutoNumber
    FirstName Text

    With data like
    Code:
    ID             FirstName
    1               Derek
    2               Kayla
    3               Audrey
    And another table to store their PT fields:

    PTs
    ID AutoNumber
    PeopleID Number
    PT Date/Time

    With data like
    Code:
    ID           PeopleID          PT
    1             1                    1/2/10
    2             1                    2/28/10
    3             1                    3/15/10
    4             1                    4/1/10
    5             1                    5/10/10
    6             2                    1/17/10
    7             2                    3/29/10
    And so on.

    This would allow any number of PTs, and would be easier to insert data and query.

    So you could get data similar to what you need using a query like:
    Code:
    select 
      People.FirstName,
      (select max(PTs.PT) from PTs where PTs.PeopleID = People.ID) as [Most Recent]
    from People;
    The result of this query would be more like:
    Code:
    FirstName        Most Recent
    Derek              5/10/10
    Kayla               4/5/10
    Audrey             7/5/11

    If you just need to get the query working period, something like the first query should be the quickest way to do so. The second way keeps your data normalized, which is the industry standard, and generally the better way to go.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's an easy way. The advise about properly normalizing your data is still important, though.
    Code:
    SELECT a.Name, Max(a.PT1) AS MaxDate
    FROM (SELECT Name, PT1 FROM Sample
    UNION
    SELECT Name, PT2 FROM Sample
    UNION
    SELECT Name, PT3 FROM Sample
    UNION
    SELECT Name, PT4 FROM Sample
    UNION
    SELECT Name, PT5 FROM Sample
    UNION
    SELECT Name, PT6 FROM Sample
    ). AS a
    GROUP BY a.Name;
    Have a nice day!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    given you current design (which is frankly dubious) I'd go with a IIF similar to BrianSteffens's statement but approaching from the other end

    Code:
    SELECT
    iif(not isnull(PT6),PT6,
      iif(not isnull(PT5),PT5,
        iif(not isnull(PT4),PT4,
          iif(not isnull(PT3),PT3,
            iif(not isnull(PT2),PT2,
              iif(not isnull(PT1),PT1,"")
            )
          )
        )
      )
    ) as MyDate
    FROM Table2;
    however its a kludgey solution to a bad physical design
    also you are using a reserved word 'name' for one of your columns. although Access / JET will probably handle it gracefully it coudl cause problems over time
    ms access reserved words - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2011
    Posts
    13
    Well that was an example header, that's not the full table.

    I tried to put different pieces in different tables but the relationships were not playing well together, so to get it up and 'functioning' I had to build one large table with the fields knowing that later I would break it out into smaller tables.

    The problem I've had is linking the data using relationships. I really could use some help with that part if anyone is willing to advise me.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what problem(s) have you had on using realtionships?

    creating 'em is easy enough
    open the realtionships view / screen / pane
    click on the column thats the parent column and drag to the 'other' column
    define the type of relationship and jobsagoodun
    ms access types of relationship - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2011
    Posts
    13
    I've been able to create them, but it constantly makes them Intermediate relationships, when they could be 1 to 1 or 1 to many.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    intermediate realtionships, sorry don't know what you mean
    realtionships fall into several categories
    a 1:1 relationship, realtivley rare, often used for security or performance reasons
    a 1:many relationship where there is one 'parent' and many on the other side. examples of this include, say modelling a product 'gozinto' structure a product is comprised of other products, assemblies or parts
    a many to many relationship which is tricky to implement in a realtions db but you can fake it by using an intersection table where you have two 1:many realtionships

    table A has a 1:many realtionship with Table Z
    table B like wise
    effectively Table A has a many to many realtionship with Table B via Table Z

    the 'other' siode of the realtionship also has meaning
    a 1:1 realtionship may actually be a 1:0,1 meaning that there may or may not be a related row in the other table a 1:1 in its purest most acadxemic meaning there is a single matching row in both tables
    most 1:many relationships are in reality 1:0,many meaning there may or may not be matching rows in the other table.

    if you are confused then bone up on the relationships used in Access and understand relational theory as applied to db's
    The Relational Data Model, Normalisation and effective Database Design
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jul 2011
    Posts
    13
    Thats what I mean, I only knew of the three types, and every time I tried to make the relationship, Access said the type was Intermediate. I'm doing some testing now to see if maybe I have my problem solved. I'll let you know what happens. Thanks for the help!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what happens if you pull up the realtionship view and right click on a realtionship that shoudl allow you to defien the type of relationship. I nearly always use the 'enforce referential integrity' AND 'cascade update' and selectively (read rarely) use the 'cascade delete'

    I still don't understand the term 'intermediate realtionship', never come across it so I don't understand where you are getting it from.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jul 2011
    Posts
    13
    Ok, so apparently i had something setup very very wrong on my first attempt here, but I have rebuilt the table(s), and things are starting to work as they should. Thanks to everyone for all the help!

Posting Permissions

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