Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Very complicated hierarchical task (Access2000-03)

    Hi!
    I need serious help! (what else is new right?)

    Here is what happening.
    I was about to pull records with Max Date and have it easy except when we determined that part of people do not have those dates so recors can not be filtered. Well, some can, some can't.

    For those Dateless people we are coming up with solution of having a table made where we are going to pick Max value.
    Let say person attended 2 colleges.
    We want to pull the most current one. However dates of the graduation is missing.
    So we go to Degree received table and pulling highest degree.
    For that I am making a tableDEGREE where let say
    Associate degree will be #1
    Bachelor #2
    Master #3

    So now the 'simple' stuff.
    How do I proceed with this task?
    How do I write and where:
    If Date is Null then look in Degree column and compare in a tableDEGREE and pick the one with the highest numeric value.

    It must be fun if I just knew where to start!

    Thanks for any info you can give me.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    This can probably be done, but you'll have to provide precise and detailed information about the tables involved: what are the exact names of the tables, and what are the names of the relevant fields?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    Thanks I was hoping it can be done.

    I will create a table DEGREE_LEVEL with fields DEGREE and LEVEL
    DEGREE_________________LEVEL
    A________________________3
    B________________________2
    M________________________1
    _______________________________________

    Another table tableMain has

    DEGREE____YEARGRAD_____STUDENTID_______SCHOOL
    A __________2006____________ 1010_____________NYU
    B___________________________1010___________UPENN
    M___________________________1010___________UCLA

    so I will want M record because of a Master degree has value of 3
    even it has Max date! (I need to make sure it is correct - to me it makes logical sense. If I am fishing for most recent college could that be the one where you getting lower degree?)


    B ________________________1011_______________MIU
    this I will want because it is the only one for this ID


    M________2005_____________1012 _______________ARB
    M________ 2007_____________1012____________PennState

    the second one because of the Max Date
    __________________________________________________ __________

    It is so pretty when I tye it but when I post it - it all screwed up! Is it a problem?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    I'll try to answer your main question later.

    About the formatting: HTML ignores multiple spaces. You can insert a <!t>[pre] tag above, and a <!t>[/pre] tag below text that should preserve the spacing as you entered it, for example
    <pre>DEGREE YEAR_GRAD STUDENT_ID SCHOOL
    A 2006 1010 NYU
    B 1010 UPENN
    M 1010 UCLA
    </pre>

    But, as you will find, this uses a fixed width font, so you'll have to fiddle with the spaces to get it right.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    I had fiddled allright? LOL
    Thanks for the tip though!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    See the attached demo database. I created a series of queries to achieve your goal. The names indicate in which order they were created: 1_..., 2_... etc.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    You are Godsend!
    I am looking, thanks!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    No, I'm Hans <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    LOL
    Well...I must be tired or something...
    I had done all of your quesries on actual database and now I am sitting staring at the result and thinking...now what?
    I need to write Report.
    What did we accomplished so far?
    StudentID_____School______Year_______Level_____Deg ree

    1010______PennState ______2007________3_______Master
    1010_______UCLA_____________________3_______Master
    1011________MIU______________________2_______Bache lor

    What am I going to do with this quesry in terms of Report writing? Sorry I am making no sense right?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    You can base a report on a query, exactly the same way you can base a report on a table.

    If you want to design the report yourself, activate the Reports section of the database window.
    Double click on "New report in design view".
    You'll see an empty report.
    Activate the Data tab of the Properties window.
    Select your query as Record Source.
    Make sure that the Field List is visible.
    You can now place fields on the report.

    If you'd rather use a Wizard, activate the Reports section of the database window.
    Click New.
    Select your query from the dropdown list at the bottom.
    Select Report Wizard or one of the AutoReports, then click OK.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    Oh, believe me I know THAT much! LOLOL
    I can't see what am I suppose to do withthe record like this
    StudentID_____School______Year_______Level_____Deg ree

    1010______PennState ______2007________3_______Master
    1010_______UCLA_____________________3_______Master

    Which school is to display? Only one most recent school belong on Report.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    I am making tables out of the quesry.
    One with NULLS for a date
    One with Not Nulls for a Date
    One with NULLS for a date and NULLS for a Level
    One with Not NULLS for a date and Not NULLS for Level

    I will then insert syb reports into main and will be done with this.
    Is it my fault that data entry people can't type in everything they should had? LOL

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    The query I designed would return only the first of these records - if there is a year it takes that record, and ignores the other one.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very complicated hierarchical task (Access2000-03)

    Hi!
    I am trying to make it work.
    Thanks so much for your help.
    I am having same IDs with same levels and different Schools.
    Waiting for management to tell me which school to pick.
    There is NO other criteria whatsoever.
    Thanks again, Hans!

Posting Permissions

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