Reports

[Tom]

Hi all,
I want to have report of the products by category. (4 categories)
I have 1 report based on a query(for category #1) and in that report there
are 3 sub reports for each of the other categories.
Each report has its own query.
I have Nodata event that pop massage in case of no data.
The problem is when the first query return "no data", the report do not open
(as it should) but I do have dataon the pther sub report

I would like only when there is no data in all queries to have a massage "no
data available".


I hope I'm clear,

TIA,

Tom

 

[Cheese_whiz]

Hi Tom,

Are your categories all from the same table? If they are, you don't need
all those subforms. You would just 'group' your report on Categories, and
then the report would make a new section for each category with the name of
the category at the top of the section and all the records that fit under
that category below it.

As far as the form not opening, I'm not sure, but I suspect if you've used a
bunch of subreports and your main report doesn't have any records, then your
subreports might be chocking because they might be linked (child/master) to
the main report which has no data to match.

Hope there's something of value here for you,
CW

 

[Tom Wickerath MDB]

if you're using reports with MS Access, Microsoft reccomends that you use
Access Data Projects

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom]

Thanks,
It was necessary to create different reports because I have to run sum to
each category and then I had to manipulate each sum field.
I believe that the sub reports are being chocked.
Any ideas?

Thanks

 

[Cheese_whiz]

Hi Tom,

I still don't see the problem with grouping on categories. When you group,
you get a group header where you can put a control that gives the category.
You can also add column headings in the group header for each column of data
to be displayed in your records.

Then, below the place where the category records appear for a particular
category, you get a footer where you can place things like calculated fields
based on the records IN THAT CATEGORY.

THEN, you can add a 'report footer' that will show up at the end of your
report (below the last category's footer), where you can basically put what
you want...including calculated fields that will count or sum (or presumably
about anything else you need) ALL the records in the report (read: all
records from all categories).

As for a solution to the subreport thing, I'm not really sure. When you
link a subreport to a report then there shouldn't really be any orphaned
subreport records (if that's the proper term when dealing with reports versus
forms). There probably is a way to deal with, or maybe someone with more
confidence than I have will confirm it just can't be, or shouldn't be, done.

CW

 

[Tom]

Thanks Cheese,
The problem is that the sum of group A must be devisible by 3, the sum of
group B must be devisible by 4, group C by 6 and they all need to be on one
report.

Thanks,

Tom

 

[Cheese_whiz]

Hi Tom,

It might be possible for you to use a DCount in your reports footer to
reproduce the sum of the records in each section of your report. You would
add one textbox to to the report footer for each section, and set the control
source for the first one (the one with category type 'open') to:

=DSum("ID", "QueryFiles", "Category = 'Open'")

You would do that for each category type.

ID in this example is the primary key from the main table in the query. If
your query produces more than one record based on each ID (read: if IDs are
duplicated in the query results), then it may effect your outcomes.

What this does is get you the totals in the report footer. Of course, you
want to divide each of these totals by some number to get the actual number
you are looking for (as you explained), so you may need to wrap that entire
DSUM calculation in parens and then add "/3" (without the quotes) to the
control source if, for example, you want to divide the 'open' category count
by three.

That make any sense?

CW

 

[Cheese_whiz]

PS: In the example above, I used "QueryFiles" as the query that you based
your report on. You would, of course, have to change that to the actual
query or table.

CW

 

[Tom]

Thanks.
I tried the Dsum function but it didnt work. I got #Error in the total
field.
Tom