Record Selection / Database Linking Question

[izenger]

Hi all,

Hoping to get some help regarding this particular issue I'm having. Hopefully this description of what I'm trying to do makes sense. Been banging my head against a wall for a few days and not getting anywhere. I'm working a database via ODBC and trying to pull out info from 3 tables.

Table A
a.key
a.date

Table B
b.num
b.key

Table C
c.num
c.date
c.seq
c.stat

a.key is linked to b.key via left outer join
b.num is linked to c.num via left outer join

My record selection is based on {a.date} from xxx to xxx where xxx is a date/range time (mm/dd/yyyy hh:mm:ss)

What I'm trying to do is for a certain date range that I specify for Table A, I want to pull all records in Table B with the corresponding date and status showing.
 
Here's an example of what info is in each table corresponding with the fields I put above:

Table A
1111  01/01/2014 08:00:00
1112  01/01/2014 14:00:00
1113  01/02/2014 15:00:00
1114  01/03/2014 07:00:00
1115  01/05/2014 07:00:00
 
Table B
1111  2222
1112  2255
1113  2677
1114  2888
1115  2991

Table C
2222  01/01/2014 08:00:00  1 Input
2222  01/05/2014 14:00:00  2 Sup
2222  01/07/2014 07:00:00  3 Complt
2255  01/01/2014 14:00:00  1 Input
2255  01/01/2014 20:00:00  2 Sup
2255  01/10/2014 10:00:00  3 Complt
2677  01/02/2014 15:00:00  1 Input
2677  01/02/2014 15:01:00  3 Complt
etc.

What I'm trying to do is select a date criteria for Table A (say from 01/01/2014 00:00:00 - 01/01/2014 23:59:59) and have it display all records from Table C for the corresponding record from Table A.

For the search criteria, it should return:

1111
1 01/01/2014 08:00:00 Input 
2 01/05/2014 14:00:00 Sup
3 01/07/2014 07:00:00 Complt

1112
1 01/01/2014 14:00:00 Input 
2 01/01/2014 20:00:00 Sup
3 01/10/2014 10:00:00 Complt

Instead, the result I'm getting when I do that date/time range search is:

1111
1 01/01/2014 08:00:00 Input 

1112
1 01/01/2014 14:00:00 Input 
2 01/01/2014 20:00:00 Sup

Crystal seems to be taking the date/time range for Table A and using that same range for Table C, instead of getting all records from Table C that I want.

Is there a way I can get this report to show what I want? Any help would be greatly appreciated.

Thanks,

Isaac Zenger

 

[briangriffin]

When I built this it works as expected -

TableA.Key inner join to TableB.Key
TableB.Num inner join to TableC.Num

Insert group on TableA.Key
In Details put TableC.Seq, TableC.Date, TableC.Stat. I also put TableA.Date into Details just as a visual check.

Record selection: TableA.Date between 1/1/2014 00:00 and 1/1/2014 23:59

I don't think changing the join types explains your issue (for this limited dataset it makes no difference) - my best guess is that you've grabbed the wrong date field.

Regardless, the approach you describe is sound so first make sure that's actually what you've built into the report.