Pulling data for reports
I run a report each month to show a groups progress in sales. The data pulls for the current month, but I have a certain field I'd like to incorporate. I would like it to search a full previous history to pull 2 new fields. If the account that has just paid has ever paid before, or not. I pull the total payments each month, so I would just need 1 of those fields to make both mathematically sound. I have been able to gather the data 2 different ways, but it doesn't allow the rest of my report to function perfectly.
I would group on account_n field, and then do a count of payments for that field. If it was = 1, and the only payment was the current month, then it was considered a new payer. This worked perfectly but the base of my data was the whole table... instead of just the current month so it skewed my totals and other calculations and I was unable to correct them with able options. I then just tried to make a query and replicated it to the one I made in crystal and tried to run off that, but the same thing happened.
I just don't know how to get both sets of data to cooperate so the rest of the data stays intact and correct. I use crystals 2011 and access 2010, and I have both options available to pull from.