Combination Crystal-Access Issue (CR8.5)

[ja01]

I have an access database, lets call it test.mdb with the following fields: requestor(text),school/area(text),jan(date/time),feb(date/time),mar(date/time), etc. The issue is that not all data will appear in all months so on the crystal side I created 12 different crystal reports and named it by the month of the year. Is it possible or practical to have just one crystal format and use a parameter to pull in the data based on the month chosen or is it best to do it the way I have. The only problem is I have to change all 12 formats if I need to add a new field. If I use a parameter is it best to make it a string parameter instead of a date/time parameter? If so, it means I must change the dates in the access table to a text.

 

[lbass]

It is very likely possible to design just one report. Please explain what you are trying to do with the report, and how you would like the report to appear. Some sample data would help. It is unclear what the problem is that you thought could only be solved by creating separate reports.

You could, of course, create a number parameter {?month} and then set up your record selection formula like:

{table.date} in date(year(currentdate), {?month},01) to dateserial(year(currentdate),{?month)+1,01)-1

-LB

 

[ja01]

I will try your solution but here is an explanation. Here is a sample database with fields and data:
requestor(text)= 'john'
school/area(text)= 'school of business'
Jan(date/time)= '1/1/06'
Feb(date/time)= '2/1/06'
Mar(date/time)= '' Nothing this month
Apr(date/time)= '4/1/06'
May(date/time)= '' Nothing this month
etc............
The table in access is called test.mdb. The idea is to have one crystal format that pulls data month to month (ie Feb) Essentially, each month has its own field name. On the crystal side, can I pull this information using just one crystal format not 12. I will try your solution but in case I did not explain this issue, hope this helps

 

[synapsevampire]

Since you have different date fields (generally an indication of bad design), you can create a formula such as:

if {?parameter }= "Jan" then
{table.jan}
else
if {?parameter }= "Feb" then
{table.feb}
>etc...>

Now use this formula for whatever you are doing.

-k

 

[ja01]

I welcome your response but could you provide a general example of how my design could have been better. I assume you are talking about the use of dates as fields

 

[synapsevampire]

One table might have:

Period table:
Periodkey (unique id)
month

Other table might have:

Values table
Valueskey
table.Periodkey (from period table)
value

Now join the tables by the periodkey and select all of the values where the period = your month.

It's called normalizing, but this might not prove to be the best solution for your database, it depends on many factors.

As for general database design, that's too large of a topic to tackle here,a dn you'd be better served reading a bit and hitting the Oracle or SQL Server forums.

Most of the database people here are novices and tend to do everything in Crystal, which is the opposite of what you should be doing.

Note also you could pseudo-normalize your current data using Access queries to allow for simpler queries.

-k