How do I set up outline numbering in an MS Access Report

jsimonuchc
jsimonuchc used Ask the Experts™
on
Hi,

I am trying to figure out a way to set up outline numbering in an ms access report. I have 4 tables, which are all related. Each table is a subset of the previous table. For example, if my tables were County, State, City, Street I would want to run a report that looked something like this:

1. USA
    1.1 Alabama
          1.1.1 Montomery
    1.2 Alaska
          1.2.1 Anchorage
    1.3 Arizona
          1.3.1 Phoenix
                   1.3.1.1 Main St.
                   1.3.1.2 Center st

Its the numbering that I am having a hard time with. Any suggestions? Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Dear jsimonuchc,

generally you have two possibilities to solve this:

1st: Give one table with all information completely to the report and use the grouping function.
2nd: Create sub reports and link the tables by it's keys.

I suggest you to use the first possibility. When you have a query with all your information in you can use the report wizard. This will give you a good overview and let you easily apply changes.

Hope this helps...
Andreas

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
This cannot be easily done directly with an Access' built in report options/settings/Properties.

Q-riders suggestion will work, but as you can see, you will probably have to add the "Outline Format" to the data.

Also you have not posted your source data, so it is difficult to give specifics.

For example:
Will this only be for the USA?

Will USA *Always* be "1"
Or will "1" always be the Top level Country?

What about levels below State? (City, Town, Hamlet, Zip Code, ...etc)
(Small countries like Monaco do not have sub divisions.)

In any event, I don't know if this can be done without a fair amount of code though...

JeffCoachman

Commented:
Assuming your four tables each has an auto-number ID field, build a query to drive your report, using the four tables linked by their respective ID fields.  Use the grouping function to group on Country, State, City and Street.

Each table should also have a field called SortOrder, which is updated every time the report is run, to take account of additons and deletions.  Use a recordset with with Country sorted alphabetically, and loop through the table incrementing the SortOrder field by 1 each time.

The index number for each record can then be constructed as an expression such as:
tblCountry.[SortOrder] & "." & tblState.[SortOrder] & "." & tblCity.[SortOrder] & "." & tblStreet.[SortOrder}
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Actually, you can use a Grouped Report.
Create textboxes in each group, with the running sum property set, to get the Group Number.
Then successively concatenate each Group textbox value to the next lower Group.

Here is a sample:

JeffCoachman
Access-EEQ24785569-GroupedWordOu.mdb
untitled.JPG
Hamed NasrRetired IT Professional

Commented:
boag2000
;-)

Commented:
Nicely done Jeff.  One small problem, though.  What happens when the group index goes over 10.  I think beforehand, you have to decide on the number o characters per group so if two is the number you can go from 01. to 99.99.99.99.

Commented:
BTW, being an Albertan, exiled in Quebec, I'm sure you meant Alberta, not Alberts ;-)  Or maybe your using dummy data?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<Or maybe your using dummy data?>
...Or maybe a dummy Expert.

Sorry for the "Speeling" error

Yes, the State/Provinces were meant to be real, the City/Town may be dummy data.

Sadly, I thought that by using a relatively "Easy" to spell Province, like "Alberta" (unlike: Saskatchewan or Nunavut) I would be OK.
:-(

Guess I screwed that theory up.

In any event, It looks like the post by Bellone would basically do the same, but in a different way.


Jeff
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
GRayL,

(Always keeping me honest...)
;-)

From the Access help Files ("Specifications") You can only have 10 levels.
So I don't even know if you can go over 10.

This is why I asked the questions I did in my first post.

I just wanted to put this out there to see if this is generally what they wanted.

;-)

JeffCoachman

Commented:
I'm not talking levels, I'm talking how big the number grows to in each level.  With 50 states in the USA, you would need to have two digits in the second level and that means strings with leading zeros  If we cataloged all 195 countries, you would need 3 digits for level 1, else the sorting is going to get screwed up.  Get my drift?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Sorry, I am still not understanding.

Can you post some sample date that will have "screwed up" sorting?

In other words, presuming that the underlying recordset is sorted By Country, StateProvince, Town, Address, under what circumstances would the sorting get screwed up?

Jeff

Commented:
Not with my solution, I believe.  However, for continuity, you could use:

format(tblCountry.[SortOrder],"000") & "." & format(tblState.[SortOrder],"000") & "." & format(tblCity.[SortOrder],"000") & "." & format(tblStreet.[SortOrder}],"000")

Might look a bit clunky tho'

Commented:
1.0
1.1
1.11
1.12
1.13
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
2.0

instead of

1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
1.12
1.13
2.0
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
No, I mean actual sample data (With Countries , State, towns, and addresses)

When I use some sample data, I get this:

The numbers seem to be in order.

What am I missing?

Jeff
untitled.JPG

Commented:
That is because right now with just a single period between level 1 and level 2, Access sees it as a number.  Add a third level with 2 decimals, and duck as the #$%^ hits the fan ;-)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
In other words the group sets the Sort order.

(If you are Grouped By Country, then the countries will be sorted  in Ascending order by default)


My reasoning is that the "Outline" they were looking for here is based on the same structure in Word.
In Word, there are no leading Zeros in this Outline scheme.

Forgive me if I am not understanding you.
:-(

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
The decimal place is just a Character.

So this is not really a number. it is just a sequence of numbers concatenated together with a "."

Jeff

Commented:
But what about the Grouping Country, City, Street?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Again, some sample data would go a long way in clearing things up for me...

I'm not being argumentative, perhaps I am just missing something simple.

Jeff

Commented:
jsimonuchc:  Any way you can pare the mdb down to the min required to support the question (4 tables, each with more than10 records, and your form as you now have it, and upload it here using Attach File below?

Commented:
Sorry, that should be 'report', not 'form'.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
The system only counts: 1,2,3,4,5,6,7,8,9,10,11

Why would it decide to count: 1,11,12,13,14,15,16,17,18,19,2,20,21...
?

I mull this over some more tonight...

Commented:
Countries
=======
CountryID - autonumber
CountryName - text

Regions
======
RegionID - autonumber
CountryID - fk - from Countries
RegionName - text

Cities
====
CityID - autonumber
RegionID - fk from Regions
RegionName - text

Streets
=====
StreetID - autonumber
CityID - fk from Cities
StreetName - text

At a minimum it has to look like this.  We can have a Moscow in Idaho a region and one in Russia a country, distinguishable by CityID.

More tomorrow ;-)
Hamed NasrRetired IT Professional

Commented:
Where is jsimonuchc of all this discussion?
I think a lock should be set on the question to close after 2 comments then reopens when the asker responds.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
From the sound of the Question, I am presuming that this is a Data Dump from one big table.

Here is a new sample, it appears to be numbered correctly all the way down to the address level.

Jeff
Access-EEQ24785569-GroupedWordOu.zip
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
hnasr,

My guess is that they were standing back until I worked out the details with Ray.

But now we are at the point were we need to know the source data...

jsimonuchc...?

Author

Commented:
Hey all, thanks so much for the input. I am waiting on authorization to upload a copy of the database so you can look at it.

Author

Commented:
Ok, I am attaching the database I plan to be using.
NBME-Classification.mdb
MIS Liason
Most Valuable Expert 2012
Commented:
Try this.

Examine it thoroughly to see if this is generally what you wanted.
(Sorting, Grouping, Formatting, ...etc)


Access-EEQ24785569-GroupedWordOu.mdb

Author

Commented:
You sir, are a genius. That's perfect.

Author

Commented:
Thanks!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<You sir, are a genius.>
As an Expert here, I am probably about average, but thanks...
;-)

Commented:
Sorry Jeff about my misunderstanding of how sorts are handled in Report Groups.  Excellent job.  However, (the shoe drops)  in order to be able to precisely relate the report to the data in the tables, would it not have been better to replace the =1 in the text boxes with the appropriate ID from the four class tables?  Knowing how autonumbers behave when a record is deleted, the 'holes' would show up.  That may or may not be important.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Ray,

First, as I said before, I have no issue with defending my posts.
As you know no one is right all of the time.

(Except maybe Lpurvis)
;-)

So I would not be much of an expert if I could not/or would not try to answer any questions about my posts.

Especially if the question comes from an Expert like you, whom I respect greatly.


Ok,

Enough "A.K."...
;-)


< in order to be able to precisely relate the report to the data in the tables, would it not have been better to replace the =1 in the text boxes with the appropriate ID from the four class tables?>
Yes, it would seem so.
But the structure of the tables was unknown at the time I posted my sample.

My Concern then would be that they may have a structure like this in the future:
Class12-->NormalClass-->Class9-->ClassC-->
Oy!

But yes, your suggestion would work quite well in this situation.

;-)

Jeff

Commented:
Jeff:  Having been unable to see the forest for the trees on this one, after playing around with your solution after my last stupid post,  I am now a believer.  The counter has to be reset to one for each group - the ID doesn't do that.  Thanks for your patience.

Ray
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Not to worry.

I am still in awe of your skills at SQL...

Remember when you bailed me out of this Q:
(The asker thought they had an "Updateable" crosstab query.)
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_23567031.html#a22019465

;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial