Data showing incorrectly in report


T

Tim

I am having trouble with a report giving back incorrect data.

The database was created in Access 2003 (*.mdb). I am using Access 2007 –
report was created in Access 2007.

The report is looking at surveys that occurred at the same place and time
(comparing differences among observers). There are two sites where a set of
surveys occurred on two different days. Only one site shows up properly in
the report with the different dates the surveys occurred. The other site has
the two survey sets under one date.

At Ole Creek a set of surveys was done on 7/10/2008 and 10/9/2008; both
dates show up in the report with corresponding surveys. Triple Divide
surveys were done on 8/5/2008 and 9/24/2008; both are listed on the report as
occurring on 9/24/2008. The control source query shows both dates for Triple
Divide.

The report layout is:
Site Header: Currently minimized – Site Name use to be here but moved so
that site name shows up at each different date a survey occurred.

Date Header: Text boxes for - Site Name and Survey Date. (The location of
Site Name between the different headers seemed to have to no effect on how
Triple Divide is shown).

Survey Header: Sub-report listing the Observers and Labels for data fields.

Detail: not visible – has the Text boxes.

Survey Footer: Text boxes that sums detail’s data fields.

Site Footer: not visible – text boxes that sums data for each site.

Group, Sort, and Total:
Group - Name
Group - Date
Group - Survey

Does anyone know why the report is doing this? Am I missing something in
the formatting or some other setting?

Thank you in advance,
Tim
 
Ad

Advertisements

D

Duane Hookom

Maybe if you explained why you needed invisible text boxes in sections. These
shouldn't be necessary to the values displayed in the group sections.

Why would you not have the survey footer information in the survey subreport?

If you main report has the details of the surveys, why do you need a
subreport?

Do you really have fields named "Name" and "Date". These are reserved words
and can cause serious issues in your application.
 
T

Tim

Thanks Duane,

I'll hopefully explain the choices on the report more fully.

First with the names. No, I did not use Date and Name in the database. The
full names are Site_Name and Survey_Date (At times, I tend to be a lazy at
typing).

The Detail and Site_Name Footer sections were originally Shown on the report
but the users of the database decided that they were not helpful when viewing
the report (too much information). I am still getting the report formatted
to how the users would like the report to look. I just have not removed the
Site_Name Footer section yet (I don’t see how it would cause my problem, but
though I would include it in case it was causing some problem - a case of too
much information).

The purpose of the report is to look at differences between observers
conducting the same survey. They currently do not want to see the
nitty-gritty details - only the overall results of each observer involved in
a Paired Survey (two or more people involved in the same survey, each keeping
separate record of what goats they observed). The report is grouped by Site
Name then Survey Date, so that surveys that occurred together are grouped
together.

The Detail section has data on when a group of goats were observed during a
survey (a separate survey record for each person involved). The data in
Detail section is time a group was observed along with numbers of different
ages and sexes comprising of the group. This data is counted (number of
groups) and summed in the Survey Footer section.
Why would you not have the survey footer information in the survey subreport?

I’m not sure what you are asking. The subreport in the Survey_Header pulls
the names of the observers; the subreport only has a Detail section with the
Observer Name and Survey_Key. The Observer table is in a Many-to-Many
relationship with the Survey table (Observers (1:M) xref_Survey_Observers
(M:1) Survey). The Survey_Key field is present in both the subreport and
main report’s Survey Header (not visible - autonumber field), and is used as
the Parent-Child field between the main report and subreport.

With Site_Name sorted ascending, the Triple Divide site is listed last in
the report. Two sets of paired surveys occurred. One set of surveys
occurred on 8/5; two people involved; two survey records (Survey_Keys 209 and
210). Another survey set occurred on 9/24; two people involved; two survey
records (Survey_Keys 207 and 208). For Triple Divide, I get all four surveys
back in the report, but they are all listed as having occurred on 9/24. The
control source query shows the correct dates for the respective surveys. I
am unable to figure out why the report is listed these under the same date.

Does this answer your questions,
Thanks for your input,
Tim
 
D

Duane Hookom

I assume the subreport display is the issue. Do you have text boxes in the
same report section as the subreport that are bound to the link master fields?
 
T

Tim

Yes, there two bound text box in the section with the subreport. Both fields
pull information from the Survey table. Survey_Key (Primary key for the
table) and used as with subreport. The other field shows information about
the survey.

I deleted that one field from the report and it did not seem to change how
Triple Divide is showing in the report.

I also just deleted the subreport. Unfortunately, it did not change how
Triple Divide is being displayed.

Before deleting and after deleting, I have a site that is similar with
surveys occurring on different dates. In all instances, it is being spilt by
date they way I'm trying to get Triple Divide. I just wish I knew why it
shows one correctly and the other un-correctly.

Would having three different header sections that pull information from the
same table cause the problem? Top most section Site_Name, then Survey_Date,
then Survey_Key. I don't see why that would be the case, but I'm missing
something.

Thanks,
t
 
D

Duane Hookom

Are you using the date field as part of the link master/child? If so, is this
field used to set relationships? Or, is the another unique field you could
use other than the date?
 
Ad

Advertisements

T

Tim

No, the date is not in a relationship or the master/child field. The date
field is used to show what date the surveys occurred and to group surveys
that occurred on that date together (along with the grouping by site).

With the main/sub-reports, the only field acting as the master/child is the
Survey_Key, which is present in the subreport and Survey_Key header section
of the main report (both are NOT visible because it has no meaning to the
survey data besides relating the tables together).

The subreport (and its source query) only has two fields present: Observer
(a concatenation of First Name and Last Name - from the observers table based
on the Observer Key in the xref table) and Survey_Key (from the xref table).

I almost feel as if I hit some bizarre format command without remembering or
knowing. I’ll look through each section’s settings to see if I unknowingly
set some property to something it shouldn’t on Monday. I have not added any
VBA to the report, so I know it’s not my bad programming. ;)

Thanks for slugging through with me,
Tim
 
D

Duane Hookom

I'm at a loss. I am aware that some chart controls which aren't that much
different from subreports, need to be requeried in the On Format or On Print
event of the section of the report containing the subreport/chart.
 
T

Tim

Thanks for the help Duane,

I cannot see any unusual formatting settings in the report.

I built a new report without the subreport and the problem show up when I
put the Survey Date field in a group/header above the Survey Key field. If
the date is in the same group as survey key or below, the Triple Divide site
show up correctly with the correct date for each survey. If the date is in a
group/header above the Survey Key (whether or not the group is based on the
date field), the Triple Divide site is showing only one date instead of two.
The other site that also has two dates does not change; it always shows the
two dates. I guess it is some bug with the Group, Sort, & Total settings and
Triple Divide being the last set of records.

I welcome any other thoughts you have on this.
Tim
 
Ad

Advertisements

D

Duane Hookom

Sorry for the late reply. It seems the "Notify me of replies" from the web
interface of these news groups isn't notifying me.

I am having trouble picturing your issue since I can't analyze your report
without actually working with it. You could contact me off-line to request a
free private consulation.

duanehookom AT gmail DOT com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top