Query Problem 1

[jschodde]

I have a table called "workdata" with two fields (and others) for employee names that are free-form data entry (not codes). The field names are: fullname1 and fullname2.

What I need to do is build a unique list of all names from these two fields for a Crystal report. I could do the following:

select fullname1 as name from workdata
union
select fullname2 as name from workdata
order by name

but the resulting "name" field would be a memo field in Crystal Reports - which for me is not acceptable.

My question to anyone is this: Is there an alternate way to write this query to achieve the same results so I can avoid the memo field problem?

Thanks,
Jeff

 

[dcm]

jschodde: I think the issue here is not how Crystal sees name but how your database has name defined. If this is a character field of more than 254 in length then the only way Crystal can 'see' this is as a BLOB (memo) field. I suggest, if you can, modifying the database design to give multiple char fields acting as 'compound' entries for name.

If the fields are defined as char less than 254 then your unioned query should return a text field in Crystal David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Crystal Decisions Enterprise Partner

 

[jschodde]

They are defined as text fields in MS Access. Unfortunately I have no control over the data structure as the application has been sold to many people.

What I did end up doing is kind of cheesy, but works....

I create the query like shown above as the "source" for a subsequent query that does this (assume first query above is called qryWorKData):

select left(name,20) as name from qryWorkData;

Now, in my report I set location to this second query and it works! Crystal returns the name field as char(255) instead of memo.

-Jeff

 

[dcm]

Jeff: Good solution - an embedded query! Why didn't I think of that. Your original problem seems to have the guys at Crystal stumped as well as Ken has pointed out to another poster. Why not drop Crystal a line on their website letting them know of your workround? David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Crystal Decisions Enterprise Partner

 

[jschodde]

Thanks for the kind words

What do you mean about the guys at Crystal being stumped? Do they read these forums? Either way - that's cool.

I'd be happy to share what I've done. Do you think my solution warrants an FAQ on this forum?

I'll also post my results on their internal forums in case anyone needs the info.

-Jeff