Crosstab Query Problem

muirchez

steve muir
Local time
Today, 05:46
Joined
Nov 23, 2006
Messages
8
I have a fairly complex (at least to me!!) crosstab query I need to create for a work project. The problem is as follows.....

I have a table which has a list of houses. Fields included are:
Postcode
address 1
address 2
address 3
Region
Price paid
Price bracket
date sold

This table has multiple entries for each house sold (i.e. The same property has been sold numerous times over the years)

What I been asked to do is this:

1: Count the number of instances of each property sold (by region)
2: Calculate the average length of time between sales (by region)
3: Calculate the average number of times properties are sold between the first sale date and last sale date (by region)

I know the logic behind what is needed but have no idea how to query this in access. I presume I can do this with a cross tab query?? but am really struggling with this.

Any help or pointers would be greatly appreciated. I can provide the table if required.

Many thanks in advance for any help given.

Steve
 
Probably not a crosstab you need:

1. A select query - group by postcode and address line 1, count date sold where date sold is not null
2. Another select query - group by region, have a calculated field - look up datediff and average that
3. And another select query - group by region, count sale dates.

Presto!
 

Users who are viewing this thread

Back
Top Bottom