Help with narrowing down a Query

[tmyers]

I am trying to make a report for out salesman on a per month basis. The problem I am currently at is I have a query made to filter down to the specific salesman then each of their customers to a per month amount. I can't figure out what my next step is in the query to get it more presentable. I feel like a crosstab is what I need, but am unsure.

It almost feels like I need multiple crosstab's to achieve what I am after. I need to consolidate the query to per salesman, then to per contractor, then to per month. Then each month needs to show sale, cost, profit and profit %. Would anyone happen to have an example of what I am trying to make?

 

[tmyers]

Tinkering with a UNION, I managed to get both sale amount and profit amount into one crosstab query. It doesn't look very pretty as you can't easily tell which row is which. Based on what I have experimented with, I think my hopes of doing this in only 1 or 2 queries simply wont work.

 

[plog]

Work backwards. Figure out what you want the finished report to look like, then build queries to achieve it.

Do a mock up with fake data and then make small incremental steps to get there.

 

[tmyers]

I can't wrap my head around how to get this into a report and make it clean. I may go the route of creating an excel file to dump the data into and hand tailor a sort of dashboard for it. Let Access do the heavy lifting, then let Excel display it with all its nice graphs and such.

 

[bastanu]

@ tmyers:
You might want to have a look if you can use my free utility to export the underlying query as a pivot table to Excel. Note that the utility can create a new Excel file every time or update the data in a pre-formatted "fancy" template as you suggested.

FBA Pivot Table Designer - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,

 

[tmyers]

Ill take a look at your addon bastanu and let you know if is what I am after. Thanks for sharing!

Also Pat, I got a chuckle out of your story.

 

[tmyers]

Sort of relevant to your story Pat.

What I am building now is mostly as you could guess, sales reports for the boss. I finished one of the reports early this morning and while testing code to export the report to outlook and send, I accidently sent the report to our entire sales department and it was unfortunately a report they are not supposed to ever see. So now I am in very hot water with said boss

 

[tmyers]

Oops indeed. I just told him in was an honest mistake while testing something and iterated I am not a coder and mistakes happen. This one just happened to cause an email to be sent.

Also note to self, when testing such code make sure whatever is being attached isn't meant to be kept secret!