1. ## 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. ## 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. ## 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

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. ## Re: Very complicated hierarchical task (Access2000-03)

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
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. ## Re: Very complicated hierarchical task (Access2000-03)

Thanks for the tip though!

6. ## 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.

7. ## Re: Very complicated hierarchical task (Access2000-03)

You are Godsend!
I am looking, thanks!

8. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•