Group with Time Values - Please Help

I have two tables that are joined by a left outer join.
Table A (Hours) just has 24 records that represent each our in the day. For example:
00:00, 01:00, ...23:00. Table B (Data) has the data I need to report off of. I joined
Table A to Table B.

I created a group on Table A because I always want to display all 24 hours even if there
is no data in Table B for that hour. So I now have 24 sections in my group.

My problem is that when I put data into the details section, I'm only getting data where
the hours exactly match. For example, Group section 08:00 is only returning data where
the hour is 08:00. I actually need it to return all data where the hour is between
08:00 and 08:59. I've been working on this for a while and I'm really stuck.

I'm using an access database and the hours field in both table is a date/time field.

Any help would be greatly appreciated. Thanks so much.

- StephanieHi,

I had experienced the same problem. I had to display the data for all the days in a month regardless of the data they have.

Used the same left outer join concept. But it didn't work. Then I had created a temp table and written code to get the result.

If any body knows why the left outer join concept is not working in crystal, please share with us

sample

table1 : contains simply all the dates from 1 to lastday
table2 : contains data for the dates in table1 (not for all the days)|||Hi Stephanie
One possible solution would be to create a report based only on Table A(with hours registered) and subreport based on Table B.Don't make any links between them.In main report insert group for field that holds hours.If you view preview now you would see all records from Table B for each hour.
In main report create a formula and add shared variable and assign only first two characters from group field.Values will be 00,01,02 etc.
Now,in subreport supress all records where first two characters of your hour fiels in Table B are not equal to shared variable.
I tested this in CR 8.0 and it worked fine.|||Thanks Denan, that's a very interesting suggestion! I'm going to try that right now!

Stephanie|||Good idea!

But what about the performance?

For a single day, the report will be called 24 times?|||Hi
Performance is definetly not optimal here.Best solution would be to filter data in subreport but unfortunately CR (at least 8.0) doesn't allow shared variables in record selection.
Biggest problem here is that you can not link those two tables.Left outer join doesn't help since it also requires a match in both tables.
Another solution would be to add another field in Table B that would hold values like 08:00,09:00 etc.That means you would need to add application logic to compute that value.I think that solution is more expensive.

Đenan