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

[erick-flores]

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

 

[erick-flores]

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)?

 

[Marshall Barton]

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.

 

[erick-flores]

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

 

[Marshall Barton]

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.

 

[erick-flores]

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.

 

[erick-flores]

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?

 

[Marshall Barton]

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.

 

[Marshall Barton]

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.


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.