I have a single table collecting weekly count of two numbers (active listings and pendings), for dollar values beginning at 100k and increasing at 10k increments up to $1m. Fields include basic id data (area, map), status (either A or P--active or pending), week_enddate (last day of weekly report period), and then a single field for each beginning dollar range (e.g., 100k, 110k, 120k, .....1000)

Each week, user enters two records in each area that reflects the number of homes in each dollar range: first record is the number of active listings (A) the second record is the number of pendings.

Sample data:

Fields:
area, week_enddate, status, 100k, 110k, 120k, 130k,.....990k, 1000k

Data:
700 11/2/03 A 2 3 1 3 2 1
700 11/2/03 P 1 0 0 1 0 0
705 11/2/03 A 7 17 9 5 10 7
705 11/2/03 P 1 5 2 0 3 2
--
etc--
--
700 11/9/03 A 2 2 1 2 4 2
700 11/9/03 P 0 1 0 0 1 1
--
-etc

My problem with this design is I need to extract data in a couple of different ways that I don't know if this design supports.
One extract: Query by date range, statistics on actives and pendings for specific dollar values: Date range will be represented by week 1, 2 etc. This would ultimately be printed, three or four dollar value ranges on a sheet; six weeks of data across.

Example:

Price: 130k

week# 1 2 3 4 5 6
# listings 4 5 2 6 3 7
# Pendings 1 1 1 3 0 2
Sales ratio (calc field)


Price: 140k

week# 1 2 3 4 5 6
# listings 7 4 2 7 2 9
# Pendings 2 0 0 1 1 1
Sales ratio (calc field)

Price: 150k

week# 1 2 3 4 5 6
# listings 1 4 1 4 2 2
# Pendings 0 1 0 1 1 1
Sales ratio (calc field)


The other extract will display the listings, pendings in dollar ranges for an entire month (example):

Price range #listings # pending
0 - 100K 0 0
100-149k 2 0
150k-200k 3 1
etc etc

Please help!!!!

Cal