I've been doing a bit of work lately with reports and I can't really seem to get to do anything in code. With my forms, there's quite a lot of chances to use code. I guess I'm just not making the connection.
But it seems that reports mainly use your queries to get the information and then what coding actually happens comes into play at the level of text boxes doing averages, sums, etc. Is there something I'm really missing here? Do reports use DAO or ADO? How do you sum fields in records with ADO? Is that possible? Would you have to do For Each...Next statements to sum fields by moving to each record?
I'm asking all this because I'm finding it really hard to total things in reports and do percentages of items that are in different groups. For example, if I wanted to sum all the hours worked of a certain shift of workers and those shifts were grouped into different building names, I haven't been able to re-group those shift values back down at the end of the report and add them all together. BASICALLY, if I could interject code to sum up values of groupings and then store them as certain variables, etc., etc., summing, averaging, %'s would be much simpler...I think. :-)
I guess I'm looking for an extensive tutorial on reports and how to work with the values in them.
Any help with ANY of these questions would be GREAT! Remember, people do searches and if they hit any of these keywords, you'll be helping them, too! Thanks a billion! It's giving me a terribly tough time right now.
First of all, don't try to do everything in one query; nor a query and procedural code. Write a series of queries to prepare your various answers .. heck, I still like macros .. and bring them together with subreports.
You'll never be happy with the results of your efforts to do magical things with reports using VB.
Thanks, ghozy, but I'm needing more along the lines of what sundial's saying. I wish it were only a problem simple enough to do simple aggregates.
I just haven't had a lot of experience with reports. I've been working on a project for a coal company for a while now and am finishing things up with the reports. So I have plenty of experience with VB (from books and a course, as well), but not much with SQL and queries. I'd love to find a site that has examples of reports and doing different things with them AND explaining what they're doing and why. Do you know of anything like that?
What I'm going to need is very complicated. To give you a glimpse of what I'll be needing: I have a labor cost table where I enter an employee and how many hours they've worked. This will need to relate back to the employee info table that tells how much they get paid per hour regular time and overtime. This will need to figure up, of course, total hours grouped per week and multiply that times their pay for a date range.
Then I have a table that holds production. Essentially, how many estimated tons per day. Then I have an adjustment table that holds an adjusted amount to the estimated amount of that same day. Those two need to be added to get the total tonage for a date range.
Throwing in a simple table for variable cost for miscellaneous purchases for a date range, we have all the entered data.
What I need finally is, for a mine, COST PER TON OF COAL MINED for the date range. Of course, I'm not asking for you or anyone to give me a solution. I plan on continuing to work with Access and reports for a while. What I need is, as the adage says, "not for someone to give me a fish and feed me for a day, but to show me how to fish and feed me for a lifetime."
Could you give me an example of an approach you would take to this? Is this feasible? Should I restructure tables? I mean, I believe there's hardly anything infeasible with wanting to calculate things like this with programming languages. It's just figuring out how to do it with the tools you got. So I just need help with Approaches to Complex Reports 101.
If you would help me, I would GREATLY appreciate it.
I usualy go with divide and conquer approach on complex problems. divide your query needs to new simple logic queries. then use those queries in other queries like new tables to get your result.
frankly I can overcome %95 my report needs with IIf/Aggrate combinations and grouping in the reports.