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]