Skip to Content
author's profile photo Former Member
Former Member

Crsytal Reports - Dataset - 2 Fields same name

Hello,

I am having trouble displaying data from MySQL in Crystal Reports.

I have 3 tables:

tblproperty: ref

tblpropert_room: fk_property_ref, fk_lookup_rootyp_id & description

tbllookup_user: id & decription

tblproperty_room;description = description about each room ie lovely view from the window

tbllookup_user; description = description text for the room name ie bathroom

I can call them using an MySql statement:

SELECT a.no_name, a.address_line1, a.address_line2, a.address_line3,a.address_line4, a.town, a.county, a.postcode, a.display_price, a.general_desc, a.ref, a.viewing_text, a.directions, a.services, b.dimensions_ft, b.dimensions_m, b.description, b.fk_property_ref, b.fk_lookup_rootyp_id, c.description FROM tblproperty a, tblproperty_room_details b, tbllookup_user c WHERE a.ref = b.fk_property_ref AND c.id = b.fk_lookup_rootyp_id AND a.ref= "pra50191"

THE PROBLEM IS HERE......

This data gets passed to a dataset which is then used to design the crystal report. But in a dataset you cannot name a field twice (description; in tblproperty_room_details and tbllookup_user

I have created two datasets, displayed the records, but the problem is that the datasets will not link.

I have tried merging AND linking via the wizard (GUI) - but results in the report adds a room description to each type of room ie bathroom several times. It does not link. This is the problem.

If we could add 2 fields with the same name within a dataset it would work fine as I would state id=fk_lookup_id & ref=fk_property_ref

But I am stuck and unsure of what to do, so any help would be great???

thanks in advance

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 13, 2011 at 02:38 PM

    The problem I am having is that I cannot use the same fields name in the same dataset so would be unable to call within the same string as the names have to be the same (from the DB to the Dataset)

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Yes, I know.... If you look closely at the statement I posted, I made 2 changes in your SQL statement, giving both description fields an alias so they have different names.

      b.description as description_B   and   c.description as description_C

  • author's profile photo Former Member
    Former Member
    Posted on Sep 09, 2011 at 05:44 PM

    I am in no way a SQL expert, but try something like

    SELECT a.no_name, a.address_line1, a.address_line2, a.address_line3,a.address_line4, a.town, a.county, a.postcode, a.display_price, a.general_desc, a.ref, a.viewing_text, a.directions, a.services, b.dimensions_ft, b.dimensions_m, b.description as description_B, b.fk_property_ref, b.fk_lookup_rootyp_id, c.description as description_C FROM tblproperty a, tblproperty_room_details b, tbllookup_user c WHERE a.ref = b.fk_property_ref AND c.id = b.fk_lookup_rootyp_id AND a.ref= "pra50191"

    I have renamed several fields with the AS (Thanks Jason)

    Debi

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 14, 2011 at 08:41 AM

    Thanks Deb it worked 😀 - sorry about the mistake !

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.