Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Table Joins 1

Status
Not open for further replies.

btamulis

IS-IT--Management
Sep 30, 2005
37
US
I am linking two tables (Crystal X) - Table A is a Bill of Material Table - it has multiple records for a distinct item. Table B is a Qty on hand table - it also has multiple records for a distinct item number.

Example:

Table A
PPN ITEM NUMBER
Widget Gadget
Test Gadget

Table B

ITEM NUMBER LOCATION QTY
Gadget NORTH 20
Gadget WAREHOUSE 10

The two tables are linked by Item Number from Table A to Table B

Current Results (in detail section of report)

Gadget NORTH 20
Gadget NORTH 20
Gadget WAREHOUSE 10
Gadget WAREHOUSE 10

Desired Results:
Gadget NORTH 20
Gadget WAREHOUSE 10

Since there are two records in Table A for Gadget - the report duplicates and prints two records for every individual record in Table B.

If only one record for item number was in Table A then report prints fine. If three records in Table A then 3 records for every individual record prints..

I have attempted all types of joins and enforcements - no luck......

Any assistance would be appreciated.





Bron Tamulis, CPIM
Great Plains Mfg Consultant
 
You could try going to database->select distinct records. Or try concatenating the three fields and then insert a group on the formula:

{tableB.ITEM NUMBER}+{tableB.LOCATION}+totext({tableB.QTY},0,"")

Then place the detail fields in the group header or footer instead of the groupname.

Of course, I'm assuming you need TableA for some part of the report, although you don't show it being used.

-LB
 
Worked like a champ - the select distinct records was grayed out and not an available option. The concenated formula and grouping suggestion works perfectly.

Thank you very much.

Bron Tamulis, CPIM
Great Plains Mfg Consultant
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top