Duplicate Records with One-Many Tables

[Peter Hallett]

I have a table, listing sales in date order. Each transaction is identified
by its associated account number. A separate table relates the account
numbers to the corresponding customer IDs. The reason for the indirect
linkage is that although most accounts are individually held – ie associated
with a unique ID – several are shared between two, and sometimes three or
four, customers. In other words, the ID/Account No. relationship is
one-many. The associated table has two columns. When an account is added, a
new record is created, showing the new ID and the associated account number.
If an existing account is to be shared, the new account number field will be
the same as that of an existing account listed elsewhere in the table.
Clearly, there is no limit to the number of records that can be added to the
table or the number that can be associated with any given account.

A report is required which lists transactions effected between manually
selected start and end dates, entered on a form. There is no difficulty in
writing a query or SQL statement to produce the required list of
transactions, sorted by account number, within the required date range, but
this is of limited use. More important is to show the account holders by
name. This means using the ID/Account No. table to find the ID(s) associated
with each account number. However, if this table is included in any query or
SQL statement, used as the basis of a report, the associated transactions are
duplicated for each contributing ID. For example, if three customers share
an account, the transaction list is shown three times, leading not only to
confusion but to incorrect report totals.

The immediate thought was that the unwanted duplication could be eliminated
by basing the report on queries utilizing the unique record or unique value
property but this has proved impossible. The ID/Account No. table, being
one-many, contains numerous duplicate account numbers. It is not difficult
to eliminate these by means of a suitable select query using the unique value
property but, immediately the ID field is included in the query, the
duplicate account numbers – and hence the associated duplicate transaction
records – all reappear in the output. I have had to admit defeat on this
one. Anyone got any bright ideas?

 

[Guest]

Don't use the detail section of the report.

Instead, group by Account Number, and use the
Group Header and Group Footer.

The detail section will have a detail record for each
client in the account, but you can leave the section
empty and just hide the whole section.

If you want to do totals or subtotals in a query, do the
total or subtotal before joining the totals query to the
client table.

If you need to filter a subquery to get totals before
joining to the client table, you won't be able to use the
simple Access menu filter functions any more.

To filter a subquery before joining it to the client table,
use a filter table, (with date fields to use as filters), or
a filter form (referenced from the subquery), or VBA
lookup functions, or InputBox.

 

[Marshall Barton]

I have a table, listing sales in date order. Each transaction is identified
by its associated account number. A separate table relates the account
numbers to the corresponding customer IDs. The reason for the indirect
linkage is that although most accounts are individually held – ie associated
with a unique ID – several are shared between two, and sometimes three or
four, customers. In other words, the ID/Account No. relationship is
one-many. The associated table has two columns. When an account is added, a
new record is created, showing the new ID and the associated account number.
If an existing account is to be shared, the new account number field will be
the same as that of an existing account listed elsewhere in the table.
Clearly, there is no limit to the number of records that can be added to the
table or the number that can be associated with any given account.

A report is required which lists transactions effected between manually
selected start and end dates, entered on a form. There is no difficulty in
writing a query or SQL statement to produce the required list of
transactions, sorted by account number, within the required date range, but
this is of limited use. More important is to show the account holders by
name. This means using the ID/Account No. table to find the ID(s) associated
with each account number. However, if this table is included in any query or
SQL statement, used as the basis of a report, the associated transactions are
duplicated for each contributing ID. For example, if three customers share
an account, the transaction list is shown three times, leading not only to
confusion but to incorrect report totals.

The immediate thought was that the unwanted duplication could be eliminated
by basing the report on queries utilizing the unique record or unique value
property but this has proved impossible. The ID/Account No. table, being
one-many, contains numerous duplicate account numbers. It is not difficult
to eliminate these by means of a suitable select query using the unique value
property but, immediately the ID field is included in the query, the
duplicate account numbers – and hence the associated duplicate transaction
records – all reappear in the output. I have had to admit defeat on this
one. Anyone got any bright ideas?

You should use Sorting and Grouping in the report to only
display the account information once (in the group header).
The many side data is then displayed in the details section.
The total for all accounts can not be done using the Sum
function (as you have seen), but using a Running Sum text
box in the group header will do that.

Alternatively, you can use a subreport for the many side
data. This way the main report record source would just be
the one side table with the account info in the detail
section along with the subreport. This way, the Sum
function will do what you want.

 

[Peter Hallett]

My thanks to both respondents. As a result, I have now got the thing working
well.

It was not possible to avoid using the detail section of the report.
Grouping by account number in the group header only resulted in the display
of the first record for each account. In the end, I placed the field labels
in the page header with the associated field values in the detail section of
the account group. This gave the required listing of account transactions
grouped by account, filtered within the selected date window and sorted in
ascending date order.

The one-many account/customer ID relationship was handled by means of a
sub-report in the account group header. This lists customer IDs and names,
obtained from the customer name and address table which is joined to the
account no./ID look-up table. Gratifyingly, this sub-report now lists all
the customers sharing an account, exactly as required. The final bonus is
that, because the transactions are listed in the detail section of the
account group header, there is no difficulty with totals. In fact, it all
looks a bit too good to be true! The transition from intractable problem to
wholly acceptable result was achieved with far less difficulty than I was
anticipating, thanks to the advice offered. The duplicate reporting has now
been eliminated.