Report resulting repetitive pages for every record.

U

unclefink

Registered User.
Local time
Today, 05:59
Joined
May 7, 2012
Messages
184
So i'm sure this is an easy quick fix but its been a while since i've created a report that I cant recall how I did it previously.

I have a report that is running off of an embedded query pulling data from a table. The fields i'm adding to the report are unbound sum if statements based on the table data, that part is working and showing the data i'm expecting. I've got 1 text box in my report however when I run the report, the information comes back for every record in that particular table, in this case 15,000 times showing the same information that many times also resulting a report more than 500 pages long when it should only be 1 page.

Can someone point me the way, i'm sure this is not a difficult issue or probably solved in a report setting I just cant remember for the life of me what setting.

Please help. This should be my last issue before I can finish this project.
 
Sounds like you've based the report on the table rather than a query that returns a single record. I'd fix that, but you can probably hide the detail section too (less efficient).
 
It sounds like its in the wrong section, specficially the Details section. Everything in there will show for every record. You haven't told us the sections of your reports, so I suggest you move it to a Grouping level and keep moving it up a level until it shows the correct amount of times.
 
I do have it in the details section. I guess i need to start there.
 
I just updated the report to come from a query and also updated the query to only show me data for the last 3 years as that is all I need for this particular report.

Doing so, I now have 1,961 pages for 8k records.

As far as the report goes, where is the appropriate place to put the data rather than details as i've never put the data anywhere other than details.

When I put the information on both the header and footer, I get an error on each of my calculated fields.
 
If all you want is the sum, you could either have the report unbound and use a DSum() in a textbox, or base it on a totals query that returned a single record.
 
I'm trying to do this without creating a ton of queries to get my ending desired results.

I'll give the dsum function a shot but now i'm going to ask for assistance with translating to this function.

I have a table called MainDataTbl
In that table there is a field called casenumber
Case numbers start with a specific site designater

DSum ( expression, domain, [criteria] )

Here is my attempt as I see it to count the number of cases that start with BP
Dsum(casenumber,maindatatbl,casenumber like "BP*")

Would this be correct for the purpose?
 
No, try

Dsum("casenumber","maindatatbl","casenumber like 'BP*'")
 
I added the dsum syntax you suggested and am getting an error on run.

Pictures attached.
 

Attachments

  • erroronsyntax.PNG
    erroronsyntax.PNG
    936 bytes · Views: 139
  • report syntax.PNG
    report syntax.PNG
    12.2 KB · Views: 145
Just noticed you're summing the same field as in the criteria. I assume it should be different. Is case number a text field?
 
case number is a text field. Our case numbers start with a site designation followed by a sequential number.

My initial intent of doing this was to use a little as queries as possible. When I built this first report I was a beginner on building access db's and now i'm updating the same report. I attempted my initial approach rather than doing it on the report itself but put the sumif syntax in a query. Looks like doing it that way is going to be easier and on one query. I laugh at myself now for creating the mess I did as a beginner! :):banghead:

Thank you all for your assistance. It goes a long way and helps develope my knowledge base.
 
Last edited:
Did you change the first field to the field you want to sum up?
 
The field in red should be the one you want to sum up, not the one you're filtering on:

Dsum("casenumber","maindatatbl","casenumber like 'BP*'")
 
pbaldy said:
The field in red should be the one you want to sum up, not the one you're filtering on:

Dsum("casenumber","maindatatbl","casenumber like 'BP*'")
Click to expand...

How should I put that in syntax if I want to count the number of records from that field while applying the filter.
 
You can use DCount() instead of DSum() if you want a count.
 
Thank you very much everyone, I have solved the issue based on your feedback. I appreciate your help
 
Glad you got it sorted.
 
You must log in or register to reply here.

Similar threads

C
pdf shrinks report
Replies
11
Views
510
Cotswold
Cotswold
A
Solved Email Report
Replies
15
Views
536
Pat Hartman
P
F
Solved Is there a way to add multiple detail tabs to one report?
Replies
6
Views
462
Pat Hartman
P
I
Solved Section Width Greater than Page Width - All usual remedies tried
Replies
10
Views
680
mike60smart
mike60smart
D
How to view a monthly reports on the 1st of every month
Replies
5
Views
376
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom