So, I’ve been trying my hand at subqueries, but I’m running into issues with what is effectively a 3 layer query.
The scenario is that I have some customers who I would like to (snail) mail flyers and other direct marketing materials. To assist with later analytics I want to be able to record the history of what and when I’ve sent out a mail shot to a customer.
To do this I have created a second table – which holds mail shot dates – and linked it to the main customer table in a 1 to many relationship.
In order to carry out my direct mail activities I need a report which lists the customers and when they were last mailed, filtered so that only customers who haven’t been mailed in the last 30 days are shown. Additionally I would like customers who have never been mailed (i.e. new customers) to appear in the query results. That way I can export the results to a Word mail merge.
It is important that (existing) customers who haven’t been mailed in the last 30 days show up in the query results AND that there is only one record for that customer. That record must show the last date of the mailshot, which by design should be older than 30 days.
The problem Is I seem to have figured out how to carry out each criteria when they are separate queries but I can’t combine them into a subquery functions as one.
I enclose my sample db where I have 4 customers (Customer 1-4). Customers 1 & 2 are historical and Customers 3&4 are new. I have created 3 queries which should hopefully provide further insight:
• Query 1 – Is a very simple select query with the join relationship changed to produce the desired results, but there are 3 results for both Customer 1 & 2 due to the 3 previous recorded mailings.
• Query 2 – Filters the above by stipulating the date (of mailing) must be older than 30 days. This also shows multiple records per customer as there are deliberate historical mailing records added. It also does not return the customers who have yet been mailed.
• Query 3 – Uses the DSum function to return the most recent historical mailing but (as above) does not return the customers who have yet been mailed AND it does not filter out newer records.
I’ve simplified things greatly for your ease in finding a solution.
Grateful in advance, as ever.
Edward
The scenario is that I have some customers who I would like to (snail) mail flyers and other direct marketing materials. To assist with later analytics I want to be able to record the history of what and when I’ve sent out a mail shot to a customer.
To do this I have created a second table – which holds mail shot dates – and linked it to the main customer table in a 1 to many relationship.
In order to carry out my direct mail activities I need a report which lists the customers and when they were last mailed, filtered so that only customers who haven’t been mailed in the last 30 days are shown. Additionally I would like customers who have never been mailed (i.e. new customers) to appear in the query results. That way I can export the results to a Word mail merge.
It is important that (existing) customers who haven’t been mailed in the last 30 days show up in the query results AND that there is only one record for that customer. That record must show the last date of the mailshot, which by design should be older than 30 days.
The problem Is I seem to have figured out how to carry out each criteria when they are separate queries but I can’t combine them into a subquery functions as one.
I enclose my sample db where I have 4 customers (Customer 1-4). Customers 1 & 2 are historical and Customers 3&4 are new. I have created 3 queries which should hopefully provide further insight:
• Query 1 – Is a very simple select query with the join relationship changed to produce the desired results, but there are 3 results for both Customer 1 & 2 due to the 3 previous recorded mailings.
• Query 2 – Filters the above by stipulating the date (of mailing) must be older than 30 days. This also shows multiple records per customer as there are deliberate historical mailing records added. It also does not return the customers who have yet been mailed.
• Query 3 – Uses the DSum function to return the most recent historical mailing but (as above) does not return the customers who have yet been mailed AND it does not filter out newer records.
I’ve simplified things greatly for your ease in finding a solution.
Grateful in advance, as ever.
Edward
Last edited:
