Changing Record Count

[pauly047]

I am trying to do an analysis of purchases my customers have made over the past 12 months using their store credit card. I am also providing rewards to my customers based on the amount of monthly transactions they placed on their cards. I am running into a problem correctly reporting on these members. Here is a brief description of the scenario and the problem I am having.
In January, I had 10 customers with credit cards. In December, I have 50 such customers. When I create the report, I want monthly summaries for each of the 50 customers showing what they spent. If I start at December and work backwards to January, the report shows all 50 customers to start. As soon as I add another month's file of transactions, the number decreases to the number of customers for that month. THerefore, when I get to adding January's file, the report gives me the monthly summaries for only those 10 customers and I lose all the data for the other 40.
How can I get the report to give me the summaries for the customers who have cards the end of December, even if they did not have a card throughout the entire year?

 

[Turkbear]

Hi,
Try this ( if you have not already):
Group by Customer and then Month...

Plase the amount spent in the detail section.

Sum it at the Month level and the Customer level.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[pauly047]

Turkbear,
Thanks for the quick response. The data files I am pulling into CR already have the monthly totals so I don't need to do a summation by month. I had the monthly totals in the details section and summed that in a group (customer #) footer for the annual total.
The problem is actually pretty strange. If I do it for the 40 customers in December, everything works (even though it is only one month's worth of pruchases). Its when I add the previous month and there are fewer than 40 customers that it throws it all out of whack. If December has 40 and November has 38, the report will show the totals I need for the 38 customers only.
I'll keep playing around with it. Can't tell if it a true CR issue or how I am computing everything in Access.
Thanks again.

 

[BobSuruncle]

Is the information in separate tables for each month?
i.e. one table for January, one table for February, etc.

When these tables are linked, the default join is an Inner Join which excludes records from the left hand table if there is no corresponding value in the right table.

You may just need to change the join tho Left Outer.


Bob Suruncle

 

[pauly047]

Thanks for the advice Bob. This worked great. I just had to figure out how to access the Join Options, but the CR help directed me. Thanks again for your prompt help.