A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How can I make a group not to show/display in a report if no data is under that group?



 
 
Thread Tools Display Modes
  #21  
Old August 1st, 2006, 11:04 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default How can I make a group not to show/display in a report if no data is under that group?

erick-flores wrote:
Well I guess I am going to need some coding for my report. What I am
trying to do is a replica of a FoxPro application. This application
produces the same report that I am trying to create. This is what the
groups headers looks like:
^ Page Header
Tag Num
Business Team
^ Group Header 1: cmp_id===================================

Description
^ Group Header 2: stg_id====================================

Description
Components Material
Part#
^ Groups Header 3: cyl_id===================================

Under this group are all the different components that the cylinders
can have. They are all one on top of another, like you can see one line
of data, but if you start moving the texbox you can see that there are
5 texbox one of top of another. So I guess that there is some code
behind this group so I will print only if there is data behind a
particular component
^ Group Header 4: bom.type1 + STR(bom.id1)

Same thing for these group, but this group will show the components
under the cylinder components.
^ Group Header 5: bom.type2 + STR(bom.id2)

Here are the last components under the components from the cylinder
components
^ Detail
^ Group Footer 5: bom.type2 + STR (bom.id2)
^ Group Footer 4: bom.type1 + STR (bom.id1)
^ Group Footer 3: cyl_id
^ Group Footer 2: stg_id
^ Group Footer 1: cmp_id

^ Pafe Footer

I was trying to imitate this report in Access. But now that I know
Access dont more than 10 groups, I guess I need to put some code behind
my Groups (4,5 and Detail). Also if you notice all the arrows for the
groups are pointing up. In Access is the other way, the arrows for the
groups point down.

Do you have any ideas of how to create some code for my groups in
Access? So I can see my report the same as I see it in FoxPro? or
perhaps another complete different way, that will produce the
same/similar results?



It's starting to sound like you data is not normalized.
There should be a single field in the record source
table/query that contains the component type (e.g. Valve,
Gaskets or Piston) and another field for subtype (e.g.
Gasket, Rod, Nuts, etc). Maybe you even have a subsubtype
for rings? Anyway, these 3 or 4 fields are what should be
specified in Sorting and grouping. This would only require
3 or 4 groups, not more than 10.

If you have the valves in one set of fields, gaskets in
another set of field and Pistons in yet a third set of
fields, you have a mess that is better dealt with in a
spreadsheet where you can move things around manually till
it looks the way you want.

Anyway, assuming I'm wrong about how your data tables are
designed. You can make a single section display in a
different way depending on the type of the group by using
code in the Format event procedure to make some controls
visible and otheres invisible. It's pretty straightforward
if a little tedious.

--
Marsh
MVP [MS Access]
  #22  
Old August 2nd, 2006, 03:32 PM posted to microsoft.public.access.reports
erick-flores
external usenet poster
 
Posts: 85
Default How can I make a group not to show/display in a report if no data is under that group?

It's starting to sound like you data is not normalized.
There should be a single field in the record source
table/query that contains the component type (e.g. Valve,
Gaskets or Piston) and another field for subtype (e.g.
Gasket, Rod, Nuts, etc). Maybe you even have a subsubtype
for rings? Anyway, these 3 or 4 fields are what should be
specified in Sorting and grouping. This would only require
3 or 4 groups, not more than 10.


Hello
You are complete right. That is the way I have my data. Ok, so I need
to add a single field to all of my table (after Cylinders), specifying
the type, right? So I will add a field for my tables:

Type1(component-type) Type2(subtype) Detail (subsubtype)
Comp_valve_asb Comp_valve_parts
Packing_case_asb Cups
Packing_case_parts
packing_set_det Gaskets
Piston_rod_asb Compressor_rods
Nuts
Pistons
Piston_rings

Rider Rings
unloaders

Is that what I need to do? add a field that says type1 under my
Comp_valve_asb, type2 under Comp_valve_parts and then the detail group
will be left for the subsubtypes.

Now, once I add those fields to my tables (if thats the right way to do
it) how would I use this types in my reports to group by type? How do I
create the query for my report? and what about some code (if needed)
behind the type's groups?

Again, thank you

  #23  
Old August 2nd, 2006, 05:35 PM posted to microsoft.public.access.reports
erick-flores
external usenet poster
 
Posts: 85
Default How can I make a group not to show/display in a report if no data is under that group?

What would be the expression for my Group Headers? because I add a
field for each table with the default value = "comp_valve_asb" and did
the same for the other tables. But now in the report how do I match all
the types from my table to a single group call type1. And also I need
the ID in my groups headers.

I am just complete lost in here. I understand what you told me, I can
see the big pictures grouping by type1 and type2. But I dont know how
to make it work. Can you please guide me here through every step, as
far as, what new fields to add? where to add them? do I need to create
new tables/queries? do I need to add new relationships to my tables?
what would be the expression for the Groups inside my report (for the
type1 and type2)?

  #24  
Old August 2nd, 2006, 11:46 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default How can I make a group not to show/display in a report if no data is under that group?

erick-flores wrote:

What would be the expression for my Group Headers? because I add a
field for each table with the default value = "comp_valve_asb" and did
the same for the other tables. But now in the report how do I match all
the types from my table to a single group call type1. And also I need
the ID in my groups headers.

I am just complete lost in here. I understand what you told me, I can
see the big pictures grouping by type1 and type2. But I dont know how
to make it work. Can you please guide me here through every step, as
far as, what new fields to add? where to add them? do I need to create
new tables/queries? do I need to add new relationships to my tables?
what would be the expression for the Groups inside my report (for the
type1 and type2)?



These newsgroups do not lend themselves to tutorials or full
blown designs. Specific questions about a detailed problem
are the norm, but questions about how to learn about a
general topic are also appropriate.

In your situation, you could search the internet on Database
Normalization, but you would get thousands of hits. While
that may be needed eventually, you can start with this
article http://support.microsoft.com/kb/283878/en-us and
search for specific aspects that you want to explorefurther.

Along those lines, I sometimes summarize most of that with a
vague table design guideline: When editing a value (e.g.
customer name) in your database, you should only have to
edit one field in one record in one table. Also, when
adding new data (e.g. a new kind of part), you should only
have to add one record to one table (you should not have to
add another field to a table's design).

I don't think I understand your data well enough to design
your tables. They may be vaguely like:

Products table:
ProductKey AutoNumber (Primary Key)
Description Text

Parts table:
PartKey AutoNumber (Primary Key)
Description Text
SpecCode Text
. . .

ProdParts table:
ProdPartKey AutoNumber (Primary Key)
ProductKey Long (Foreign Key)
PartKey Long (Foreign Key)
SubPartOf Long (Foreign Key) to parent part in same table

With the tables properly designed, the next step is to
create a query to use as the report's record source. The
query would join the tables appropriately so the report gets
its data in a nice organized manner. How that's done
depends on how many levels of sub(sub...)parts you have.
For a small number of levels you can just use outer joins to
multiple instances (one join for each level) of the
ProdParts table. If you might have an arbitrary number of
levels, then you have a BOM (bill of materials) problem that
requires a more sophisticated approach (that will require a
fair amount of research on your part).

Specific questions about normalization and table design
should be posted to the tables design newsgroup.

--
Marsh
MVP [MS Access]
  #25  
Old August 3rd, 2006, 01:12 AM posted to microsoft.public.access.reports
erick-flores
external usenet poster
 
Posts: 85
Default How can I make a group not to show/display in a report if no data is under that group?

I do understand what normalization is. As far as I know my tables are
well design. There are no repeated fields in any of my tables. Except
for those like "description" but in this case is a description for each
table. On the other hand, I guess I will need to do some more research
of how to create those groups. Thanks for your time

  #26  
Old August 3rd, 2006, 05:48 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default How can I make a group not to show/display in a report if no data is under that group?

erick-flores wrote:

I do understand what normalization is. As far as I know my tables are
well design. There are no repeated fields in any of my tables. Except
for those like "description" but in this case is a description for each
table. On the other hand, I guess I will need to do some more research
of how to create those groups.


You never have described your tables, but I have the
impression that you have one table with different fields for
Compressor, Stages and Cylinders. These would be repeated
fields as they are all "parts" of one kind or another.
Remember my vague guideline - how many "things" would have
to be changed if you added a high volume, low pressure
compressor that used an impeller? It certainly should not
require you to change the design of any table.

The first exercise in table design is to determine the
independent "entities". Each entity is then represented by
a table and its fields are the "attributes" of the entity.
In my view Cylinders are not an entity nor are they
independent of Compressor and Stages. Therefore, you need
to define an entity such as "parts" to hold that information
along with any other "parts" such as rings, etc. Sometimes
this is neither easy nor intuitive, but it is necessary to
avoid all kinds of problems in every other aspect of your
database.

OTOH, maybe that's what you aready have and it's the
report's record source query that's causing all the
reporting problems.

--
Marsh
MVP [MS Access]
  #27  
Old August 3rd, 2006, 03:35 PM posted to microsoft.public.access.reports
erick-flores
external usenet poster
 
Posts: 85
Default How can I make a group not to show/display in a report if no data is under that group?

You never have described your tables, but I have the
impression that you have one table with different fields for
Compressor, Stages and Cylinders. These would be repeated
fields as they are all "parts" of one kind or another.
Remember my vague guideline - how many "things" would have
to be changed if you added a high volume, low pressure
compressor that used an impeller? It certainly should not
require you to change the design of any table.


My tables are design as follow:
locations: Table
Field Name Data Type
loc_id(pk) AutoNumber
description Text
loc_info Memo

compressors: Table
cmp_id(pk) AutoNumber
loc_fk Number (relationship one-to-many)
tag_num Text
Notes Memo

stages: Table
stg_id AutoNumber
cmp_fk Number
description text
Notes Memo

cylinders: Table
cyl_id AutoNumber
stg_fk Number
serial_num Text
description Text
Notes Memo

comp_valve_asb: Table
cva_id AutoNumber
cyl_fk Number
description Text
Notes Memo

comp_valve_parts: Table
cvp_id AutoNumber
cva_fk Number
description Text

gaskets: Table
gsk_id AutoNumber
cyl_fk Number
description Text
Notes Memo

piston_rod_asb: Table
pra_id AutoNumber
cyl_fk Number
description text

pistons: Table
pst_id AutoNumber
pra_fk Number
description Text
Notes Memo

rider_rings: Table
prr_id AutoNumber
pst_fk Number
description Text
Notes Memo

piston_rings: Table
ppr_id AutoNumber
pst_fk Number
description Text

Note: All the "_id" are the Primary Key for each table, and the "_fk"
are the Foreign Key for each table. All the relationships are
one-to-many. I have 18 tables in total, here are only 11 tables, but
they show all the possibilities that could happend.

OTOH, maybe that's what you aready have and it's the
report's record source query that's causing all the
reporting problems.


I do think that my problem is in the report;s record source query.

  #28  
Old August 3rd, 2006, 07:33 PM posted to microsoft.public.access.reports
erick-flores
external usenet poster
 
Posts: 85
Default How can I make a group not to show/display in a report if no data is under that group?


By the way, if you know a good (really good) book that explain
technical stuff about MS Access (like doing this kind of groups in
reports, and some others technical/difficult/hard tasks) can you please
let me know which ones are out there?

  #29  
Old August 6th, 2006, 04:58 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default How can I make a group not to show/display in a report if no data is under that group?

erick-flores wrote:


By the way, if you know a good (really good) book that explain
technical stuff about MS Access (like doing this kind of groups in
reports, and some others technical/difficult/hard tasks) can you please
let me know which ones are out there?



The reference that I rely on is The Access xx Developers
Handbook (where xx is the version of Access) by Getz,
Litwin, ... from SYBEX.

--
Marsh
MVP [MS Access]
  #30  
Old August 6th, 2006, 05:54 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default How can I make a group not to show/display in a report if no data is under that group?

erick-flores wrote:

You never have described your tables, but I have the
impression that you have one table with different fields for
Compressor, Stages and Cylinders. These would be repeated
fields as they are all "parts" of one kind or another.
Remember my vague guideline - how many "things" would have
to be changed if you added a high volume, low pressure
compressor that used an impeller? It certainly should not
require you to change the design of any table.


My tables are design as follow:
locations: Table
Field Name Data Type
loc_id(pk) AutoNumber
description Text
loc_info Memo

compressors: Table
cmp_id(pk) AutoNumber
loc_fk Number (relationship one-to-many)
tag_num Text
Notes Memo

stages: Table
stg_id AutoNumber
cmp_fk Number
description text
Notes Memo

cylinders: Table
cyl_id AutoNumber
stg_fk Number
serial_num Text
description Text
Notes Memo

comp_valve_asb: Table
cva_id AutoNumber
cyl_fk Number
description Text
Notes Memo

comp_valve_parts: Table
cvp_id AutoNumber
cva_fk Number
description Text

gaskets: Table
gsk_id AutoNumber
cyl_fk Number
description Text
Notes Memo

piston_rod_asb: Table
pra_id AutoNumber
cyl_fk Number
description text

pistons: Table
pst_id AutoNumber
pra_fk Number
description Text
Notes Memo

rider_rings: Table
prr_id AutoNumber
pst_fk Number
description Text
Notes Memo

piston_rings: Table
ppr_id AutoNumber
pst_fk Number
description Text

Note: All the "_id" are the Primary Key for each table, and the "_fk"
are the Foreign Key for each table. All the relationships are
one-to-many. I have 18 tables in total, here are only 11 tables, but
they show all the possibilities that could happend.

OTOH, maybe that's what you aready have and it's the
report's record source query that's causing all the
reporting problems.


I do think that my problem is in the report;s record source query.



While it might be possible to normalize those tables on the
fly by using a UNION query, you would be far better off
changing it to something more like my earlier general idea.

Splitting all that info into separate tables sure looks like
the culprit to me. Using the table name to identify the
component is the troublemaker here. Instead you should have
one table with a field that identifies the component. Since
your tables are so similar, it would be easy to do by just
leaving the Description, Notes and/or SerialNum fields Null
for the components that don't use the field. You may want
to get a second opinion from someone in the Table Design
newsgroup.

In my simple example tables, please take note of the
"junction" table that contains the foreign keys to connect
the product to its components and another foreign key to the
component's parent component in the same table.

This structure may not be intuitive to you and you will have
to do some homework to understand how to deal with the
hierarchical nature of your data, but it does allow you to
construct the query needed to produce your report.

Without this, I don't see how you can organize your data to
get the report you want. Don't get me wrong here, the
problem you are working on is not a simple problem. It
requires a sophisticated table structure, at least one
complex query before you can use all that to create a fairly
straightforward report.

--
Marsh
MVP [MS Access]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.