Left Join & too many records returned 2

[SarahS1]

I am a new user to Crystal. I am using version Crystal XI. I am trying to recreate an Access Report into Crystal. Below is the code from both reports. My Access report correctly pulls in the province (ALADDS) based on the address number (AN8), in Crystal it seems like my join isn't working correctly and it pulls in more lines of data. My Access Report pulls in 1775 lines. If I don't use anything from table F0116 in Crystal I also get 1775 lines. As soon as I add the ALADDS field I get 2282 lines. I have played with the various settings on the join, and if I do an Inner Join I get 2280 lines. I'm not sure what I am missing.

CRYSTAL
SELECT "F0911"."GLKCO", "F0911"."GLMCU", "F0911"."GLOBJ",
"F0911"."GLDCT", "F0911"."GLDOC", "F0911"."GLAA", "F0911"."GLDGJ",
"F0116"."ALADDS", "F0116"."ALAN8", "F0911"."GLAN8"
FROM "JDE_EPD_REP"."CPRODDTA"."F0911" "F0911" LEFT OUTER JOIN
"JDE_EPD_REP"."CPRODDTA"."F0116" "F0116" ON "F0911"."GLAN8"="F0116"."ALAN8"
WHERE ("F0911"."GLDGJ">=109091 AND "F0911"."GLDGJ"<=109120) AND
"F0911"."GLOBJ"=N'50003' AND ("F0911"."GLKCO"=N'00000' OR
"F0911"."GLKCO"=N'10361')
ORDER BY "F0116"."ALADDS"

Access:
SELECT DISTINCT CPRODDTA_F0911.GLOBJ, CPRODDTA_F0911.GLDGJ,
CPRODDTA_F0911.GLKCO, CPRODDTA_F0911.GLMCU, CPRODDTA_F0911.GLDCT,
CPRODDTA_F0911.GLDOC, CPRODDTA_F0116.ALAN8, CPRODDTA_F0911.GLAN8,
CPRODDTA_F0116.ALADDS, CPRODDTA_F0911.GLAA
FROM CPRODDTA_F0911 LEFT JOIN CPRODDTA_F0116 ON CPRODDTA_F0911.GLAN8 =
CPRODDTA_F0116.ALAN8
WHERE (((CPRODDTA_F0911.GLOBJ)="50003") AND ((CPRODDTA_F0911.GLDGJ)
Between 109091 And 109120) AND ((CPRODDTA_F0911.GLKCO)="10361" Or
(CPRODDTA_F0911.GLKCO)="00000"))
ORDER BY CPRODDTA_F0116.ALADDS;

 

[jmd0252]

OK,, I can tell you are bring you G/L file and linking it to an address file.. I came from JDE. The G/L file could have many records with the same address.. I would guess the F0116,, has one record to an address line, you can try adding the company nummber to the link. You can also, look in access at how the join is defined there. You want all the G/L records of a certain address or what??

 

[SarahS1]

Yes- JDE data. When I run the F0116 (address book info) file on its own there is only 1 address field per line. Yes the GL field can have multiple order lines with to the same address. I am trying to pull in the province field. The province field is only stored in the F0116. I am trying to determine the sales by province. There are no other common fields between the F0116 and F0911. I can't use company number because that isn't in the F0911. I checked out the access join and it is also a left join from the GLAN8 to ALAN8.

 

[jmd0252]

Actually the compnay number is in the F0911, there are 2 ,, could be different.. or could be same.. Are you filtering by a specific document type? OK the joins sound correct. You will get all the F0911 records,, and only info from the F0116 where the address numbers match. Your record count should be only what records you get from the F0911. Sometimes the joins in the data expert window can be confusing.. Remove all the links,, and then do the click and drag, from the F0911 to the F0116,, then edit the join,, and make sure it says left outer. Try this and see,, Let me know..

 

[SarahS1]

Ok - I relinked - shows "F0911.GLAN8 --> F0116.ALAN8". I selected the "left outer join" option. Under "enforce join" I used "not enforced" - it returned 2282 records, I used "enforced from" it returned 2282 records, I used "enforced to" it returned 2282 records I tried "enforce both" and it returned 2282 records.

Re company number - sorry I meant to say that it isn't in the F0116. I have the table field names and descriptions in excel and I did a vlookup to see if there were any other comonalities that I might be missing. The only other fields that are the same are User ID, Program ID, Date Updated, Work Station ID and Time last updated, but linking on those isn't going to help me here.

Re filters: on the F0911.GLOBJ choosing 50003 (the sales account), F0911.GLKCO choosing my company code, and in F0911.GLDGL filtering for a 1 month date range.

Re record count. Thats why I am confused about this join. When I run the report with nothing from the F0116 then I get the right number of records, and the right $ amount. When I add in the province code ALADDS from F0116 I get more records and the wrong $ amount. It pulls in duplicate for some line items.

OK - so then I compared an extract from my access query vs my Crystal query - and checked where I was getting duplicates. There is nothing wrong with the data from the F0911. I ran an Access extract of problem customer numbers and guess what? surprise, surprise, the problem customers are appearing the F0116 twice! There is nothing different about the lines - no later date stamp or anything. So - is this a database integrity problem, as my understanding is that they should only be appearing once. So - is it worth trying to have my IT fix it? Or is there another way around it? Can I restrict the lookup to only match to the first correct match it sees? Or create an subreport that only pulls the data once and then try to link to that? Thanks for any insites you may have...

 

[Turkbear]

Hi,
Absolutely notify IT about the data issue...If that is a production system then folks rely on it to be correct -
attempting to correct for bad data by some report design manipulation is not a good practice and will not fix the underlying problem -
( Having been both a DBA and a report developer, I know that if any developer had discovered bad data and failed to inform me, or someone else in our database admin group, they might be looking for a new career.)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

In Crystal, go to database->check: select distinct records. I don't see the "distinct" in your Crystal query.

-LB

 

[jmd0252]

Well lets see, what we can do. What type of Edwards version are you on. We were on World 7.3 cumm 11. If you know how to do a wrkqry. Look at the F0116 file,, select a customer that is giving problems. After the address numnber there is an effective date and another one byte field, using those other 2 fields may give you way to filter the F0116 records. You can also pull up that customer in the address book, and see if that tells you anything. If you are still having problems you can contact me directly, if you need to. james.denton@hinklecontracting.com

 

[jmd0252]

There is also, another Wed site for jde users, World or Enterprise, and it has a Crystal section.

http://www.jedlist.com

 

[SarahS1]

James- thanks for walking me through this. I apologize, I thought I had a good understanding of how the files worked from my experience in Access. Just goes to show me that there is always more to learn. Thanks for the new link - I will bookmark that too!

Turkbear - I e-mailed my IT and they are looking into it.

Linda - I did was you suggested and it works! I tested 3 months of data and it all appears correct. Thank-you!