Missing data on a grouped/sorted report

[anubis2k7]

Hi,

I am having a problem with dynamically sorting/grouping data in my report at runtime. My problem is that when the report is run using sorting/grouping I am missing data.

Specifically, my situation is that I have a query that contains results from 2 tables that have a one to many relationship to each other (ex. 1=Contact, M=Contact Incident, 1 Contact can have multiple Contact Incidents). This query is the dataset for my report.

I want to be able to sort by fields from both “Contact” and “Contact Incident.” I have the following code on the “On Open” event of my report:

Me.GroupLevel(0 ).ControlSource = “<Contact field name goes here>”
Me.GroupLevel(1 ).ControlSource = “<Contact Incident field name goes here>”

In the report, I have two default group headers, “Groupheader1” (corresponding to Contact) and “Groupheader2” (corresponding to Contact Incident) which are preset to sort by Contact primary key and Contact Incident primary key.

The user decides which fields to sort by on a secondary selection form.

My problem is that when I open the report, I am missing records from the report. I have noticed two things: 1) if the record has a null entry in a field that I sorted by (ex. CONTACT_NAME, 80% of the time it will not show up 2) occasionally, the record belonging to “Contact” will not appear on the report, but the “Contact Incident” fields will appear but they will be shown belonging to another “Contact.”

This is kind of a bizarre error. I tried looking at the query itself, and all the records are shown. If I do not run any sorting or grouping on the report, all the records are displayed. I tried putting a textbox databound to “Count=(*)” on my report however the record count does not go down, even when I am missing records.

I have done some research and from what I read it seems that access is “combining” two of my rows and subsequently “hiding” the data.

Any ideas? Thanks in advance.

[dima69]

Hi,

I am having a problem with dynamically sorting/grouping data in my report at runtime. My problem is that when the report is run using sorting/grouping I am missing data.

Specifically, my situation is that I have a query that contains results from 2 tables that have a one to many relationship to each other (ex. 1=Contact, M=Contact Incident, 1 Contact can have multiple Contact Incidents). This query is the dataset for my report.

I want to be able to sort by fields from both “Contact” and “Contact Incident.” I have the following code on the “On Open” event of my report:

Me.GroupLevel(0 ).ControlSource = “<Contact field name goes here>”
Me.GroupLevel(1 ).ControlSource = “<Contact Incident field name goes here>”

In the report, I have two default group headers, “Groupheader1” (corresponding to Contact) and “Groupheader2” (corresponding to Contact Incident) which are preset to sort by Contact primary key and Contact Incident primary key.

The user decides which fields to sort by on a secondary selection form.

My problem is that when I open the report, I am missing records from the report. I have noticed two things: 1) if the record has a null entry in a field that I sorted by (ex. CONTACT_NAME, 80% of the time it will not show up 2) occasionally, the record belonging to “Contact” will not appear on the report, but the “Contact Incident” fields will appear but they will be shown belonging to another “Contact.”

This is kind of a bizarre error. I tried looking at the query itself, and all the records are shown. If I do not run any sorting or grouping on the report, all the records are displayed. I tried putting a textbox databound to “Count=(*)” on my report however the record count does not go down, even when I am missing records.

I have done some research and from what I read it seems that access is “combining” two of my rows and subsequently “hiding” the data.

Any ideas? Thanks in advance.

I suspect a logic mistake here. When you try to group by a field which is not unique in your table, you actually get one group header for all (different) records with the same field value. I guess what you really want is to sort the report without affecting the groups.
So I suggest you to leave the actuall group headers with their primary keys, and add two more group levels - just for sorting, without showing group headers.
Something like this:

Me.GroupLevel(0).ControlSource = “<Contact sorting field name goes here>”
Me.GroupLevel(1).ControlSource = “<Contact primary key goes here>”
Me.GroupLevel(2).ControlSource = “<Contact Incident sorting field name goes here>”
Me.GroupLevel(3).ControlSource = “<Contact Incident primary key goes here>”

while Group Levels 1 and 3 are actually show group headers, Group Levels 0 and 2 are for sorting.