Report length

[racdata]

Can anyone suggest a solution on this?

I made a report like this example

Part List Situated Supply Labour Strip/Assemble Spray Total
XXXXX ZZZZ 20.99 10.00 15.85 5.00 51.00
SSSSSS qqqq 45.56 12.00 21.33 10.50 89.00
________________ 0.00 0.00 0.00 0.00 0.00
________________ 0.00 0.00 0.00 0.00 0.00
________________ 0.00 0.00 0.00 0.00 0.00
________________ 0.00 0.00 0.00 0.00 0.00
________________ 0.00 0.00 0.00 0.00 0.00
Total 77.55 22.00 37.18 15.50 140.00

When doing a quote like above is there a way to shorten the report to the last entry. Like can grow / can shrink

If I have a quote of 5 lines I only wat to see the 5 lines or if the quote is 25 lines I want to see only the 25 entries. At this moment I see the 2 entries and the other lines all the 0.00 I set this report up to take 30 entries this mean I see all the rows where there is no entries

Part List Situated Supply Labour Strip/Assemble Spray Total
XXXXX ZZZZ 20.99 10.00 15.85 5.00 51.00
SSSSSS qqqq 45.56 12.00 21.33 10.50 89.00
Total 77.55 22.00 37.18 15.50 140.00
Thanx

 

[WayneRyan]

rac,

It's hard to help without the details.

Where does your report get it's data from? (I'd guess a table, not query)

Does the table have the data with the 0's? (I'd guess yes)

Can you attach the DB? (Compact/Repair, then ZIP)

Wayne

 

[racdata]

The Db is too large to post 900Kb after I have zip it

The report came from a querry
and yes the table do have all the 0 in it because the format is set to currency

I attched a word doc with the report and some screen prints

If it is OK with you I will send post my E-mail address and then you can send me an e-mail address where I can send you the Db (I will keep it a secret)

I have delete all the table's,Querries, and reports I do not have problems with, zip it and it is still 166.1kb
Thanks for your help

 

[Pat Hartman]

Add criteria to the query that eliminates the rows with only zeros.

 

[racdata]

I am "lost" on this one

Add criteria to the query that eliminates the rows with only zeros.

Sorry Pat but I do not know what and where to enter the "criteria"
I want to eliminate the the 0.00 on the report.
Display only if value is >0
Thanx

 

[Pat Hartman]

For each field that you want to test place > 0 in the criteria cell. If there are multiple fields where you need to apply this criteria, put each in a separate row. So the first field will have <> 0 in the first criteria row, the second will have <> 0 in the second criteria row, etc. If you run out of rows, you'll need to switch to SQL view to continue adding criteria. The point of putting each condition on a separate row is to make the query builder connect them with the OR operand. So effectively you are asking Jet to return all rows where fld1 <> 0 or fld2 <> 0 or fld3 <> 0 or fld4 <> 0, etc The condition boils down to "return any row where at least one field has a value not equal to 0".

 

[racdata]

For each field that you want to test place > 0 in the criteria cell. If there are multiple fields where you need to apply this criteria, put each in a separate row. So the first field will have <> 0 in the first criteria row, the second will have <> 0 in the second criteria row, etc. If you run out of rows, you'll need to switch to SQL view to continue adding criteria. The point of putting each condition on a separate row is to make the query builder connect them with the OR operand. So effectively you are asking Jet to return all rows where fld1 <> 0 or fld2 <> 0 or fld3 <> 0 or fld4 <> 0, etc The condition boils down to "return any row where at least one field has a value not equal to 0".

I did try your suggestion - But still get the error message - Query too complex

 

[Pat Hartman]

Post the query if you need help finding the syntax error.

 

[racdata]

For all the EXPERTS

Post the query if you need help finding the syntax error.

Pat
See the attached zip file
I thank you in advance for your help

Thanx a mil

 

[Pat Hartman]

I have some good news and some bad news. The good news is that your app will be easier build and more flexible for the future. The bad news is that you need to go back to ground zero. You have created 6, 1-1 relationships. The relationships are actuall 1-many although I'm not entirely sure if the tables contain data that relate to each other or to the client table. In any event, all your related tables need to have all the repeating groups removed leaving you with a single instance of whatever. Then you need to add appropriate primary keys to allow multiple instances per ID. You will also need to add the correct foreign key if it isn't ClientID. Your design sets the number of related fields permanently at 30. The proper design allows 0 - infinity.

You spent a lot of time designing that report but all you need is ONE row in the detail section. The report program will create as many detail lines as necessary to accomodate the data. So if there are 5 detail rows, 5 rows will be printed.

BTW, ID is a poor choice as the name of a table's primary key. In the Client table an appropriate name would be ClientID. Then in the related tables, you would add ClientID in addition to their own primary key.

Access cannot calculate totals in a page footer so if you want footers on every page, you'll have to do the calculations manually. If you can live with having a report footer at the end of the report, Access can do all the work. Formatting may still be an issue if you want the report footer to print a the bottom of the last page. Page footers by default print at the bottom of a page but report footers print immediately after the last detail line.

You will probably need one of these articles to help you get the footer properly placed.

ACC2000: How to Print a Group Footer at a Specific Location
ACC2000: How to Print a Constant Number of Lines Per Group
ACC2000: How to Create Page Totals on a Report

 

[racdata]

Thank you Pat

Pat I thank you for your help

I will look into your suggestions and do so.

The report is always a one-page report and that’s not necessary to total in the footer of the report. I know how to do the calculations in the footer of a report.

Do I understand correctly I can delete all the other rows except for the first one and then if Access needs to place more rows it will be done by itself.

I surely will try it but first I am out of town for a few days. I will let you know what the results are.

Thanx
Arrie Schoeman

 

[Pat Hartman]

then if Access needs to place more rows it will be done by itself

- yes. That is the way the report detail section works. The ENTIRE detail section is printed for EACH input record. Therefore, if you have 30 input records, the detail section will contain 30 rows and Access will take care of reading and writing all by itself.