Print The Text, Not The ID.....

  • Thread starter Thread starter Colin
  • Start date Start date
C

Colin

How to print text values instead of ID's?

I have 4 tables. The goal is to list bills of materials.
The first two are for a Parent product and Child product.
The 3rd table is a join table to allow for one Parent to have many
Children and for one Child to have many Parents.
The last table is for product units.


PARENT PRODUCT TABLE
Parent_ProductID (the parent part number) PRIMARY KEY
Parent_Product_Description (a text description)
Parent_Product_UnitID (a number pointing to the Units Table)


CHILD PRODUCT TABLE
Child_ProductID (the parent part number) PRIMARY KEY
Child_Product_Description (a text description)
Child_Product_UnitID (a number pointing to the Units Table)


PARENT CHILD JOIN TABLE
Parent_ProductID (the parent part number) PRIMARY KEY
Child_ProductID (the child part number) PRIMARY KEY
Child_Qty (the Qty of children that are contained in the parent)


UNITS TABLE
UnitID (the parent part number) PRIMARY KEY
Unit Description (a text description e.g. Feet, Pounds, Each…)


I would like to produce a report that looks like this (assuming the
parent has 3 children for this example):

Parent Part Number Parent Description Parent Unit

Child Part Number Child Description Child Qty Child Unit

Child Part Number Child Description Child Qty Child Unit

Child Part Number Child Description Child Qty Child Unit


Here's the problem:
Using the report wizard, I can only show the Units Description on one
side of the report (e.g. units show for Parent). How can I show the
appropriate Units Description for both the Parent and the Children?
This is common on many reports where I would like to show text (like
units) in more than one place. I have a units table to avoid duplicate
data.
Thanks!
 
Colin said:
How to print text values instead of ID's?

I have 4 tables. The goal is to list bills of materials.
The first two are for a Parent product and Child product.
The 3rd table is a join table to allow for one Parent to have many
Children and for one Child to have many Parents.
The last table is for product units.


PARENT PRODUCT TABLE
Parent_ProductID (the parent part number) PRIMARY KEY
Parent_Product_Description (a text description)
Parent_Product_UnitID (a number pointing to the Units Table)


CHILD PRODUCT TABLE
Child_ProductID (the parent part number) PRIMARY KEY
Child_Product_Description (a text description)
Child_Product_UnitID (a number pointing to the Units Table)


PARENT CHILD JOIN TABLE
Parent_ProductID (the parent part number) PRIMARY KEY
Child_ProductID (the child part number) PRIMARY KEY
Child_Qty (the Qty of children that are contained in the parent)


UNITS TABLE
UnitID (the parent part number) PRIMARY KEY
Unit Description (a text description e.g. Feet, Pounds, Each…)


I would like to produce a report that looks like this (assuming the
parent has 3 children for this example):

Parent Part Number Parent Description Parent Unit

Child Part Number Child Description Child Qty Child Unit

Child Part Number Child Description Child Qty Child Unit

Child Part Number Child Description Child Qty Child Unit


Here's the problem:
Using the report wizard, I can only show the Units Description on one
side of the report (e.g. units show for Parent). How can I show the
appropriate Units Description for both the Parent and the Children?
This is common on many reports where I would like to show text (like
units) in more than one place. I have a units table to avoid duplicate
data.
Click to expand...

In the report's record source query, add the Units table
twice. Make sure there is line connecting Parent to one
copy of Units and another line from Child to the other copy
of Units. Now you can drag the UnitDescription field from
each of the two Units tables to the field list. You should
then provide an alias name for these two fields (e.g.
ParentUnitDescr and ChildUnitDescr).
 
Back
Top