kmay
asked on
Missing records when using 2 tables
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
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
ASKER
Well...I'm not too happy about Crystal assuming anything!!
Thanks for the help!
Kathy
Thanks for the help!
Kathy
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.
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.