Pulling a specific value from another table/database

[jstoehner]

I am fairly new to Crystal Reports. I work for a call center specializing in leisure/hospitality reservations, the bulk of which are golf tee times. My task is to create a report showing a line by line detail of our bookings for a given period including the applicable green fee based on the facility (course) and date.

Most of the report has been created but I am having a tough time conceptualizing how to pull in the rates (even though it seems that it should be fairly simple). I have the rates in a simple Access table (open to other suggestions) as follows.

The top row (column headers) in my Access table contains the unique facility ID and down the left would be 01/01/07 through 12/31/07. The data in the intercecting fields would be the rate for that facility on that date.

| Date | 90100| 90102| 90103 |
| 01/01 | 55.00 | 60.00 | 85.00 |
| 01/02 | 65.00 | 70.00 | 75.00 |
| 01/03 | 55.00 | 50.00 | 55.00 |
| 01/04 | 75.00 | 90.00 | 65.00 |
| 01/05 | 75.00 | 40.00 | 95.00 |
| 01/06 | 95.00 | 50.00 | 35.00 |

So if my reservation report looked like this, I would want to see the following...

JOHN SMITH | FAKE COUNTRY CLUB | 90102 | 01-04-2007 | $90.00 |
MARK JONES | NOTA COUNTRY CLUB | 90100 | 01-06-2007 | $95.00 |

I would greatly appreciate some suggestions on the best way to approach this. I have CR v.10 and the reservation data is pulled from an Oracle database via ODBC.

Thanks!

 

[synapsevampire]

Access tables don't have values across the top as if in a crosstab.

If you post example data and expected output you can minimize attempts to describe basic technical information.

So assuming what you are asking is how to join an Oracle table to a lookup table in Access, go ahead and join by the facility and the date field.

We don't know the names or the data types of the fields in either database, so again, try to post technical information first, then describe whatever you feel is required afterwards.

You can also LINK (not import) the Oracle tables into the Access database and build a query within it as the datasource for the Crystal Report,this will prove faster.

-k

 

[jstoehner]

Let's see if I can improve my question some. I'm starting to wonder if the Access table will accomplish what I thought so I may need to go in a diffrent direction all together.

The main database with all the reservation data (except for the green fees) is the Oracle. The relevant fields are as follows...

MEMBER_NAME (text)
FACILITY_ID (text)
FACILITY_NAME (text)
RESV_DATE (date)

There is a specific rate that for each facility for each date. I currently have these rates in a spreadsheet with the facility ID across the top in row 1 and the dates down the left in column 1. So in my CR I would need to add the corresponding rate for that facility ID and that date.

Any recommendations?

Thanks for your time.

 

[synapsevampire]

Sure, don't use spreadsheets for data.

Your first post stated "The top row (column headers) in my Access table".

Now you say it's Excel. What are we to understand from this?

The data should look like:

Date Facility Rate
01/01 90100 55.00
....etc...

So again, try to avoid being clever in table design, normalize the data and use a real database preferably Oracle, but if you need a secondary database, use Access over Excel.

-k

 

[jstoehner]

Thanks for your help. You were correct in that I was overcomplicating the table design. Now I'm in need of formula help so off to that board....