teaaddict

Ha! I feel like I'm getting plenty of that already

Thank you for your help, I think you've got to the real crux of the issue with your example. Following the difficulty I was having, I returned to the client to make sure that this was completely necessary or if there was any flexibility in the structure of the report. Although she had specified the second of the options you outlined, on closer inspection of what the report is used for it seems the first option would be fine. So thankfully I an avoid the problem altogether. Once again though, thank you for the time and expertise you've given me.

You're both right, it is the same report repeated with different levels of detail. And the reports should be ordered so that each Staff member is ordered behind the department they are from, and so on. All of the data for this report is, indeed in one table.

Perhaps I was unclear- I need to generate a report for each company, listing departments. There are only 5 or so of those. But then for each department in each company(~10 departments for some) I need another report with the staff members. And for each Staff member (usually 10-15) I need another individual report with the product list. They don't necessarily have to be interleaved in the order I outlined above, but that would be ideal. With that in mind, I thought I would need to know the number of departments, staff members etc. who have ordered within the time period I'm looking at , and then loop through that number of times, producing a page (and appending it to the single PDF) each time. I can't see a way of 'just generating the reports', but I may well be missing something obvious. Do you mind explaining a bit more?

Hi Everyone, I'm trying to write a script that will generate a rather large and 'nested' report from my database. I haven't gotten very far yet- I'd like to know if what I'm attempting is feasible, and if anyone has suggestions for the route to take. My database includes records of products ordered, when, who by, and many other details. The report I need to generate will contain many pages, with smaller reports nested amongst others. For example, the first page would be a list of the companies which have placed orders in a time period, and the total cost of the products they ordered. Following this would be another page for each company, each breaking down the departments which have ordered items, and the total cost incurred by each. For every department, there would then be another page detailing the Staff members who have placed orders, and their individual totals. And finally for each individual there would be a report listing the codes of products they ordered and the cost total for each of those. Each of the individual pages is possible to make using a sub-summary report and a find/ sort, but I'm not sure how to script it all together. I was thinking that it may be possible to achieve this using nested loops. Do you think this would work? Something like: 1. Count the number of Companies which ordered products in the the specified time period. 1. Generate the report with the list of Companies. 2. Count the number of departments in each company which ordered something in the specified time period. 3. Loop through the different companies, for each, Count the number of Departments which ordered products in the the specified time period. Then produce a report with the list of departments. 4. Inside that loop, loop through the different departments, Count the number of Staff members which ordered products in the the specified time period. Then produce a report with the list of Staff. 5. Inside that loop, loop through each staff member producing a report with the list of products. I hope you understand what I mean. My first stumbling block is the 'counting' parts- to get the number of unique values in a field for a found set. I understand that this may be possible using a self-join relationship, and am working on it at the moment. Any and all suggestions would be welcome- is this going to work? Many thanks! -Sarah