Can I pass Crystal parameter value to Access query?

[swhitten]

I have an Access query that counts the number of home sales/closings by region, by month. The query is summarizing data found in a detail table that lists every single home sale/closing and includes fields such as address, area, square footage, lot size, closing price, etc. So Area A had 5 closings for the month of August, 2009.

I have a similar Access query that counts the number of open home listings by region, by month - based on detail table of listings. So Area A had 10 houses on the market for August, 2009.

In Crystal, I want to display columns in this order:
Count of listings for the region/month
Count of sales for the region/month
Months Inventory - calculation of listings divided by sales - in the example above, 10 open listings divided by 5 closings = 2 months inventory.

I will have 36 columns going across the report - Oct 2009 listings/closings/inventory, Sep 2009 listings/closings/inventory, etc.... for 12 months (3 columns per month).

All of that is good, until I add this complication. The user wants to be able to filter the underlying data using several different Crystal parameters. Examples:
Show me only houses in the 2,000 to 2,500 square foot size range.
Show me only houses that are less than 5 years old.
Show me only houses where the price per square foot is $80 to $100.

The values to do the filtering are in both the Listings detail data and the Closings detail data. So I need the count/summary queries to filter based on the Crystal parameters. Is that possible?

Alternatively, I can't come up with a way to create this report by bringing all the detail records to Crystal, since I am working with two disparate data sets - listings and closings. I thought about sub-reports, but I run into issues with two things:
1. I have to do the listings / closings calculation.
2. I need to display the # of listings next to the # of closings for each month. I can't just show 12 months of listings, then 12 months of closings.

Any ideas? Thanks!

 

[swhitten]

Progress...
I added a simple parameter in my Access summary query - Age of house. When I run the Access query in Access, it prompts me to enter a value for Age of House.

Then I wrote a simple Crystal report based on the Access query, and it automatically added a parameter field for Age of House in the Crystal Report. When I run the Crystal Report, it prompts me to enter a value for the parameter, and it seems to be passing that parameter to the Access query.

Now I have an issue with the Access query not summarizing properly, but that's a different issue.