Duplicate Records in Report View

[Scott Whetsell, A.S. - WVSP]

I have a table in which several fields allow for the selection of multiple
values. In my report which lists the table records, it lists a duplicate
record.

Ex. The table has the following fields:
field 1 = date
field 2 = time
field 3 = notes
field 4 = customer (allows multiple selection from other table)
field 5 = representative (allows multiple selection from other table)

The report is grouped by date, then sorted by time.
If there are 2 customers and 3 representatives selected, then it creates 5
records in the report, all with identical information.

How can I make it list the record only 1 time?

Thanks for any help that can be provided

 

[Duane Hookom]

Are you using Access 2007? Seems to me this would be the only way to select
multiple records from another table.

You should be using a simple junction table with subforms to add records to
the junction table. Then use subreports to display the 2 and 3 records.

 

[Scott Whetsell, A.S. - WVSP]

Yes, I am using Access 2007. I'm not 100% sure what you mean by using a
junction table.

I have several tables already which hold their own respective information.
Customers are stored in one table, representatives in another, and the call
for service details in yet another. The call for service table is the
primary table which relates them together. It provides a lookup field for
customers, where several can be selected from a query of the customer table,
as is the same for the representatives.

 

[Duane Hookom]

Are you suggesting that a single call for service record can relate to more
than one customer and more than one representative?

I would create a junction table in place of any where that you have a
multi-select list control/field.

 

[Scott Whetsell, A.S. - WVSP]

Working for a police agency, on each call for service we could have multiple
complaintants, multiple suspects, and several officers assigned depending on
the nature of the call.

As my complaintants and suspects pull from a qry from the same tbl, what
would be my best practice for the junction table?

Thanks,
Scott

 

[Duane Hookom]

If one call can have multiple complaintants then I would expect to see a
table that would have a ServideCallID in a record for each complaintant.
This table would be used as the record source for a subreport.