Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2013
    Posts
    2

    Select one field based on another table

    I need some help...
    I have a database that I am using Crystal Reports 2008 to connect to.
    I have a table called "PRICE" set up with the following fields: [TYPE], [SERVICE], [FEE]
    The table data looks like this:

    TYPE SERVICE FEE
    1 1 100.00
    1 2 250.00
    1 3 350.00
    12 1 35.00
    12 2 50.00
    12 3 75.00
    23 1 55.00
    23 2 80.00
    23 3 120.00
    etc...

    I then have another table called "REPORT" that lists the services completed which correlate to the table above:

    Order_ID SERVICE_ID
    200100012 2
    200100056 1
    200100082 2
    200100251 3
    200100365 1
    etc...

    I need to create a report that will extract the FEE from the PRICE table based on the SERVICE_ID field from REPORT table.

    I think I need some sort of formula but not sure how to go about it. I do not want the other FEEs to be displayed.

    Any help will be greatly appreciated.

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    The correlation between the 2 tables does not make sense to me!

    If the Serivce_ID = Type, there is nothing to tell me which type you want.
    For example if Service_ID = 1, looking at the Price table, there are 3 Rows that are of type 1 with fees of 100, 250 & 350. Which one?

    If the Service_ID = Service, you have the same problem as above! Too many duplicates of any one Service to make a determination of which Fee is correct.

    Your REPORT table needs a Type_ID as well as a Service_ID to make any kind of meaningful join.

    This also should be posted in one of the forums specific to your database rather than New Members!

  3. #3
    Join Date
    Aug 2013
    Posts
    2
    I apologize for posting on this forum. In the future I will try to post in the correct area.

    There are other tables involde

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Bsierschul View Post
    I apologize for posting on this forum. In the future I will try to post in the correct area.

    There are other tables involde
    Not a problem for me. By posting in the correct area, more of the right people will see your post and the odds of getting a good answer go up!

    If there are more tables involved, then you need to let us know what they are so a better answer can be found!

Posting Permissions

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