Solved Slow DB for Report (1 Viewer)

[Weekleyba]

I've put this small DB together that I hoped would be part of a larger application.
The problem I have is, it's slow.
The table where all the data is, IHS - Allowance Status by Project and Location, has 48K records when fully imported from Excel.
I've deleted most of them for the attached DB and even then it's bit slow.

I have a feeling that my approach in developing this is two complicated....??? Many queries, many subreports, …. etc.

If someone could take a look and let me know if there is a way to speed this up or if on suggestions on how to accomplish this another way, I'd be very appreciative.
Thanks

 

[plog]

First, you haven't added any descriptions to your fields in your tables, any comments to your VBA code nor completed your Relationship Tool. This is a big impediment to me, a person 100% unfamiliar with your data and this database, to understanding the overall goal of it and helping you with this issue. Its going to be a moderate impediment to someone in your organization who comes later and needs to fix something. And its going to be an irritant to you in the future when you can't exactly what you were doing in one section of it.

Second, you need to let queries do the heavy lifting of getting the data ready for your reports. Funding_R has 4 sections each with 5 subreports for a total of 20 subreports. All of those are unnecessary. Funding_R could be done with one report fed from a properly structured query. I suggest you run through a few query tutorials (https://www.w3schools.com/sql/).

Third and foremost, you don't really understand normalization (https://en.wikipedia.org/wiki/Database_normalization). Read up on that, its how data is supposed to be structured in tables. The weird part is your tables actually look good in that regard, but you broke all that with all your queries. 1 big rule of normalization is to not store data values in the name of the object.

Let's take "EQUIP_FED_CurrentFY_Q" as an example. 'EQUIP' should not be in the name of a query, nor even the name of a field. EQUIP should be the value in a field, that field could also hold the values "FSA", "M&I" and "QTR". Doing that eliminates 3 queries. Then, FED should go in as the value of a field in the same manner. Lastly, CurrentFY should as well. All those queries named similarly can be consolodated into 1 query that has all those fields which differentiate the data.

Lastly, I'm not exactly sure what you are doing with all those UNION queries, but I believe its a hack to force records to show up when there is no other data. There are better ways to do this. However, I suggest you focus on the above before worrying about that.

Again--give normaliztion a quick read, work thru SQL tutorials, then consolodate your queries which will allow you to eliminate a lot of those subreports, if not all of them.

 

[Weekleyba]

Thanks Plog.
Ill work on all of it and get back to you on my progress.
One thing, when it comes to normalization, I thought that only matter to the tables. Never thought of applying to queries too.
thanks again.

 

[Weekleyba]

I'm afraid I need some more help with this.
I don't know how to set this up without using multiple subreports or subforms.

For the report below, I need to first filter by the IHS #, then for each of the four sections, I need to filter by the BAP (like 7202450326, 7400450149, etc.), then by the Year (CY or PY) then sum the 'Funds Available'.

Can someone show me how to set that up for just one of the BAPs, like "M&I"?
(The M&I Budget Activity Code or BAP, is 7202450326)

It seems like it would be easy, like setting Select queries for each textbox but, I'm just at a loss here.

Below is what I'm trying to generate and did create it but, like Plog indicated, it is not the correct way to do it and that's probably why it was taking so long to generate the report.

Any help would be appreciated!

 

[plog]

We are not familiar with your data. That doesn't mean we can't help, it just means you have to be extremely explicit in referencing the tables/fields/data in your databas:

(The M&I Budget Activity Code or BAP, is 7202450326)

You have no field called 'BAP' nor any field called "Budget Activity Code". Where are we to find the value '7202450326'? You have to be extremely specific.

For you and us, I suggest take that image of the report and label each piece of data (M&I, Funds Available, Allowanced, etc.) with the field the data comes from. That will be enough for me to start on a query, but might also lead you to building it as well.

 

[Weekleyba]

Thanks Plog
I will do so when I get back home, though all the data is really in one large table IHS-Allowance Status....of which I download from another source and import into this database. The other tables are there to add a couple fields BAPDescription and the location name.

There should be a field in the IHS table named Budget Activity Code.
I’ll jump on it when I get home.
thanks

 

[Weekleyba]

Ok. Hopefully this will provide some clarity on what I'm trying to do.
Sorry for the confusion.

The goal is to create the report shown below per Location per Budget Activity Program.

The report query would have a filter structure like the following:
IHS - Location (per the Main_F, cboLocation1)
Budget Activity Program (four different ones shown - 7202450326, 7400450149, …)
CY-PY (Current Year, Prior Year)
Funds Available (sum the current year and prior year)
(All these fields are in the table, 'IHS - Allowance Status by Project and Location')

So for #1 below, to get the desired dollar amount, a query would have to have the correct:
Location (per the Main_F, cboLocation1) of '45211SDH000000'
Budget Activity Program of '7202450326'
CY-PY of 'Current Year'
Funds Available, sum this column
This would give $164,668.00

I can easily create this query but, need to also get #2, and #3, which is a different query....right?
And then after that, I need to do it all over again for the next Budget Activity Program of 7400450149, and others.
This is why I was writing multiple queries.

Plog, you mentioned I could reduce the number of queries and subreports but, I'm not sure how to do that.
If you could show me how to do write query to do #1, #2, and #3, I could probably figure the rest out.

Let me know if you need more info as I'm happy to provide since I really want to figure this out.
Thank you.

 

[plog]

First some critiques of your tables:

1. Only use alphanumeric characters in names. This means no dashes, pound signs, nor spaces. This just makes coding and querying easier.

2. Use primary keys correctly. ID is the primary key field in Locations_T, but you use [IHS - Location] as a foreign key in [IHS - Allowance Stats...]. You should get rid of the ID field and use [IHS-Location] as the primary key (after you rename it per #1 above)

Now for your report. Like most seemingly complex things, the real trick in accomplishing them is knowing how to break the whole thing down into achievable steps. So, throw your filter criteria aside and focus on just one query. We need to make a query that has all the data you need so that your report can be created from that one query. I have attached a database with that one query.

Funding_Q is the only query you need to build your 1 report (no subs needed). Here's what I did and why:

1. Added BAP_T table. This converts your Budget Activity Program codes to the text that appears on your report (M & I, Quarters, etc.). It is also used to force null records into the final query..

2. Used YearsLocationsBAPs_Q to force null records into the final query. You did this via a ton of UNIONs, I accomplished this in what's called a Cartesian JOIN. It's a fancy way of saying I used tables in a query without relating them. This way every Year (Current & Prior), Location (from Locations_T), BAP (from BAP_T) combination will show in the final query.

3. I used the CY - PY field as criterai in an IIF statement instead of as criteria on the query as a whole. By doing this I was able to get your all your summed funds fields into the same query as different fields.



Look over the database, make sure you understand what I did (ask any questions). Then, your job is to make a report based on that query. Again, hold off on the criteria element. Make a report that spits out everything. You will need to convert the current Report Header to a Group Header using the Location.

Last you will fix your form to apply the criteria there. The code that runs when the "Open Report" button is clicked currently runs a Docmd.OpenReport. That function allows you to apply criteria to it, thus opening the report with criteria applied then. That is how you do the criteria portion. Check out the documentation for more help:

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

 

[Weekleyba]

Thanks Plog!
I'm making headway but don't have enough time right now to work through it all.
One question though, why do you include 'loc_Code: IHS - Location' in the YearsLocationsBAP's_Q?
I don't see a use for this as it's the same as 'IHS - Location'.

I'll work more on this tonight.
Thanks again for the help and …..Cartesian Join? Never heard of that before. Very nice!!!

 

[plog]

One question though, why do you include 'loc_Code: IHS - Location' in the YearsLocationsBAP's_Q?

I was going to use it instead of [IHS - Location] because of the naming rules I mentioned prior, but then I didn't completely do it. I used loc_Code to show in Funding_Q, but used [IHS - Location] to JOIN to the [IHS - Allowance Status...] table.

So, you can remove one of them, but you will first need to fix Funding_Q to use the one you are leaving, prior to deleting it from YearsLocations_Q.

 

[Weekleyba]

First off, thank Plog! There's no way I could've thought off this on my own or searched the internet for this answer. This is such a better solution!!

I'll clean this up some more, with VBA comments, text box labels, etc., but is the report I have made what you would have done? See attached.

Also, is there a way to have the report place FSA and Equipment next to Quarters and M&I? See below.
Is this where I would have to use di reports to accomplish this?
Thanks again.

 

[Weekleyba]

Forgot to attach the db.
Here it is.

 

[plog]

For that layout its going to require subreports. The good news is, its only going to require 2 subreports and the reports themselves will be exactly the same:

Make 2 Funding_Q queries--one that will hold the BAPs for the left column and one that will hold the BAPs for the right column. So now you have Funding_Q_Right and Funding_Q_Left each with criteria to show just the BAPs they are to show.

Copy Funding_R and name it Funding_R_Right. Change the datasource on Funding_R_Right to Funding_Q_Right and delete the Location Grouping on it. It should just have a Details section.

Copy Funding_R_Right and name it Funding_R_Left and change its data source to Funding_Q_Left.

Change the datasource of Funding_R to Locations_T and delete the items in the Detail section. Instead you will use Funding_R_Right and Funding_R_Left in the detail section of Funding_R, linking parent and child appropriately

That should give you the format you want (you might have to mess with the ordering in the sub reports to have the 2 items appear in the correct order.

 

[Weekleyba]

Thanks Plog!
I did exactly as you suggested and it not only looks good but it's working sooooooo much faster.
Very good lesson for me. Cartesian JOIN....now I know.

 

[plog]

That's just a hack. The real lesson and what did the heavy lifting was fundamental SQL querying. Practice those and then learn the tricks.

 

[Weekleyba]

Plog,
As I'm adding additional info into this database, I ran into another stumbling block that I could really use an assist on.

My Locations are in three categories. Service Units (which are Federal) , Tribal, and Area (Federal as well).
Some of the Budget Activity Program (BAPs) codes only apply to Service Units or Tribal or Area.

How do I get my report to show only the BAPs that apply to that Location?
In the db attached I modified the M & I BAPs to show both the M & I Federal and M & I Tribal to demonstrate the problem.
When you run the report for "Belcourt Service Unit", it shows both the M & I Federal and M & I Tribal. I only want the M & I Federal to show in the report, in this case. See below.

I think I need use the Locations_T which has the data for each location, as to whether it is a Service Unit, Tribal, or Area, but I'm stumped.
Can you help again?

 

[plog]

This is bigger than this or any report, this has to do with your table structure. You will need to modify and add data to the following tables:

Locations_T : Add a LocationCategory field as SHort Text. This field will hold the values "Tribal", "ServiceUnit" and "Area". Add the appropriate value to every record. Next, delete the Tribal, ServiceUnit and Area fields.

BAP_T: Remove the primary key from bap_Code and add a bap_Category field as Short Text. This field will hold the values "Tribal", "ServiceUnit" and "Area". Add the appropriate value to every record. Next, you will need to add more records to this table for every section you want on your report. For example, after completing the above steps for this table you will have one record where bap_Code=7120450121:

bap_Code, bap_Description, bap_Category
7120450121, Equipment, Area

That means only Locations with LocationCategory="Area" will have an Equipment show on their report. To get Equipment sections on the other Categories you will need to duplicate that record 2 times and change "Area" to "Tribal" and "ServiceUnit".

YearsLocationsBAPs_Q: After doing the above you need to change this query so that the new bap_Category and LocationCategory fields are joined in the query designer.

Do that and your report should work as you want. Before eveyrthing though, make a backup of your current database just in case anything goes wrong.

 

[Weekleyba]

I kind of thought this may be more complicated as I spent about 4 hours on it today with no luck.
I will work on this tomorrow morning if not sooner.
Thank you so much for your help!!
It’s like gold to me.

 

[Weekleyba]

Hey Plog - With all your help, I believe the database is now on the right track.
Of course, I'll have to toy with it some but, it's a much much better design, thanks to you!
If you're willing.....I may have you look at my other database that I've been working on for quite awhile here and there.
The db in this thread, I plan on inserting it into my larger one.
I knew my approach to this db (the one we worked on) was not right, but I didn't know how to fix it.
So, thanks for all the lessons.
Stay healthy.

 

[plog]

Absolutely, glad its working and more importantly making sense to you.

I suggest you post in the appropriate forum (Table/query) and then private message me that you did and i will be sure to check it out.