Customers who have not had transactions...

[dawnd3]

Hi there, Lets say I have a database with customers and transactions. How would I run a query (to create a report) to show all the customers who have not had a transaction in the last 6 months? Do I have to create a query of those who HAD a transaction in the last 6 months and then compare it to a list of all customers? Or is there an easier way?

Thanks,

Dawn

http://www.calliopeconsulting.net

 

[DriesG]

Perhaps you can just write as a criteria for the transactions in your query:
<(date()-180)

This when you assume that a month has 30 days as an average, otherwise you will have to predefine the days in a month.

regards,
Dries

 

[dawnd3]

Hi there, thanks, but the problem with that is that it does not show me who hasn't had a transaction in the time frame, it only tells me who has had transactions before the time frame. I need to know who has NOT had an order.

Dawn

http://www.calliopeconsulting.net

 

[MacCaillean]

Hi dawnd

You need a query that has the Customers and Transactions tables related via the Customer ID. In the join properties of the relationship, however, you need to set it so that all records are displayed from the Customers table.

Then you can add a criterion to return only records where the Transaction ID is null (i.e. there is no transaction).

When you combine this with your date parameters, you will return only those customers without transactions between the given dates.

Regards

Mac

 

[MichaelRed]

There is a query wizzard for &quot;unmatched&quot;. Use it. Study the results. You will not need to ask this question again.

You can find a reference to this via the ubiquitous {F1} (aka Help) under the Answer Wizzard tab with unmatched records as the keyword.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[dawnd3]

I am not sure if this is what I am looking for. But I appreciate the help. When I do as you suggested Michael, (I changed the join type and put &quot;is null or <date()-180&quot; in the criteria for the date)it is showing me customers that don't have transactions and those who had transactions more than 6 months ago, but I want to see those who had transactions 6 months ago but NOT in the last 6 months. I am not sure if I am misunderstanding your post or if I am just not explaining myself well. Basically, if I wanted to send a letter to anyone who hasn't ordered in 6 months, I want to be able to see who those people are. Am I misunderstanding your post?

Dawn

http://www.calliopeconsulting.net

 

[dawnd3]

ok, I finally figured it out. Michael your post led me in the right direction, so thank you. I created one query that showed me all customers. Then I created another query that showed me all transactions in the last 6 months. Then I created a 3rd query that qrouped the customers that were in the transaction query. And finally a 4th query where I changed the join type to show me all records from the All customers query and only those that matched from the transactions query (linking the customer id). I then filtered for the null to see those who did NOT have a transaction in the last 6 months. YEAH!!!!

Dawn

http://www.calliopeconsulting.net