Trying to get values of comparison of two weeks.

[pjdube]

Dear All,

You all have been a great help in the past and I am stumped on this one.

I have a week by week sales table. Each week there are regular clients that I input how much they pay/donate.

I am trying to create basically a comparison query of the current week versus last week to show what
clients have not paid yet in the current week.



Here is an example of my query/datasheet so far:

Date:	Client:	Amount:
12 Mar 20	Pablo Frotest	$200.00
12 Mar 20	John Frow	$20.00
12 Mar 20	Frank Bestow	$100.00
12 Mar 20	Timothy Sparks	$450.00
12 Mar 20	Mike Smith	$230.00
5 Mar 20	Pablo Frotest	$200.00
5 Mar 20	John Frow	$20.00
5 Mar 20	Frank Bestow	$100.00
5 Mar 20	Timothy Sparks	$450.00
5 Mar 20	Mike Smith	$230.00
5 Mar 20	Jim Strobe	$100.00
5 Mar 20	Tim Spankel	$450.00
5 Mar 20	Hillary Gross	$230.00

So it pulls up the last two weeks which is great, but what I want the above to show is WHO has not paid yet, and should
come out as follows:

Client:	Amount:
Jim Strobe	$100.00
Tim Spankel	$450.00
Hillary Gross	$230.00

That way I can follow up on them.

Any help with this in sql would be great.

Thanks!

Regards, Phil

Timothy Sparks

[ssanfu]

What happens if some one hasn't paid for 2 weeks?

This seems to work but there is an extremely limited data set - you really need to test this...

It is more difficult if last week's payments are in the same table as this week's payments. So I created two queries for this week and last week, then created an unmatched query that is the record source of the form.


Use the date picker or enter a date for the Last week date (in this case Mar 5, 2020). Click the button to see the results...

[pjdube]

Steve,

Brilliant! I don't know why I did not think to just make 3 queries and then join them!!!!

Your example was perfect and simple and it works!

Thanks for taking your time in helping me.

Phil

[pjdube]

Steve,&nbsp;<br><br>Brilliant! I don't know why I did not think to just make 3 queries and then join them!!!!&nbsp;<br><br>Your example was perfect and simple and it works!<br><br>Thanks for taking your time in helping me.&nbsp;<br><br>Phil

[ssanfu]

Happy to help.

Good luck with your project......