Stop Report repeating for each record in subreport (1 Viewer)

ZenDiagram

Registered User.
Local time
Today, 13:46
Joined
Aug 28, 2018
Messages
37
Hello.

I was hoping someone could go into this sample database and help me with a few things.

Purpose of the database:
to hold data about owner's and their properties. (one to many)
That data's sole purpose is to be generated onto a yearly report that goes to each owner.
The report is effectively a "application" on which the owner's review the data of their properties, write any changes for the new year, and send it back to my office. I then update their records.

You will see that the report , "rpt_YearlyApplicationToOwners" is used as the yearly "application" sent to owners.

It has two subreports. One for property details named "sbrpt_OwnerPropertiesQuery" and one for forestry details named "sbrpt_OwnerProertiesForestryQuery"

First problem: for every record of parcel information in the "sbrpt_OwnerPropertiesQuery" , the main report repeats. i.e., if the owner has 4 property records in the subreport, the application will repeat 4 times.

arnelgp went into the database and fixed this before, but I was unsure how to replicate that. He did some sort of group by in the record source query.

Thank you,

Zen
 

Attachments

  • 2.4 example.accdb
    1.6 MB · Views: 74

mike60smart

Registered User.
Local time
Today, 16:46
Joined
Aug 6, 2017
Messages
1,899
Hi Zen

The problem is down to poor normalisation

The query is based on tblOwnerProperties.

Each Streetname is made up of a Number of Parcels with Each Parcel having Specific Acres

This means instead of 1 Table to deal with Properties you need at least 3.
 

ZenDiagram

Registered User.
Local time
Today, 13:46
Joined
Aug 28, 2018
Messages
37
Mike60smart,

I know my database isn't normalized completely but I want to keep it this way. Is there a way to fix this without restructuring my tables?
 

mike60smart

Registered User.
Local time
Today, 16:46
Joined
Aug 6, 2017
Messages
1,899
Hi

I would recommend that you bite the bullet and redesign.

You will always have to do work arounds to get correct data
 

GinaWhipp

AWF VIP
Local time
Today, 12:46
Joined
Jun 21, 2011
Messages
5,901
Putting aside that this database is going to be a constant work around project in its present state. You could just do a Group By (the Totals button) with the Record Source of the Main Report.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:46
Joined
Feb 19, 2002
Messages
42,971
Zen, you are letting the form dictate the design. The form design can change if the tax code changes. The temptation to just model the form is strong so I understand it. It will almost always cause you more pain in the long run but if nothing has changed on the form for 10 years, you are probably OK for a while. That doesn't mean that I would do it but if you can't devote enough time to this to create a good model, then I'll help as best I can. However, the form is confusing as are many government forms. Is the bottom section "Agricultural or horticultural" a recap of the acreages from the individual properties? Or is it something else that you are not keeping in the tables? It is not bound which is why I am asking.

The duplication problem is caused because the main report is bound to a query that joins the owners to the properties. That makes one main form page for every property rather than for every owner. To solve this problem, you would just delete the properties table from the main query. However, doing this prompts for Classification. If Classification is different for each property, then it cannot occur ONCE on the main form, it needs to appear on the type of classification subform and so be stored in the property table.

I can't resolve this conflict without a better understanding of the data.

And as we've mentioned before, limiting to two owners and one address is completely illogical. For example, I had two houses for a while. The first house was titled in the name of my husband and myself. He passed away. I bought a new house in just my own name. So I'm going to exist as an owner on two properties but I will get two forms rather than one. Is that true? Surely you must have lots of situations where the owners are not married and therefore do not live at the same residence. Who gets the form?
 
Last edited:

Users who are viewing this thread

Top Bottom