Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: SQL with Crystal Reports

    I am working with a poorly designed database and need to create what I think is a simple crystal report. (perhaps someone can help me with the SQL language or some other way to create the report)

    Illustration:
    In a properly designed database you may have the following:

    Table 1:
    CompanyID
    Name of Client
    Address of Client
    SalesRepID


    Table 2:
    SalesRepID
    First Name
    Last Name


    With the above database I can link SalesRepID to each table and easily create a report that lists the name of the client as well as the name of the sales rep.


    I have to work with the following database:


    Table 1:
    CompanyID
    Name of Client
    Address of Client
    SalesRep1ID
    SalesRep2ID
    SalesRep3ID
    etc...

    Table 2:
    SalesRepID
    First Name
    Last Name


    Instead of properly creating a 3rd table to allow multiple SalesReps, I have a database that lists SalesRep1ID, SalesRep2ID, etc... There are some tables that repeat this to 100!

    Now, I cannot simply link SalesRepID to SalesRepID since there are multiple links.

    PROBLEM:
    How do I create a report that list ALL sales reps and their names on the same report??
    Do I have to create multiple SQL statements for each sales rep?
    Any idea how to do this in Crystal Reports????

    I am hoping there is a simple solution that I am missing??

    Thanks

  2. #2
    Join Date
    Sep 2004
    Location
    Monterrey Mexico
    Posts
    2
    What about a sp returning a rowset ?

  3. #3
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112

    Question Possible solution, but more info may be required

    In the table:

    CompanyID
    Name of Client
    Address of Client
    SalesRep1ID
    SalesRep2ID
    SalesRep3ID


    How many SalesRep ID fields are there (i.e. is it SalesRep1ID through SaleRep100ID, ...)?

    Can you use a UNION in the SQL? E.g.

    SELECT CompanyID, Name of Client, Address of Client, SalesRepID = SalesRep1ID
    FROM Company
    UNION
    SELECT CompanyID, Name of Client, Address of Client, SalesRepID = SalesRep2ID
    FROM Company
    UNION
    SELECT CompanyID, Name of Client, Address of Client, SalesRepID = SalesRep3ID
    FROM Company

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •