CODEWITHSUNDEEP
sqlms-access
user1709986
user1709986

Reputation:

Microsoft Access - Generating a Specific Query

Here's an interesting challenge that I'm facing. I dub you genius if you can create a query for this problem - It's been troubling me for quite some time.

Here's two tables:

Customers Table

customerId, name

Transactions Table

transactionId, customerId, amountPaid, purchaseDate

I want to select all of the customerId's for people who don't have any transactions for the current month, but owe from the previous months.

For example, if the month I select is October, I want to select all of the customerId's where there are no transactions for October, but where the sum of transactions before October is greater than zero. Basically, my goal is to generate a report that displays receipts for customers who owe nothing for the current month, but owe for previous months. Thanks for considering this problem.

Upvotes: 0

Views: 93

Answers (3)

user1709986
user1709986

Reputation:

I finally solved it with the help of user1238850 and Guffa. It's a bit messy, but it seems to work so far. 

SELECT Customers.customerId 
FROM (
    SELECT Customers.customerId
    FROM  Customers
    WHERE (((Customers.customerId) Not In (
        SELECT Transactions.customerId 
        FROM Transactions 
        WHERE Customers.customerId = Transactions.customerId AND 
              Transactions.purchaseDate >=#8/1/2012# AND 
              Transactions.purchaseDate <#9/1/2012# OR 
              Transactions.customerId Is Null)))
      )

AS Q1     
INNER JOIN (     
    SELECT Transactions.customerId, Sum(Transactions.amountPaid) AS SumOfamountPaid
    FROM Transactions
    WHERE Transactions.purchaseDate <#8/1/2012#
    GROUP BY Transactions.customerId 
           ) AS Q2 
        ON Q1.customerId = Q2.customerId;

Upvotes: 0

Guffa
Guffa

Reputation: 700322

You can get the last purchase date of each customer for purchases up to and inlcuding the selected month, and check if that is before the selected month.

It's been a while since I used Access, but something like this:

select c.customerId, c.name, max(t.purchaseDate) as lastPurchaseDate
from Customers as c
inner join Transactions as t on t.customerId = c.CustomerId and t.PurchaseDate < #2012-11-01#
group by c.customerId, c.name
having lastPurchaseDate < #2012-10-01#

Upvotes: 0

mpora
mpora

Reputation: 1479

You might need to rethink your database model. IMHO I think you need more tables to handle multiple months. Say a table with months (January to December) as columns and a userid column. From that I think you shoul be able to carry out your argorithm you are describing.

Upvotes: 0

Related Questions