Removing Duplication in Complicated Report

[Pat Hartman]

One report does not have an impact on the other. If a report does not have sorting defined, we will end up with an unordered set of data. Tables and queries are by definition unordered sets. Access confuses us into thinking that tables maintain some sort of physical order but that is because whenever you run a C&R, one of the tasks is for Access to sort every table into PK sequence and then rewrite it in physical order that way. Unless you update the records with lower PK values and cause them to move around, you will never notice. I've posted about this many times along with examples. The effect is much easier to see when working with SQL Server due to the different way that SQL Server actually retrieves records. But you can force the effect to manifest itself if you understand the problem and what causes records to be moved to the end of the file.

 

[Deleted member 73419]

One report does not have an impact on the other. If a report does not have sorting defined, we will end up with an unordered set of data. Tables and queries are by definition unordered sets. Access confuses us into thinking that tables maintain some sort of physical order but that is because whenever you run a C&R, one of the tasks is for Access to sort every table into PK sequence and then rewrite it in physical order that way. Unless you update the records with lower PK values and cause them to move around, you will never notice. I've posted about this many times along with examples. The effect is much easier to see when working with SQL Server due to the different way that SQL Server actually retrieves records. But you can force the effect to manifest itself if you understand the problem and what causes records to be moved to the end of the file.

I'm sorry, I just don't think I'm getting this.

All the Reports are sorted:

in the case of Report 3, AA is a PK.

I'm just not understanding why there is inconsistency in the order of Report 3 when it is sorted. Some records are in the right order but not others. I just cannot make sense of it.

 

[Pat Hartman]

I don't see A1 in report 3

 

[Deleted member 73419]

I don't see A1 in report 3

Ah yes, sorry. I modified Q3 (which is the record source for Report 3) so that it includes A1 and B1.

SELECT DISTINCT Q2.ID, FNT.ID, FNT.AA, FNT.BB, Val(FNT.AA) AS A1, Right(FNT.AA,1) AS B1
FROM FNT, Q2
WHERE (((FNT.ID)=[Q2].[ID]))
ORDER BY Q2.ID, FNT.ID;

I did this so that A1 represents the numerical part and B1 represents the alphabetical part. This was a trial so see whether I can first sort numerically (A1) and then alphabetically (B1).

This has the correct desired effect when Report 3 is opened standalone but not when Report 3 is opened as a sub-report of Report 1.

 

[Pat Hartman]

Your data makes no sense so I can't see where the issue is. I added more fields to the subreports. Maybe you can use them to see what is wrong.

 

[Deleted member 73419]

Your data makes no sense so I can't see where the issue is. I added more fields to the subreports. Maybe you can use them to see what is wrong.

Hi,

This is proving a little tricky to describe...

Opening Report 1, there are 50 records in total.

Looking at record 10 you can see the order of the Sub-Report 3 item AA starts with 10 at the top and 9 below:

However, when moving on to record 11 the order of AA has swapped to 9 at the top and 10 below.

So it looks like the order changes on some of the records? That was what I was trying to deal with on some of my earlier posts by being able to firstly sort numerically and then alphabetically.

It seems rather strange that the order changes on the same Sub-Report between records. The order of Sub-Report AA, changes throughout the 50 records but I chose records 10 and 11 as these highlighted the issue nicely.

Does this make sense now?

 

[Space Cowboy]

I have recently had a couple of similar but not identical problems,

in the end it turned out to be either a join that needed changing or a date configuration, or data input clerk error.

 

[Pat Hartman]

I added the additional controls and brought them to your attention so you would look at them. Notice that on page 10 ID 10 comes before ID 9 and on page 11, ID 9 comes before ID 10. That explains the order of the details. If you go back to the beginning of the report, you see that sometimes the middle grouping sorts low to high and other times it sorts high to low.

I also removed all the sorts from the queries.

The problem turned out to be the master/child links on 1/2 were incorrect. I might have figured that out yesterday if the data had made any sense. You were using ID but that was not unique and that was causing the confusion. Once I switched the link to use AAA the random order went away. I also had to change some of the report properties to make the pages break correctly once I fixed the first problem.

There is something wrong with your schema. You should not have to use a cross join to get the data you want. I have no idea what your data is so I cannot help with it but in the abstract your design is incorrect so it is likely that it is also incorrect in the concrete.

 

[Deleted member 73419]

I added the additional controls and brought them to your attention so you would look at them. Notice that on page 10 ID 10 comes before ID 9 and on page 11, ID 9 comes before ID 10. That explains the order of the details. If you go back to the beginning of the report, you see that sometimes the middle grouping sorts low to high and other times it sorts high to low.

I also removed all the sorts from the queries.

The problem turned out to be the master/child links on 1/2 were incorrect. I might have figured that out yesterday if the data had made any sense. You were using ID but that was not unique and that was causing the confusion. Once I switched the link to use AAA the random order went away. I also had to change some of the report properties to make the pages break correctly once I fixed the first problem.
View attachment 115001

There is something wrong with your schema. You should not have to use a cross join to get the data you want. I have no idea what your data is so I cannot help with it but in the abstract your design is incorrect so it is likely that it is also incorrect in the concrete.

I'm not sure whether we are looking at the same database here but there are now over 1100 records where there should be 50.

The ID order is not correct:

as 4 does not come before 3 and there is a lot of duplication:

 

[Pat Hartman]

I added the additional controls.

I fixed the master/child link on the middle subform. You CANNOT use ID as the link.

This is your garbage data. It makes no sense to me. The joins are wrong. The schema is wrong. If I don't know what the data even is, I cannot begin to help with your schema problem and I've already spent hours on it.

Go back to your version where you think the only problem is the sort order. Add the additional fields to the report because otherwise you don't have a clue what you are actually looking at. Then recognize that you can't use ID as the master/child link. Change the queries so you can use something else that makes sense. Once the master/child link was fixed, the sort order became predictable and "correct" based on your description.