Reduction of report pages.

CybranACU

Registered User.
Local time
Today, 03:26
Joined
Nov 5, 2015
Messages
13
Im not sure how to ask this question so I'll provide as much info as I can.
I have a database which was given to me as individual tables for each year, Im using union to combine them. I have a form that allows people to make selections based on things like location, year, types, ect. It then dynamicaly creates a query to select those things and then combines it with data from another table, which then gets set to a report. What i would like to do is check if all of the data is the same then show one record for each location where years had all the same data say 2008-2015 rather than as it is now displaying each record as a seperate page. There are a few things to check, 30+ or so. Im not sure where to start this. Should this be done in SQL or can I do it as part of the report after I have the data, or should I do this first and then send the new data to the report?
Example:
Records: LocationA , Year1, thing1, thing2, thing3, thing...
LocationA , Year2, thing1, thing2, thing3, thing...
LocationA , Year3, thing1, Sprocket2, thing3, thing...
Output(of sorts):
LocationA for Year(s) Year1-Year2, thing1, thing2, thing3, thing...
LocationA , Year3, thing1, Sprocket2, thing3, thing...

I've tried grouping but i dont think its complex enough for what im wanting to do.
I could do code but im not very familiar with access, and where i should put it in this case.
 
I'd like some clarification on your table structures before we go much further, I suspect there is room for improvement. If you could post their structures in this format:- The Table name, and then underneath the Table name a list of the fields in that table. Do that for all the tables, that should do for a start.
 
Last edited:
All of the data is brought in through a union query. But the table structure is such.
AYear:
Location, locationid, f1, f1c, f1l, f2, f2c, f2l, ... f17, year.

This is how it was given to me, and its sufficient enough not to change.
 
I've tried grouping but i dont think its complex enough for what im wanting to do.

Do you mean that you don't think it will be possible to get the answer you want by using grouping in SQL queries?
 
From my attempts at getting grouping to work it has not. Not that im an expert with it. If you think I could solve it with grouping let me know, in as great a detail as possible.
 
Starting with the union query you now have try this:

1. In the query builder make a query from the union query In this new query put in the criteria for the year span you want, e,g, 2008-2015 . Leave the year out of the query result, i.e., uncheck it. I guess the years will be coming from a form. If you don't know how to do this let us know.

2. Put this query in the SQL view and after SELECT put in the word DISTINCT. Make sure there is at least one space on both sides of the word DISTINCT. The DISTINCT keyword will get rid of any duplicates

3. Use this query as the record source for the report. You can group by location in the report.

You will probably want something in the report about the year span. Maybe the title being something like Blah Blah for the Years 2008 to 2015. One way you can do this is by referencing the information in the form in the report's open event. If you don't know how to do this let us know.
 
Last edited:
I want, or at least want to count, for each location the years that everything is the same. Would distinct still alow me to do this? My understanding was that it would not bring in other values that are the same, or allow me to count them. And if I use it on every field and at least one of them had any value that was the same and at least one that was different would it also be thrown out?
 
I want, or at least want to count, for each location the years that everything is the same. Would distinct still alow me to do this?
Thats a different query than what I described. I described a query that would show the unique information for a span of years. I'll think about this other query and get back to you.
My understanding was that it would not bring in other values that are the same, or allow me to count them. And if I use it on every field and at least one of them had any value that was the same and at least one that was different would it also be thrown out?
I'm not sure what you mean. It would take out any duplicates for the span of years. There would be no point in seeing the same information twice.
 
I want, or at least want to count, for each location the years that everything is the same.
I thought about this and I'm pretty sure I don't understand what you are saying. Do you mean that everything at a given location could be the same for several years? If so you could do this in a report just group on location and everything and then the detail will just have the years. You can put in a record count for that.

If you could tell us what this data (everything) is I think we could provide better assistance.
 
Do you mean that you don't think it will be possible to get the answer you want by using grouping in SQL queries?
I suspect the tables need redesigning hence my previous question...
 
Each record is for a location for a specified year, It looks similar to my first post as LocationA I want to in some way check or compare that if everything in the record is the same as the year before/behind it then dont show this record but let the end user know it was from years 2016-2006 or such, but also need tobe able to point out if something did chage say, LocationB from 2006-10 then output the changed record, ex. 2011, and if that policy stuck it should say LoactionB 2011-2016. The values in the record rarely change from year to year. If i could do this i could reduce the report from outputing say ~ 3300 (all locations for each year shown) pages to `330 pages (One location for multiple years where nothing changed).

Hopefully I clarified what I'm trying to do.
 
I hope some other forum member can think of some easy way to do this. I can't. Ignoring the exceptions for the moment what could be done with some vba is this:

1. Create a table for the output which will only different from the query in that the year will be a year span rather the just a year.

2. Create a query that sorts the existing union query on location, then year, then everything else.

3. Open a record set of this query and loop through all of the records keeping track when everything (all fields except year and location) changes and inserting records in the the output table accordingly. This would require code to compare everything in one record to everything the next record which because of nulls can be tricky.

4. Run the report base on the output table. When the report is closed delete the records in the output table.

I'm not sure how long it might take for this code to run, but if it could save some trees it might be worth the wait. The other thing about this approach that's not so desirable is that your database would grow in size by the size of this output table every time you run the report. The size wouldn't go back to the original size until you ran a compact and repair. So you would probably want to do a compact and repair often although nowadays with terabyte drives so cheap maybe this isn't such a big deal.

I be willing to give this a shot if you can upload a stripped down version of your database, but maybe you should wait and see if some other forum members come up with something better.
 
I am not at all against coding in something to do this. But im not much a VB coder myself, If you could let me know a general example as to how to get this started i could probably do that. Also where would I want to put this? Right now users select what data the want into a form and that generates a query that is sent to the report. Would I want to do this at the begining of the report after the query?
 
Would I want to do this at the begining of the report after the query?
Yes, that shouldn't be a problem. I'll see if I can write some code that's generic so that you can adapt it to your problem. This may take a little time, probably by tomorrow.
 
I was working on the some code to solve this when I stop to consider the query I was using and I think I might have stumbled across a solution that doesn't required code.

In the attached database I set up a table with a location and year field, and fields F1, F2, and F3 that represent the rest of the data. The query named "Group by Location and Everything" groups the data by the location, F1, F2, F3 and the displays the minimum and maximum years for this grouping.

I think this might be what you are looking for. Please test this concept and see if it works. If it doesn't do what you want please tell me where it is going wrong. There no point in me writing any code right now as I believe this query produces what my code was going to do.
 

Attachments

This is very similar to what I was looking for except that in for instance the last case the record is displayed only the min and max years even through a year that changed between them ex. Tucson. I'm looking to show in that case each year seperating the records when they were together and seperate those that have changed.
 
I believe one thing you want is that for single years where there was a change it would display a single year and not a range; display just 2002 and not 2002 - 2002. Is that correct?

Other than that is there some special order you want them in? If so please explain and give me an example.
 
Displaying a range rather than a single year is not a big deal, I can fix that before the end result. But mainly dividing it where a year was different and then grouping by those that were the same, ex 2006-2008 for A , 2009-2011 For B, then 2012-2015 A again, but as a single records but A should be two because it changed and then went back. So that it I can identify changes in the middle of these records and know when they happened. If that helps.
 
I see what you mean. That doesn't work. I guess there was no way it could be that simple. Well it's back to the code. I try to get you something soon.
 
I just wanted to let you know that the code is moving along. Probably have something by tomorrow
 

Users who are viewing this thread

Back
Top Bottom