Skip to content

Old Access reports – and how to tweak them

January 19, 2016

This was the email from my customer.  ”

Hi James!

I’m hoping you can help me with the system.  I just printed out the District final request and there is an odd problem.

So…

  1. Go into the system, go to reports, requests, district final requests, and look at …. Gananda for example.  You will see it starts with AV100 and then CC32……etc…. now scroll to the 7th  page and you should see towards the bottom CC31, 34, 42, and 43. I must have put them in wrong or something, can you make them go in order with the other CC kits on page 1?
  2. Also if you look at the 7th page again, you’ll see a section break where it looks like the school is supposed to add the amount of the SM kits, EM kits, AS kits, CC kits, etc.. However earlier in the document there is nothing for the ES kits or the SC kits. Can you put a break in there or if anything there could be a break at the bottom of each page with a “sub-total”. 
  3. And… if you look at the section break is says 518.000  SM s  I don’t know what the “s” is I think  it used to say subtotal or something.  Can you check that too?
  4. One more, can you put page numbers on this on the bottom right??”

Oye.  Half the battle is almost always understanding the request…

This is reasonable, but for me, difficult to solve.  I usually want to be a technician and not a demolition expert.  Translated meaning I want to solve the issue – help my customer, with as little change to the application as possible.  This is true because the application is a 17 year old MS Access application that does the job.  If there is new stuff asked for, that is one thing, but when I am asked to fix something, that usually means be a technician.

The report in question actually uses a table as the source, not a query.  That is the first thing that seems odd to me.  I am used to seeing a query as the source to a report, not a table.  There is functionality in the app. that when executed ends up deleting the table in question and then rebuilding and populating it.  Fine, its logical, but still odd.  My customer then, one a year, creates new order by entering it and then executing the routine responsible for building and populating the new table.  The table is populated with about 10,000 records because it rights every district and whether or not it is using a kit for the new year.  In reality, this report should have a source of a query, which would only pull about 100 records per district, instead of 1000.  But, I digress.

So, this is the interesting thing in the report.  The grouping and sorting area at the bottom.  Again, sorting and grouping and filtering are things that typically happen in a query.  The query is the laizon between table data and reports, but not this time.

This is the group – sort – total area of the report, where these rules that are usually defined in a query are in the report.

group_sort_total

Getting the right order, especially in the Sort and Grou on Kit_number was the real tricky part for me.  After looking at this issue a couple times, I was able to understand what was being done here and be a technician.  Grouping first by districtID, then sorting by kit_number and then group again on kit_number – using only the first 2 characters of each kit number.  Seems trivial now, but it frustrated me for longer than I want to admit.

A snapshot of the report, shows the output and why it does make sense this way.

sample_finalrequest

and then the total section when the kit number changes from ES

kit break

So records are grouped by district and then sorted by kit number first (so they are all together) and then grouping on kit number, the first two letters of, and then totaling for each kit type, like “ES”.

Once it worked, it made sense, but not a minute before.

 

Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: