Help Creating Report in Access

[BigD_Apex]

Hi, I'm trying to create a report in access, and am having no luck. I've gone into design view and expression builder, but I think I'm missing something here.

For this example, we're tracking sales tax, and they need to be broken down by city and state. The current report that I am working from is at the detail level, which I import into Access and run a query to group sales by jurisdiction. Now in some states tax is levied at state, county and city level.

So let's assume that location 1 and location 2 each had $100 total in sales - after I run my query to group and sum the data my output would look like this:

City A $100.00
City B $100.00
State $200.00

My desired output for a report would be:

City A $100.00
City B $100.00
<Space>
State $200.00

In other words, I want the report to say "On the top section of the report give me all sales <> *State*. (The identifier has "State" in it) On the bottom section give me only *State* sales"

I'll then do other calculations because this report will have taxes collected, taxable/non-taxable sales, etc, but at this point I can't get the report to disregard State on the first section.

Thanks in advance.

 

[SydneyGeek]

Use the Report Wizard as the first stage in building the report. It will prompt you for the table / query to use, guide you through selecting grouping, sorting and subtotals, let you choose the page orientation.

The final result will require some tweaking but a lot of the hard wok is done for you.

Denis

 

[BigD_Apex]

Thanks for the reply. I did get a report that I was happy with (for the most part) from Report Wizard.

My stumbling point is how to not include "State" in the top total and have it separately stated on the bottom.

 

[SydneyGeek]

In the report design, right-click the band that represents State and pick Sorting and Grouping. Display the footer if not already shown.
If you have a subtotal that you want to display below the group, cut and paste the one that is in the State header and place it in the footer.
If you want to hide the State header, set the height of any controls in it to 0 and then reduce the height of the header. It won't show but any grouping / sorting that relies on State will continue to work.

Depending on the version of Access that you use, Google for some online tutorials on reporting. There is plenty of good material out there.

Denis