WHAT CAUSES DUPLICATE DATA IN AN ACCESS REPORT?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created an Access report based on several queries, and the report has taken
some of the data more than once from the query. This affects the sum, since
duplicate data is added in to the total. What can I do?
 
Remove the duplicate records? Either that or provide more information. You
can sometimes create a running sum on a text box in a group header or
footer.
 
Duane- Thanks for the reply. I can hide the duplicate entries, but they
still add into the sum. In addition, some of the data is brought in twice,
particularly when a customer has more than one invoice entry. In this case,
both of the separate invoice entries have been brought into the report twice.
If there is some code I can write to correct this, can you please direct me
to it? Thank you!
 
Can you provide more information? Did you try using a running sum on a text
box in a group header or footer?
 
Duane-

This is a report I put together using Report Wizard, and it is divided into
various headers with associated footers. The problem is in the detail
section - the mathematics are correct, but the text boxes in the detail
section drawing the information from the query are drawing the information
more than once from the query if a customer has more than one invoice for one
day. The invoices have different products, but each of the products displays
once for each invoice that day, even though they're only on one invoice. By
the way- the query is correct; all of the invoices and products only have one
entry. Any thoughts?
 
You can modify a report created by a wizard. You can also tell us about the
grouping and sorting.

Apparently you are attempting to sum a value from a parent table and it is
repeating for each child record. Is this correct?
 
Duane:

Here's an outline of the structure I'm using: Billing details are stored in
a table with an invoice number and date. A query draws out the information
for a specified date. A second query draws out the information from the
previous month. There is a third query that tracks a running total of one of
the items. I brought the information from all three queries into the Report
Wizard and have the report grouped by: county, then Sales Manager, then sales
person. When Report Wizard asked how I wanted to view the information, I had
a choice of viewing the data by current month, last month, or running total.
When I chose current month or running total, the Wizard would not combine the
data. Choosing last month enabled the wizard to assemble the report.
Interestingly, it is only the current month data being brought in
incorrectly. I hope this is clear enough to understand what is happening,
but I can try to explain further if needed. Thanks for your patience with
this, and I appreciate your help!
 
Please try to create paragraphs in your replies. I have to work to hard (my
issue) to make sense all of this information. It's like when a co-worker
babbles on without taking a breath and I am still trying to digest the first
sentence.

What is the SQL View of the Record Source of your report? Are County, Sales
Person, and Sale Manager unique in your report or do you see multiple copies
of the same three fields?

Can you provide about 5-6 records from your report's recordsource and then
show us how they display in your report and how you want them to display?
 
Duane:

My apologies for the lengthy paragraph - I'd be happy to break it down. I
don't know how to answer your first question, since I don't know if SQL View
is Design View or something else.

The groupings by County, Sales Manager, and Sales Person each represent
headings and subheadings in the report. I would be open to providing data,
but have no idea how to do so in this discussion group format.

I know I can send a page of the report in html format, but I don't know
whether I could export a portion of the data from the table and/or query into
html. It seems that you'd need to see the data and query/report structure to
determine the root problem.

If there is no feasible way for me to get this data to you then I'll try to
find another solution. In either case, thank you for your help to this
point, and let me know of any suggestions you have for sending this
information to you.
 
You can open your report in design view. Then view the report properties and
find the Record Source property. Click the [...] button to view the query
design. You can the do two things:
1) view the SQL view and paste this view into a reply
2) view the datasheet view and copy all the records to paste into Excel.
Once in Excel sum the appropriate columns to see what totals you get.

You can type significant data into a reply.
 
Back
Top