Need help creating a "summary" report

[shootermcgavin]

I’m trying to build a report in Microsoft Access and I’m running into a little bit of a problem. I’m not extremely experienced in Access, but I need to do this for a class project.

To put this in easier terms, I’ll relate it to a simpler idea than the actual project itself. Let’s say I’m trying to build a report that sums up everything I’ve spent at the grocery store in the past few years. Broken down first by grocery store. So, on one page of the report, I want the summed amount that I’ve spent at Stop and Shop. Under that will be different categories… milk, bread, water, etc. On the next page, I want the same information, just for Shop Rite. Etc, etc. I have tables and queries set up like this


When I attempted to create the report, it puts a single record on per page. When in reality, I just want one “Grocery Store” per page and have everything summed up. Sort of looking like this:


Sorry if this is a horrible example, it’s just easier than writing down all of the terminology that’s used in the project. I don’t know if this is too lengthy of a process and I simply need to learn more about Access or whether someone on here knows/can guide me in the right direction. I would really appreciate your help. Thanks.

[rpeare]

most of what you're asking for can be performed via formulas in a report based on your raw data. Typically people do not like commenting on 'class project' threads but there are a number of ways to get to what you want and I'll give you one example

first create a report based on your dataset (Lets assume your first screenshot is your entire dataset and it's a query, or table, named TESTDATA)

next, add a sorting/grouping of SHOP NAME, make sure the header and footer section are showing
in the header section of the SHOP NAME put the shop name

next, add a sorting/grouping of ITEM, make sure the header and footer section are showing
in the header section of the ITEM put the item name
in the footer section of each item you'd have a formula like =sum(iif(year = 2011, amount, 0))
and do it for each year.

There are more elegant ways to do this so that your report can be used for any year (let's say your database has 20 years of data and you want to be able to pick any 3 year period in there) but the same basic design holds you'd just have to modify your source so that it's only showing the data you want (through a query for example)

[shootermcgavin]

Sorry, I'm new here. Not sure what's accepted and what's not. But thank you for replying anyway. I'll give what you wrote a shot. Thanks a lot, I appreciate it.

[Dal Jeanis]

There's no rule against asking for help on class projects, shooter. Your question was well presented and politely phrased, which goes a long way here, like most places in life. You even provided pictures!

If you're getting a single record per page, then for some reason you are page-breaking for every record. Okay, that's circular, but it can be interpreted to mean something. This might be because your sort order is mismatched to your break logic, in other words because you told the report to break whenever A or B changed, but sorted on X and Y. I haven't seen this happen in Access, but I've seen it in every other report product I've ever worked with, so that's the first thing I'd look for.

Once you've handled your immediate problem, you might want to look at Pivot Tables and Crosstabs, which can get you some really great reports very similar to what you're attempting here.

[rzw0wr]

Shooter, You will find that on this forum and most like it that people are willing to help students readily.
They will answer most questions.
What they don't like is to do the work for the student.

Please post your questions and most will qive you an answer to GUIDE you. I will if I can.

Dale

[shootermcgavin]

rzw, I know, hence why I said "guide me in the right direction" in my original post. I wasn't asking anyone to do my work for me, I was just getting stuck with the records.