Conditional Table Link

[MichaelNZQA]

Hi, am a new CR 8.5 user so hope this is a reasonable question.

Within the database in hand, I have a table that identifies WEEKS by a start and end date. The transaction file only holds the transaction date and not a direct link to the WEEK reference etc.

I want to GROUP the various transactions by the WEEK reference, but am unable to create a formula that will select the correct WEEK for each transaction.

I can write an SQL query that selects by :
&quot; Where Tranx Date >= Start Date and Tranx Date <= End Date&quot;
but am unable to insert similar logic into Crystal Formula.

Does anyone have any thoughts?

 

[IdoMillet]

Before exploring the join issue, a simpler approach is to use the built-in Grouping by Week/Month/Quarter... that Crystal provides:

1. Group on the Transaction Date
2. In the &quot;Break&quot; option dropdown, select 'for each week'

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[MichaelNZQA]

Ido -
Thanks for that response. It deffinately allows me to group by week. However am delaing with a Time Sheeting system where payroll weeks run Wednesday to Tuesday which is controlled from my WEEK table. Group by Week uses a standard Sunday to Saturday week.

Regards

Michael NZQA.

 

[kenhamady]

If the week is always Wed to Tue then you should be able to make a formula that does this without the link.

Try:
{CAL.ONDATE} - DayOfWeek ({CAL.ONDATE},4)+1

Group on this and it should group on weeks from Wed to Tue.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[MichaelNZQA]

Ken

Top work - thanks for the assistance.

Regards

Michael.