Number of Customers Won and Lost

[MikeAuz1979]

Hi,

Using Access 97 I have what I think is a really tricky problem.

I have the following query:
SELECT QryData.CONSUMER_NAME, QryData.STATE_CODE, QryData.Quarter, QryData.Year
FROM QryData;

and from this I would like to group this into Quarters/Years and get total figures for how many customers have been won (How many consumer names wern't in last quarters data but are in this quarter) and lost (How many consumer names were in last quarter but not in this quarter)

I haven't a clue where to start so any help would be appreciated!

Thanks in advance!
Mike

 

[alvechurchdata]

Try looking into sub-queries. These let you select records from one set which aren't in another. The general idea is to use one select statement to generate a list and then use another select query to select records that are or are not in that list. As an example of of how to find the customers who have not placed orders:

Select * from customers where customerid not in _
  (select customerid from orders)

There's a screenshot here:

http://www.alvechurchdata.co.uk/accsubquery.htm

You'll need something a little more complex to restrict each part of the query to a particular date range but I hope this will get you going.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Crowley16]

it would probably be more effecient in this particular case to use left joins and test for nulls...

--------------------
Procrastinate Now!

 

[lespaul]

check out the understanding joins article below for more info on joins and how they work.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi