Solved

Missing records when using 2 tables

Posted on 2002-03-12
7
209 Views
Last Modified: 2008-03-10
Using [2] Foxpro tables to build report.  Grouping on GLACMST.GLNO yields ALL GLNOs as expected.  When I add details (the GLNO fields in each table are indexed and linking properly) from the second table NONE of that data displays--if I format the section to [Suppress blank section] it IS suppressed.

I've built this several different ways: when pulling in the GLTRMST table first and using GLNO from that table all the data from both tables displays in the Details section ok...but I need to show ALL GLNO from the GLACMST table-which is the complete list.  The second table shows active transactions so not all GLNOs will have records from that table.

Sooo...how do I keep the report grouped on GLACMST.GLNO, and pull in the additional info from the GLTRMST table??
Thanks,
Kathy
0
Comment
Question by:kmay
  • 3
  • 3
7 Comments
 
LVL 1

Expert Comment

by:ssam
ID: 6857756
try a left outer join on the first table since not all GLNOs will have records from your second table as you stated in the second paragraph.  hth.  ---ssam
0
 

Author Comment

by:kmay
ID: 6857991
Hi,

I am unable to choose a link type - all options are grayed, only 'Equal Join' is allowed.  I have changed  the 'From' table to GLACMST- this returns all the GLNOs, but still no details are returned which are pulling from the GLTRMST table???

When adding the database I selected [Database Files | Find Database File]...should I have added the table using a different method??

Thanks,
Kathy
0
 
LVL 6

Accepted Solution

by:
dbirdman earned 200 total points
ID: 6858575
If you have the Query Designer Component with your Crystal Package, you can use it to create a Query file (.qry), then select the Query File as your Report Source, just like you do with a Database (Look under the Dictionary/Query Folder under the Add Database Menu Option).  You can use the Query Builder to specify the tables and join properties for the query, so that what your report sees is a record set with all of the data it needs.

Hope this helps.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:kmay
ID: 6861801
Hi,

Ok...so now I've had a crash course in Query design!  The data is showing up as needed...however there is a glitch. Any date field which in the original database is empty displays on the report as 12/30/1899 (yep not a TypeO).  

Well my boss, a Foxpro guy, went in and corrected that by modifying the FPro files...But, now I've just finished another report-different tables etc...and all null date fields are displaying 12/30/1899...any clues to where this is being generated and more importantly how do I resolve this?  I puled this data straight from the .dbf - no query.

Thanks for the help!
Kathy
0
 
LVL 6

Expert Comment

by:dbirdman
ID: 6861935
I'm not sure why you get 1899; it's probably a Crystal Reports Assumption.  One way you can control this is to either set up a Function Field for the date and put it on the Report, or right-click on the Date field on the Report and insert a conditional formatting expression.  The expression might look like this:

IIF(MyField = "", Date(), MyField)

In this example, if MyField is Null, I use the current Date, otherwise I use the value within the field.

Good Luck.
0
 

Author Comment

by:kmay
ID: 6862115
Well...I'm not too happy about Crystal assuming anything!!

Thanks for the help!
Kathy
0
 
LVL 6

Expert Comment

by:dbirdman
ID: 6862479
I'm not sure why you get 1899; it's probably a Crystal Reports Assumption.  One way you can control this is to either set up a Function Field for the date and put it on the Report, or right-click on the Date field on the Report and insert a conditional formatting expression.  The expression might look like this:

IIF(MyField = "", Date(), MyField)

In this example, if MyField is Null, I use the current Date, otherwise I use the value within the field.

Good Luck.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now