Report resulting repetitive pages for every record. (1 Viewer)

unclefink

Registered User.
Local time
Today, 10:10
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:10
Joined
Aug 30, 2003
Messages
36,118
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).
 

plog

Banishment Pending
Local time
Today, 12:10
Joined
May 11, 2011
Messages
11,611
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.
 

unclefink

Registered User.
Local time
Today, 10:10
Joined
May 7, 2012
Messages
184
I do have it in the details section. I guess i need to start there.
 

unclefink

Registered User.
Local time
Today, 10:10
Joined
May 7, 2012
Messages
184
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:10
Joined
Aug 30, 2003
Messages
36,118
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.
 

unclefink

Registered User.
Local time
Today, 10:10
Joined
May 7, 2012
Messages
184
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?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:10
Joined
Aug 30, 2003
Messages
36,118
No, try

Dsum("casenumber","maindatatbl","casenumber like 'BP*'")
 

unclefink

Registered User.
Local time
Today, 10:10
Joined
May 7, 2012
Messages
184
I added the dsum syntax you suggested and am getting an error on run.

Pictures attached.
 

Attachments

  • erroronsyntax.PNG
    erroronsyntax.PNG
    936 bytes · Views: 88
  • report syntax.PNG
    report syntax.PNG
    12.2 KB · Views: 91

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:10
Joined
Aug 30, 2003
Messages
36,118
Just noticed you're summing the same field as in the criteria. I assume it should be different. Is case number a text field?
 

unclefink

Registered User.
Local time
Today, 10:10
Joined
May 7, 2012
Messages
184
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:

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:10
Joined
Aug 30, 2003
Messages
36,118
Did you change the first field to the field you want to sum up?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:10
Joined
Aug 30, 2003
Messages
36,118
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*'")
 

unclefink

Registered User.
Local time
Today, 10:10
Joined
May 7, 2012
Messages
184
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*'")

How should I put that in syntax if I want to count the number of records from that field while applying the filter.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:10
Joined
Aug 30, 2003
Messages
36,118
You can use DCount() instead of DSum() if you want a count.
 

unclefink

Registered User.
Local time
Today, 10:10
Joined
May 7, 2012
Messages
184
Thank you very much everyone, I have solved the issue based on your feedback. I appreciate your help
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:10
Joined
Aug 30, 2003
Messages
36,118
Glad you got it sorted.
 

Users who are viewing this thread

Top Bottom