Totaling with a cuple of 1 to Many relationships
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20561
Printed Date: 02 May 2025 at 2:20pm
Topic: Totaling with a cuple of 1 to Many relationships
Posted By: DoubleD
Subject: Totaling with a cuple of 1 to Many relationships
Date Posted: 19 Mar 2014 at 5:10am
|
I need to write a report that includes a one to many relationship on two tables I started simple access the table with one (inventory) I link it to a table with a 1 to many relationship (InvMovement) I group on and to sum on qty move - Life is good Then I add the warehouse table so I can figure out how many items I have on hand. Yup its a one to many so now I have 2 one to many relationships here (1 to many to many) Needless to say my totals are all whacked out. I can get one side or the other to total OK but not both. Given the tables INV 1 2 INV Movement 1 10 1 20 2 15 INV Warehouse QTY 1 A 3 1 B 5 1 C 2 2 A 10 2 B 10 In Crystal when I link them all together I get a complex that looks like this Inv Move WH QTY 1 10 A 3 1 10 B 5 1 10 C 2 1 20 A 3 1 20 B 5 1 20 C 2 2 15 A 10 2 15 B 10 The report should show INV Tot Moved On hand 1 30 10 2 15 20 How would you go about making the report total correctly I get a total of 90 movements for INV 1 and 20 on hand For inv 2 I get total movement of 30 and a qty of 20 |
Replies:
Posted By: otto
Date Posted: 19 Mar 2014 at 5:15am
| Are you linking INV Warehouse and INV Movement to INV? |
Posted By: DoubleD
Date Posted: 19 Mar 2014 at 5:29am
|
Originally posted by otto Are you linking INV Warehouse and INV Movement to INV? Cyrstal defaults the links but I deleted them all and made them INV link partno to partno left outer join to INV Movement and INV link partno to partno left outer join to INV Warehouse So I think the answer to your question is Yes |
Posted By: otto
Date Posted: 19 Mar 2014 at 5:36am
| you should not link more than one table to a field because it will open those tables at the same time and it will duplicate, triplicate you data. Try linking INV to Inv Movement and then Inv Mov to Inv warehouse, then group by INV and do the summary |
Posted By: otto
Date Posted: 19 Mar 2014 at 5:46am
| If that does not work you can put Inv Warehouse table in a subreport linked to INV by INV, so in the main report you will have INV and Inv Movement, there do a group by INv, put there the INV, the summary by move and the subreport, in the subreport make a gruop by INV as well and put on that gruop the summary by QTY |
Posted By: DoubleD
Date Posted: 19 Mar 2014 at 5:54am
|
Originally posted by otto If that does not work you can put Inv Warehouse table in a subreport linked to INV by INV, so in the main report you will have INV and Inv Movement, there do a group by INv, put there the INV, the summary by move and the subreport, in the subreport make a gruop by INV as well and put on that gruop the summary by QTY Originally posted by I was just about to post I'm new to crystal reports and I just can't figure this out. I can get it to work if I link just INV and INV Movements or just INV and INV Warehouse I may just write two reports. One for QTY on hand and the other for total movements. Then I'll export the reports to excel then use a vlookup to pull in the data into one sheet There has to be a way to do this in crystal but I  OMG I forgot all about subreports - I actually did something like this a while back but I only did it once and if you don't use it you lose it. OK now to remember how - Better go look at my old report Thanks I'll post back if it works |
Posted By: DoubleD
Date Posted: 19 Mar 2014 at 6:04am
|
Originally posted by otto you should not link more than one table to a field because it will open those tables at the same time and it will duplicate, triplicate you data. Try linking INV to Inv Movement and then Inv Mov to Inv warehouse, then group by INV and do the summary I may not have a movement for every piece of inventory so wouldn't I drop records by linking like this ? |
Posted By: otto
Date Posted: 19 Mar 2014 at 7:02am
| then link INV to INV ware and INV ware to INV move. Did you try doing the subreport? |
Posted By: DoubleD
Date Posted: 19 Mar 2014 at 7:12am
 Otto The Subreport worked Thank you ever so much for reminding me I am interested in your previous post about linking INV to INV MOVEMENT and INV MOVEMENT to INV WAREHOUSE - Would that really work? Wouldn't it drop records . I want data for verything in my inventory - if I have no movement would the outer join still bing in the INV record so that I can find anything in the warehouse table - I may have to experiment with this after I satisfy the client request. Ooops may have to switch the link and sub report I think I'm dropping stuff but 'm sooo much closer now Tanks a again |
Posted By: otto
Date Posted: 19 Mar 2014 at 7:24am
| you should do an inner join in order to populate all the records you have. |
Posted By: DoubleD
Date Posted: 19 Mar 2014 at 11:09am
|
Originally posted by otto you should do an inner join in order to populate all the records you have. Are you sure about inner join ? I swapped my tables around and now the report is running forever. Makes scene I am no longer linking a 1 to many but a many to many so if I have 200 records in one and 100 n the other I have to pull back 100 record 200 times. OK time to try the Full outer join and put the tables back the way they were Again thanks for your help See below for what my Documentation tells me on joins Inner An Inner join is the standard type of join. The result set from an Inner join includes all the records in which the linked field value in both tables is an exact match. Left outer The result set from a Left Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table Right Outer The result set from a Right Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the lookup (right) table for which the linked field value has no match in the primary table Full Outer A Full Outer join is a bidirectional outer join where you can see all records in your linked tables. The result set from a Full Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table, and a row for every record in the lookup (right) table for which the linked field value has no match in the primary table |
Posted By: otto
Date Posted: 20 Mar 2014 at 3:01am
|
try doing a left outer but in this way
INV-->INV Ware--->INV Move because you cannot have movement for each warehouse but if you have movement that will belong to a warehouse |
Posted By: DoubleD
Date Posted: 21 Mar 2014 at 3:39am
|
Originally posted by otto try doing a left outer but in this way INV-->INV Ware--->INV Move because you cannot have movement for each warehouse but if you have movement that will belong to a warehouse I finally got this to work. I made 2 Passes if you will INV - INV Ware (did a small selection to exclude a couple of WH we didn't want and qty <> 0 ) then I made a subreport (2nd pass as I call it) going to INV Move I grouped on partno on both main and sub reports and It all came out. The report took 2.5 + hours to extract to excel but then I changed the report options to do grouping on the server and it sped things up maybe 5 minutes to do the extract  Thank you for all your help and patience with my newbie questions |
Posted By: otto
Date Posted: 21 Mar 2014 at 3:48am
|
I am newbie like you, so was a help between newbies, hahaha. You are very welcome
|