Skip to Content
0
Former Member
Nov 15, 2008 at 01:37 PM

Issue with Duplicate Results and Summary Fields

13 Views

My report is based on an Access Database. For the purposes of this current issue, there is the Master Table called u201CCnu201D and then a related u201CTable Au201D which includes multiple records related to the Master and also a related u201CTable B,u201D also with multiple records related to the Master.

For my report, I want to output results from Table B based on criteria in Table A. This naturally produces duplicate output on the report. As a result, the Summary Fields that I want to include are incorrect.

I have tried multiple combinations of the Select Distinct function, suppression based on u201Cprevious fieldu201D being equal, summary fields, running totals and if-then statements and still cannot find a solution that works in all of the cases involved.

Below are descriptions of three different scenarios that are occurring:

EXAMPLE #1: Entry 1 has 2 records in Table A and 2 Records in Table B so "Select Distinct" including the Table B ID # and then a Summary Field for the Total produces the correct results, while a Running Total is Incorrect.

EXAMPLE #2: Entry 2 has 2 records in Table A and 1 Record in Table B so a suppression formula of the field based on the "Previous =" Table B ID # and the Running Total produce the correct results, while Summary Field is incorrect.

EXAMPLE #3: Entry 3 has 6 records in Table A and 6 Records in Table B. Neither Summary or

Running Totals produce correct results.

I would greatly appreciate any advice in terms of creative formulas, etc. that would help produce correct results here.

Iu2019ll be pulling my hair out soon! Thanks so much-