MULTIPLE DUPLICATE RECORDS IN REPORT

[Vic]

I have two tables. Table one is a customer table with general information.
Table 2 have activity relating to the customer in table 1. For examples lets
say your tracking purchases for a customer and table 2 is housing that
information.

Here is the problem.

I have defined a field in the report that contains the customer information
in the detail section due to the fact that there are multiple different
customers. I have setup a text field that has purchase information from
table 2. That text field has the following in the source code
=avg([pdrating]).

That works great and give me the average from table 2 for that customer.
Here is the problem. Let's say that customer has 4 purchases enter in table
2.

It repeats the customer information 4 times on the report.

So what am I missing. I can't figure out what I'm doing wrong. The
computation is correct and the information is exactly what I need. Just
don't need the customer repeated as many times as there are records for them.

Thanks Everyone

 

[Evi]

Have you tried this, Vic?
Click the Sorting Grouping box in report design view.
Put your CustomerID or whatever your unique field is called, in there and
choose Yes next to Group Header.
Drag your customer controls into this header area.

Evi

 

[Vic]

Yes. Tried that as well. Still repeats customer information.

 

[Allen Browne]

Evi is right.

If you create a group header for the CustomerID field, and move the controls
into that header section in design view, the customer information will
appear once above the details.

Either you did not understand it, or you have other fields above the
CustomerID in the Sorting'n'Grouping dialog, so it is grouping on other
things first.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Yes. Tried that as well. Still repeats customer information.

I have two tables. Table one is a customer table with general
information.
Table 2 have activity relating to the customer in table 1. For examples
lets
say your tracking purchases for a customer and table 2 is housing that
information.

Here is the problem.

I have defined a field in the report that contains the customer
information
in the detail section due to the fact that there are multiple different
customers. I have setup a text field that has purchase information from
table 2. That text field has the following in the source code
=avg([pdrating]).

That works great and give me the average from table 2 for that customer.
Here is the problem. Let's say that customer has 4 purchases enter in
table
2.

It repeats the customer information 4 times on the report.

So what am I missing. I can't figure out what I'm doing wrong. The
computation is correct and the information is exactly what I need. Just
don't need the customer repeated as many times as there are records for
them.

Thanks Everyone

 

[Evi]

I have two tables. Table one is a customer table with general information.
Table 2 have activity relating to the customer in table 1. For examples lets
say your tracking purchases for a customer and table 2 is housing that
information.

Here is the problem.

I have defined a field in the report that contains the customer information
in the detail section due to the fact that there are multiple different
customers. I have setup a text field that has purchase information from
table 2. That text field has the following in the source code
=avg([pdrating]).

That works great and give me the average from table 2 for that customer.
Here is the problem. Let's say that customer has 4 purchases enter in table
2.

It repeats the customer information 4 times on the report.

So what am I missing. I can't figure out what I'm doing wrong. The
computation is correct and the information is exactly what I need. Just
don't need the customer repeated as many times as there are records for

them.

Have you tried this, Vic?
Click the Sorting Grouping box in report design view.
Put your CustomerID or whatever your unique field is called, in there and
choose Yes next to Group Header.
Drag your customer controls into this header area.

Yes. Tried that as well. Still repeats customer information.

Vic, is it possible that the tables on which the report is based aren't
joined?
Have a look at the query on which the report is based. If it isn't based on
a query then create a query from the 2 tables and base a report on it. Put
the Primary Key field from the 'Orders' table (ie your table that has many
items that correspond to one item in the other table) into the grid along
with the other fields including the field that links it to the customer
table. Put the fields which you require (but not the Primary Key field) from
the Customer table(or your table with the single item) into the grid. Join
the tables FROM the Primary Key field in your 'Customer' table TO the same
field in your 'Orders' table. If you want to see all the customers even if
they don't have Orders, ensure that you use the join that says Show all the
Records from the Customer Table

Base your report on that and group as I described. Please let us know what
you discover. If you are having a problem, someone else will also be having
it.
Evi