May 23, 200817 yr I have a database of transactions that contains a 38,000 unique transaction numbers and customer number field. There are dramatically more transactions than customers. I want to generate a report that looks something like this: 1 transactions - 30,000 2 transactions - 5,000 3 transactions - 2,500 4 transactions - 400 5 transactions - 90 7 transactions - 4 11 transactions - 1 Total Transactions - 38,000 The report simply tells me how many repeat customers i have. How do i create a report that looks like this? I've included the data. Edited May 23, 200817 yr by Guest
May 23, 200817 yr The problem is that you can't organize the reporting by a summary field, you have to make a selfjoin category to category, then provide your solution with a calc' with the aggregate function Count( over this relation ... then is just to group your summary report with this. If you do this via the wizard will you initially get a body part where you are likely to expect the cathegory field, which then gets moved up in the subsummary part of the layout, before the body part gets deleted. --sd
May 23, 200817 yr Author What you suggest is way over my head. Any chance you can build me an example in the data i provided? Thanks in advance (either way)! Peter :P Edited May 23, 200817 yr by Guest typo
May 24, 200817 yr Any chance you can build me an example in the data i provided? More or less, where would you expect me to find the +30K records to report upon - anyways I've made you a template to tear appart. --sd Transactions.zip
May 24, 200817 yr Author YOU ROCK! Thanks for the example. I attached a sample data file with 1000+ rows of data. Its much simpler than yours. Simply a column of transaction numbers and a column of corresponding customer numbers. My goal is to know how many customers had 1 transaction, 2 transactions, 3 transactions etc. THANKS AGAIN! data.txt
May 24, 200817 yr All right with the given data would it look like this attachment:. --sd Transactions2.zip
May 24, 200817 yr Author Soren, I think you are close. The goal is to know how many individuals bought once, twice, etc. See my original post. THANKS AGAIN! eter
May 24, 200817 yr I think you are close. The goal is to know how many individuals bought once, twice, etc. See my original post. I thought it was what the lateste template did, whats wrong? ... the longest number is the individual's ID isn't it, while the first of them is the transaction ID. --sd
May 24, 200817 yr Author SD, The goal is to know the total number of people who have 1 transaction, 2 transactions etc. the report would look like this: Number of Transactions - Number of individual users 1 - 30,000 2 - 5,000 3 - 2,500 4 - 400 5 - 90 7 - 4 11 - 1 Does that make sense?
May 24, 200817 yr Indeed, but how would make 1208 transactions become that?? Where is the flaw in my approach?? Ah I've just seen it! --sd Transactions3.zip Transactions4.zip Edited May 24, 200817 yr by Guest
May 28, 200817 yr Author Søren Enclosed is a file with 38000 records. Run the report and you'll see an error i cant figure out. Can you tell me why the summaries for the higher number of transactions have so many customers? It doesnt make sense to me. The data doesnt back it up. Thanks again for all your help. I OWE YOU! Transactions5.zip
May 29, 200817 yr It's a bit worse than I thought in the first place, we then better do it the non relational way - although it's going to be quite a bit slower in performance ... I would hope that others might chip in and make the algorithm faster! --sd Transactions6.zip Edited May 29, 200817 yr by Guest
May 30, 200817 yr I would do it this way. I believe it's faster and more straightforward. Note that it ignores the found set in Transactions, so if you want to restrict the report, you will need to filter the relationship. ReportByTXCount.fp7.zip
June 6, 200817 yr Author Your solution works great! Unfortunately when I try to reproduce your solution in my existing database i get the following. (Your correct report is on the left, my incorrect report is on the right) Any clue what I'm doing wrong? Peter
Create an account or sign in to comment