adding a non-existent group

[swhitten]

I have an Access database that has a query in it that returns the following columns for property sales:

County
Month Sold
Price Range
Count of sales for county/month sold/price range group

It sums by county, month sold, and price range. Results look something like this:

Smith County August 2009 $0 to $150 3
Smith County August 2009 $150 to $200 6
Jones County August 2009 $300 to $350 2
Smith County Sept 2009 $0 to $150 1
Smith County Sept 2009 $150 to $200 2

Not all counties have listings for each month/price range.

The Access db includes a Price Range table; an earlier query links the price to the price range table to get the price range for each record. Then the summary query does the subtotaling (counting) off that query.

The price range table has the following groups:
$0 to $150
$150 to $200
$200 to $250
$250 to $300
$300 to $350
$350 to $400
Over $400

Here is my issue. I need the report to show a row for EACH price range for each county/month, even if there were no listings. It should say 0 if no listings for that county/month group.

So Smith County for August would have 7 rows - one for each price range. Based on the data shown above, only two of the price ranges would actually have values; the others should be 0.

I just don't know how to make something out of nothing. I tried linking together my summary query with the price range table, forcing it to select all records from the price range table. But somehow that doesn't give me any empty price ranges.

I'm out of ideas.

Thanks!

 

[lbass]

You have created this datasource in Access, doing the linking there, so that Crystal can't really solve this problem. I think it has to be addressed in Access. I can't give you much help in Access, but it looks to me like you have to lead with the price range table and use left joins from this table to the sales table.

-LB

 

[swhitten]

LB - thanks for the reply. Left joins in Access behave very weird. Not like I would expect, since I usually work with SQL. ugh.

I did however get this resolved.
Three tables in access - one each for all possible price ranges, all possible counties, and all possible months.

Then I wrote a query in Access that pulls all three tables in. No linking, because there are no fields to link on. But by adding all three tables with no linking, access builds a dataset that is all possible combinations of the three items. Who knew?

Then I wrote another query that links the Sold Count query to the All Possible query. Works beautiful.

Solved in Access rather than in Crystal.

 

[elsenorjose]

But by adding all three tables with no linking, access builds a dataset that is all possible combinations of the three items. Who knew?

That's called a Cartesian Product and generally not a good idea. It can result in HUGE datasets as the database grows.

http://www.smartcomputing.com/Edito...cle=articles/2006/s1705/51s05/51s05.asp&guid=

 

[swhitten]

Elsenorjose,
Thanks for the feedback on the Cartesian Product. Tell me if you think I will be okay with this. My all-possible-combos query should not grow for the next 10 years. I built it with all possible counties (99), all possible price ranges (21), and 10 years worth of months. We will never add counties or price ranges, and we won't have to add months for a long time.

Does that overcome your valid concern about the dataset getting too big?

Thanks again.

 

[lbass]

As long as the report is not too slow, then I think this is a fine solution.

-LB

 

[chelseatech]

We did a series of articles in our newsletter on how to create a report with data that's not there.

One approach to look at is using the Previous and Next functions to interrogate adjacent records to see if the data has all the required values. Display additional sections to display text that contains the missing groups.

More details on these techniques in the (free) backissues.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm