Report showing same data MULTIPLE times :(

[Heather]

so, this is my second post in a week...

i wrote last week regarding a summation issue on my reports. now that i
have that cleared up, we'd like to get our data to the report in a different
manner.

to this point, i've manually entered my data into a form, and then used that
form's table to build a report. what i'd LIKE to do is run a make-table
query, get my data from a pre-existing ODBC (it's our Accounting software's
data), keep it updated with an append-table query (run once a week), and
build the report based on this new table.

here's my problem today...i have the make-table query set with the
parameters i'd like to see (ex: data after 1/1/2008), and the table itself
looks great. i also have a report set up with the Project Number (it's in
the main report header), and a subreport with the data for each project.
when i was working on the "manual-entry" version of this report, i would get
a report page for each project (that's what i want to see). however, this
time, i'm getting 16 pages of the first project, 22 pages for the second,
etc. i looked back at my table...there are 16 rows of data for project 1, 22
rows of data for project 2, etc.

what is wrong? any help is appreciated!
thanks,
H

 

[Armen Stein]

so, this is my second post in a week...

i wrote last week regarding a summation issue on my reports. now that i
have that cleared up, we'd like to get our data to the report in a different
manner.

to this point, i've manually entered my data into a form, and then used that
form's table to build a report. what i'd LIKE to do is run a make-table
query, get my data from a pre-existing ODBC (it's our Accounting software's
data), keep it updated with an append-table query (run once a week), and
build the report based on this new table.

here's my problem today...i have the make-table query set with the
parameters i'd like to see (ex: data after 1/1/2008), and the table itself
looks great. i also have a report set up with the Project Number (it's in
the main report header), and a subreport with the data for each project.
when i was working on the "manual-entry" version of this report, i would get
a report page for each project (that's what i want to see). however, this
time, i'm getting 16 pages of the first project, 22 pages for the second,
etc. i looked back at my table...there are 16 rows of data for project 1, 22
rows of data for project 2, etc.

what is wrong? any help is appreciated!
thanks,
H

If you are loading that table with a MakeTable query, then it is being
completely rebuilt with new records each time. So the problem would
seem to be in the MakeTable query. Are you joining to any other
tables in that query? A join to a table with multiple matching values
will result in duplicate records. I would try temporarily changing
your MakeTable query to a Select query and see what you get. I bet
you're joining to some kind of project detail table that has 16 rows
for Project 1, etc.

If instead it turns out that you are actually getting duplicate data
from your accounting system, you'll need to use a Totals query to
eliminate the duplicates.

Side Note: Most developers dislike MakeTable queries, for a few
reasons but mainly because Access will choose the field types for you,
sometimes resulting in a table structure you didn't expect. A more
reliable way is to have just one table that drives your report, then
use a "Delete * From MyTable" query and then an Append query to reload
it whenever you like.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com