Help with narrowing down a Query (1 Viewer)

tmyers

Active member
Local time
Today, 02:39
Joined
Sep 8, 2020
Messages
763
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

Active member
Local time
Today, 02:39
Joined
Sep 8, 2020
Messages
763
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

Banishment Pending
Local time
Today, 01:39
Joined
May 11, 2011
Messages
10,866
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

Active member
Local time
Today, 02:39
Joined
Sep 8, 2020
Messages
763
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:39
Joined
Feb 19, 2002
Messages
35,925
The query should not filter down to the salesperson. Leave that to the report you are making. If you want to create a report for a specific salesman, Use the Where argument of the OpenReport method and pass in the Salesman's ID.

If you don' know what you want to present, you will never figure out how to format it. What have you got so far and why is it unacceptable?

I have a pretty funny report story. Stop reading now if you aren't interested.

Back in the 70's we were working with a Mainframe using COBOL, and produced our reports on continuous form paper called GreenBar because the paper had alternating sections of green to make long reports easier to read, I walked into the president's office one day only to find him completely wrapped up on computer paper. It's a visual. You had to be there to see an imposing 60 year old man literally wrapped in computer paper. Once I stopped laughing, I asked what he was doing. He explained that he was trying to compare the totals sections for the various salesmen. I said, don't move, I'll be back in an hour. I ran down to my desk, took the sales report and removed the detail section so that it only showed the 5 line summary for each salesman. So, instead of being 100 pages, the report was three pages. Printed it out and returned. From that moment on, the man thought I walked on water. Sometimes our job is so easy I wonder why I get paid so well to do it:
 
Last edited:

bastanu

AWF VIP
Local time
Yesterday, 23:39
Joined
Apr 13, 2010
Messages
1,172
@ 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.
Cheers,
 

tmyers

Active member
Local time
Today, 02:39
Joined
Sep 8, 2020
Messages
763
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

Active member
Local time
Today, 02:39
Joined
Sep 8, 2020
Messages
763
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

Active member
Local time
Today, 02:39
Joined
Sep 8, 2020
Messages
763
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!
 

Users who are viewing this thread

Top Bottom