Date query

[rosaW]

Hello,

I have created a query to show on which week numbers of the year, customers have bought products. I now want to show the week numbers in which each customer has not bought anything.
Can anyone explain how to do this?

Greatly Appreciated!

 

[tjlindow]

Hi Rosa,

Can you post a screen shot with some made-up data? It's difficult to propose a solution without having an idea of how the source data looks.

TJL

 

[rosaW]

This is what i got so far. The weeks on which each customer has bought a product.
http://i969.photobucket.com/albums/ae180/wotte/weeks.jpg

 

[tjlindow]

Rosa,

The easiest solution to see the weeks where a customer did not buy would be to create a pivot table with strCustomerID in the rows, dtm Year and dtmWeek in the columns, and count on dtmWeek. Blank values in the resulting pivot table would indicate weeks where the customer did not buy; a value of 1 would show the customer did make a purchase.

TJL

 

[rosaW]

Thank you that worked! Now i only need to show the customers with the weeks where there are blank values or did not purchase. How would i set up a filter for this?

 

[tjlindow]

First, you would need to remove the Grand Totals for columns and rows. Then you could add a formula in a cell that is well to the right of the pivot table (so that the formula isn't immediately overwritten by the Pivot Table when additional data is added). If you choose to put the filter formula in Column AA, the formula would be:

=SUM(B$4:Z4)-COUNTA($B$3:Z$3)

This formula assumes your column labels (dates) are in row 3 and your first column of pivot data is in B4. "0" values for the formula would imply that the customer has made purchases in each week - you could filter these out. Values that are >0 mean that the customer has missed one or more weeks.

TJL

 

[rosaW]

I don't know if this makes a difference or if you realized this, that i am using MS Access.

 

[jbeaucaire]

I don't know if this makes a difference or if you realized this, that i am using MS Access.

I don't know if you realized this, this is an Excel forum. We expect all Qs here would be about Excel.

You can post Access questions here:
http://www.mrexcel.com/forum/forumdisplay.php?f=18

 

[rosaW]

Indeed. I see that now..i'm new to this site. Thanks!